Historical Price Retrieve issue/resolution

[jblankenship]

I have noticed that certain investments in my Fund Manager portfolio were not updating prices when I selected "Retrieve Historical Prices" for the entire portfolio. Here's what I discovered about them and what I did to resolve the situation, in case it may be helpful to other FM users.

For setup, I am using the Advisor version of Fund Manager. I have my portfolio arranged as: Master Portfolio/major sub-porfolio/household/individual account/ - so an example would be as follows: Master Portfolio/Active AUM/Smith Family/John Smith IRA

Within this structure, the individual investments are saved in the Fund Manager\Data folder as *.dat files (using Portable/Cloud Mode). An example investment might be saved as Fund Manager\Data\IBM.dat.

When I tried to update historical prices, there was a group of investments within several subportfolios that would not update when I attempted to retrieve the historical prices for the entire major subportfolio. It was the same group of investments every time that wouldn't update. However, when I selected just the individual account and retrieved historical prices, the process would update the prices, no problem.

Investigating further, I noticed that the investments that were not updating in the mass update often had a naming standard of ticker.dat (such as IBM.dat) - whereas all other investments (the ones that were updating) had the naming standard of ticker_accountnumber.dat (such as IBM_1234567.dat). Sometimes the naming standard was a bit different, like IBM3.dat, or within the naming standard (ticker_account.dat) but referencing an old account number (which resulted after the transition from TDA to Schwab account numbering) but still not conforming to what appears to be the standard. In my case, the account number is the Schwab account number, for importing positions and transactions. So I went to the individual account level in question and created a new investment with the same ticker as the one that wasn't updating, saving the investment within the normal path (Fund Manager\Data\*.dat), so that the naming standard for this new investment would follow the ticker_accountnumber.dat (eg, IBM_1234567.dat) nomenclature. In creating this new investment I indicated 1 share at $1 for the initial investment (this is important later).

Then I closed FM in order to save this new investment. I then made a copy of the original *.dat file (with the incorrect naming standard, IBM.dat for example) in a working folder and renamed it with the new name that was assigned when I created the new version of this investment (IBM_1234567.dat), and then replaced the new *.dat file in Fund Manager\Data with my copy of the old data with the new name.

I reopened FM, and now there are two investments in this account subportfolio for the same ticker. Carefully reviewed these two, making sure that the new copy (file name IBM_1234567.dat) includes all of the pricing and holdings information from the old copy. I then chose the one with the old naming standard and closed that investment, thus removing the duplicate investment from the portfolio. There is also an extra transaction in the Share Transactions for the Cash holding account for the opening of the newly-named investment - in my case I indicated one share for $1, so it was easy to find and delete.

After closing FM once again, I took the extra step of removing the mis-named investment (IBM.dat) file from my Fund Manager\Data\ folder. Being paranoid of causing problems, I moved the mis-named file to a holding folder elsewhere, in case I needed to bring it back.

Now the prices for these investments update along with the entire major subfolder with no problems.

Incidentally, I'm not sure why these particular investments had the wrong naming standard. Many but not all of these turned out to be the only holding of that particular ticker within the overall Master Portfolio (in other words, no other account held that partcular investment), and most all had been in existence for many years, and thus many versions of Fund Manager. So it's possible that at some point in the evolution of FM the default naming standard changed and these weren't updated... this is just speculation on my part.

Here are the steps of the process more concisely:

1. Locate the investment that's not updating.
2. Make note of the file name for the *.dat file (Properties\Statistics tab)
3. Create a new investment with the same ticker (File\New Investment) and save the new investment in the same path (Fund Manager\Data\). I indicate 1 share at $1 price. The description of the investment doesn't matter as it will be overwritten when you copy the old file into the new name.
4. Close FM, saving portfolio on exit.
5. Copy the original *.dat file to a working folder.
6. Make a second copy of the original *.dat file in your working folder.
7. Get the new *.dat file name. Overwrite the second copy's name in the working folder with the new name.
8. Cut the new copy of the file with the new name and paste into your regular data path (Fund Manager\Data\) and choose to replace the existing file.
9. Re-open FM. Locate the old investment (with the original name in Properties\Statistics tab) and Close this investment.
10. Close FM, saving portfolio on exit.
11. Locate the original *.dat file in your regular data path (Fund Manager\Data\) and delete this file.
12. Re-open FM. Go to the Cash holding for the account and delete the $1 transaction for the original purchase of your newly-named investment.

[jblankenship]

If faced with the above issue for many investment (*.dat) files, you might find the following process helpful.

My price retrieve log is called intlog3.txt. Locate this by going to Help/Logs/Price Retrieve...
I saved this to another location in order to work with it. Then I opened the file with Excel (you could import it into Google Sheets as well), which created a spreadsheet with one tab named intlog3.

Create two new tabs in this sheet, called Sheet1 and Sheet2.

Within Sheet1 in cell A1, enter the following formula:

Code: Select all

=UNIQUE(FILTER(intlog3!C:C,LEFT(intlog3!C:C,7)="WARNING"))

Then in Sheet1 cell B1:

Code: Select all

=MID(A1,FIND(CHAR(34),A1,1)+1,FIND(CHAR(34),A1,FIND(CHAR(34),A1,1)+1)-FIND(CHAR(34),A1,1)-1)

Copy this formula down for all rows that have data in Column A

Then in Sheet1 cell C1:

Code: Select all

=MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)

Copy this formula down for all rows that have data in Column A

In Sheet 2, cell A1 enter this formula:

Code: Select all

=SORT(FILTER(Sheet1!B:C,Sheet1!C:C<>""),2,1,)

This should produce a sorted list of all of the investments that are not updating. They're sorted by the account level for ease of updates. If your file or tab is named something other than intlog3, just update that name in the first formula (Shee1!A1).

[Mark]

Hi jblankenship,

Interesting. Thanks for sharing your experience/learnings. The filename of the investment should have no impact on the ability to retrieve price. I can't say what happened here, but I'm glad it worked out for you.

We have changed the default filename convention. It used to just be <symbol>.dat, and now it is user-adjustable, but the new default is <symbol>_<account number>.dat. When you're importing or retrieving if you click the "New Investment Options..." button you can select your preference for "Specify Filename Format for Automatically Created Investments".

[jblankenship]

Hi Mark -

I agree, it doesn't make sense that the name of the investment .dat file should cause the issue, but it's hard to argue with the results. For the first time in a long time, this morning I did a history retrieve of a week's worth of prices and did NOT receive a message that some of the investments couldn't be updated.

[Mark]

Hi jblankenship,

One other possibility is that there is some dependence on the investment stack order. This changes the request made to the quote server. For example, if there was an invalid symbol in the stack which messes up the retrieval of all later symbols in that particular request. We try to prevent this, but stuff like that has happened. Adding new investments as you've done, would change the investment stack order. I'm not saying this is for sure what caused it, but it is possible.

[robert1]

Mark,
I have noticed some of my mutual funds are not displaying the right return info (at least when compared to Morningstar when using Tiingo. Will the fix Jblankensip referenced or the new quote module resolve my issues?

[Mark]

Hi robert1,

Are you comparing using TWR or ROI? If you had any transactions your ROI may differ from the TWR, which is more of what you would see published, like on Morningstar. Also, make sure you're comparing for the same time frame, and have prices recorded at the beginning and ending of the yield term.

[robert1]

I'm using the custom report settings in Fund Manager. I checked and its set for TWR and it doesn't track Morningstar. I noticed that other available settings in the custom report settings include % Gain (between). Would that or some other option more closely track the returns on Morningstar more closely as that is what most people check.

Any suggestions you can offer to to track Morningstar would be greatly appreciated.

[Mark]

Hi robert1,

If you're using a Custom report, and want to compare a performance metric of an investment against what you see published, like on Morningstar, please use one of the "Time-weight return" fields, like:

Time-weight return 1 year (ending)

The difference in these fields is the yield term (when the calculation starts and ends). The above ends on the ending report date, and goes back 1 year. So, if you want to see the 1 year yield for 2024, choose that field and set your ending report date to 12/31/2024.

Some other things to check are that you have pricing recorded for the beginning/ending yield term dates. In this example, it would need prices on 12/31/2023 and 12/31/2024. For TWR on an investment, you also need to record all the distributions. Use the Data Register, and under the "Prices" data type look at your recorded prices. Under "All Transactions" look at your recorded distributions.

For many more details on using yields in Fund Manager, see the documentation.

[robert1]

Mark,
I verified that the TWR return was being used. I still noticed several substantial return variances between the FM calculated return and Morningstar and Yahoo. If you have time to look at this, here are several of the impacted funds:
BUITX
PBQAX
PJFAX
PRJAX
IALAX

[Mark]

Hi robert1,

Look at the details of 1. It is easiest if you look at a 1 year yield. What is the starting/ending price? Were there any distributions? For example, if you look at the yield today, ending 1/20/25 for 1 year, what is the price on 1/20/25, and what was it on 1/20/24? Look at what you have recorded in the Data Register under the "Prices" data type.