Importing price history from TIAA-CREF and others...

feffer777's Avatar

feffer777

12 Oct, 2016 09:47 PM

Recently, price history imports from TIAA-CREF began to fail. I researched this, located the problem, and found a fix. So this "how to" may help other MD users who need to do manual price history imports from TIAA-CREF or other financial institutions. If your FI posts public prices daily and is an "open" fund, MD already automatically imports price histories and you can stop reading here. However, "closed" or proprietary funds usually require a manual import. Without this, you will not be able to create historical graphs etc. MD has long had a work-around; "manually importing a CSV file." These can be obtained from your FI's web-site, downloaded and imported into MD. If the process works correctly MD will say something like, "successfully updated 139 of 139 prices." If that happens, you're golden, but if it shows errors or omissions, you are stuck! There are no available fixes within MD and you aren't shown the reason for the failure. Of course you could manually enter all the data, but that's not why we use a personal finance program. Following is a way to get trouble-free imports.

Your FI will have a web-page which allows price history downloads of their funds. For example, I import my two TIAA-CREF funds quarterly. First set the period from start date to end date of your first fund; then download it as an excel file. Do this separately for each fund you own. Open the file in Excel or whatever spread-sheet program you are using. It may have two or three columns: one will have the date and the other the closing price on that day. If there's a third column, it may be the name of the fund...you should delete that column. Move the dates to the far left column if they aren't already there, and the prices to the second column. Insert a line at the top of each column with the headings, "Date" and "Close" (w/o the parentheses, of course).

Look carefully at the format of these two columns. The dates MUST be in the format "mm/dd/yyyy" That means for example that July 7, 2016 will be formatted as "07/07/2016" If that is the case, save it as a "CSV" file. It should import successfully into MD. For several years, TIAA-CREF files worked this way, and I never had a problem with them, however recently, they changed their web-site and evidently their underlying data-base format. Their recent files fail to import into MD! Opening them in my spread-sheet program, Libre-Calc showed that the dates were in the format of "mm/dd/yyyy" but that single digit months did not have a leading zero, so July 7, 2016 now appeared as "7/07/2016" Evidently this was poison for the MD import engine. In a closed thread, I've suggested that the MD folks should test this for themselves and at some point, adjust their import engine. This seems like far too fragile a difference to cause failure. However, until they do, you can do a work-around to "fix" your file and get it to import. There are two ways, both pretty easy.

Method One: with the file open, manually add a zero to the first date (oldest date that appears first in the date column) if it is a single digit month, so July 7, 2016 will be "07/07/2016" That is enough to get the import to work even though all the other single digit months remain unchanged. Save it as a "CSV" file as before and import into MD. By the way, the Price format is more robust; with or without "$" signs, both import OK.

Method Two: when you open the file and see single digit months, notice that the "entry" line probably has a place-holder...something like this: '7/07/2016 Notice the single apostrophe mark before the month! It won't be in the dates themselves, but is probably an invisible character that throws MD off. Close the file w/o changes and re-open it with settings that flip the place-holder to a "zero" This will vary with your spread-sheet app. Window's Excel will allow you to re-format on-the-fly but some other apps may not. Once you find the way to do this in your SS app, it becomes even easier than the other method.

btw, I mentioned this issue to my TIAA-CREF representative and they are "looking into it..." As you know big FI are not inclined to change things unless a LOT of customers complain. I do hope that MD will treat this as a bug and correct it as some point. Until then, I hope this "how-to" will help other MD users that have an issue.

  1. System closed this discussion on 11 Jan, 2017 09:50 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac