Importing historical prices manually.

feffer777's Avatar

feffer777

20 Oct, 2019 07:16 PM

This may be necessary for a few reasons. Some securities are proprietary and their daily prices are not handled by Yahoo or FT, or if you do not use MD every day, but still want a complete graph of your investments. For a long time the extension "Quotes and Exchange Rates" would automatically import all daily prices and historical prices into MD. This was broken by Yahoo at some point prior to 2017, probably because they wanted to monetize their data. A feature called Alphavantage was added to the Quotes and Exchange Rates extension to overcome this issue. Currently this does not work very well...many securities fail or are skipped. A new extension that reportedly works very well is Quote Loader. This system has one downside for those who do not use MD everyday: it does not auto-import historical prices. This is a feature for active investors (or those who open MD every day) because it does not over-write past prices. If I'm mistaken about any of the above, please correct me, I'd love to automate historical price imports like the "good old days."

I'm a "passive" investor who owns a number of stock and bond funds from a few Financial Institutions, and only use MD to aggregate my portfolio and print out a report once a quarter. However, I like the MD feature that shows graphs of all the securities I own (or used to own) and for this I need a complete price history of each. In the past, I posted about a method for doing this in the thread Yahoo and historical prices? but I've refined and simplified this method. These are my assumptions: you want MD graphs your present and past investment securities, so you need all historical prices AND you don't want to delete securities you no longer own.

This method gets historical prices from the Yahoo Finance web-site.

Yahoo historical price method.
1. In MD, go to Account > Portfolio_View. All presently owned securities & their symbols are listed there.
2. Double-click a security symbol and it’s “Historical Prices” window opens.
3. Go to Yahoo Historical price page and paste the security symbol into the search window...if greyed out, paste the symbol into the topmost search window and your security's price history should open up.
4. Filter it by the date range needed. Or simply use the period from the 1st day of the quarter to the present day (In my case usually
about 2 weeks after the end of the quarter when all relevant Statements etc are posted). Download it as a text.csv file; this will probably import into MD correctly without needing to open the file to make any alteration. This is the Easiest Way! If it fails or skips things, open in Excel (or LibreCalc), save as a .CSV file and re-open; change price column head to “Close” and fix the single digit month date issue (see my previous post). Import this security.csv file to MD from the dialog on the MD Price History window, then close and re-open to update it and make sure current price is shown correctly.

Note, by opening MD Account > Portfolio_View, you will see only funds you currently own. You can manually update them fairly quickly one by one. No need to delete old securities to reduce clutter. They live on in MD with graphs of the time you owned them...you can of course update them too to see if maybe you'd like to re-invest. Yahoo now offers a "premium" service with monthly fees, but so far this method still works on "free" form of their site. If you have a Yahoo login, it may be useful; it seems to "ungray" the quote search box, but if not you can use the topmost search box.

If you only use MD at the end of quarters like me, but don't care about security graphs in MD, you can install the new Quote Loader extension and set it to update on that day (or on opening MD) and that day's quotes will be imported, but you will have to be certain to open MD on the last day of the quarter!

Again, if I'm mistaken about any of this, please let us know.

  1. 1 Posted by derekkent23 on 21 Oct, 2019 10:26 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    If you want to use Mikes Security History Loader extension to load a number of security history downloads in one go, you could follow this process.

    1. Download each of your security price histories from Yahoo as individual CSV files.
    2. In notepad++ open each CSV file in turn and:
      a. From the menu click SEARCH – REPLACE to open a window.
      b. In the “Find what:” box enter the year, e.g. 2019
      c. In the “Replace with:” box enter Security “Ticker,year” e.g. AAPL,2019
      d. Click REPLACE ALL. Note if there are entries for more than one year repeat the to add Ticker, in front of each year.
      e. FILE - SAVE the file.
    3. In notepad++ create a master file, FILE – NEW and copy/paste each of the individual CSV file contents into the master file. Exclude the heading from all but the first copy/paste.
    4. Add Ticker, to the first line. E.g. Ticker,Date,Op......
    5. Save the master CSV file, FILE – SAVE AS. Make sure you add the extension .csv to the name e.g. Master CSV File.csv See screen shot of master CSV file with two security price histories.
    6. In Moneydance temporarily set the date format to the format type used in the CSV file. That is under Preference set date format to yyyy/MM/dd
    7. Once installed open the Security History Loader under Extensions.
    8. Click CHOOSE FILE and OPEN FILE the master CSV file.
    9. Set the select dropdowns as screen shot.
    10. Click LOAD DATA. See screen shot example of two securities.
    11. Click the box, bottom left, to tick all the SELECT boxes.
    12. Click SAVE SELECTED VALUES and CLOSE window.
    13. Reset required date format under preferences.

    Hope this helps.

  2. 2 Posted by feffer777 on 21 Oct, 2019 04:54 PM

    feffer777's Avatar

    Yes, Mike also suggested that in another thread. It's a way to get historical prices into MD, but it does involve considerable manual manipulation. MD already has a built in Import function for historical prices. One can (still) get a range of historical prices from Yahoo, download as a .csv text file and import that into MD (no extension needed) and it's not necessary to manipulate or even open the .csv file. Seems like far fewer steps. I haven't tried the Security_History_Loader extension, so I'm just theorizing. But I have used the method I described and it is pretty efficient with no failures so far.

    It's really too bad that we lost the previously inbuilt MD function to do this all automatically when Yahoo began to block it some years ago. These are all work-arounds. I wonder if InfiniteKind could simply pay Yahoo for the privilege of using their data. Would it be cost prohibitive?

  3. 3 Posted by feffer777 on 22 Oct, 2019 07:50 PM

    feffer777's Avatar

    Downloading Historical Prices from Yahoo efficiently. OK, playing with the "manual" method a bit more.
    1. Make a text list of securities you own with: name of security security_symbol The list should be sorted by MD Account > Portfolio_View > security
    2. Go to the Yahoo Finance site and paste the first security_symbol in your list into the "Quote Lookup" search space. A list of choices will drop down, click on your security...it should be at or near the top of the drop-down.
    3. The historical prices of your security fills the page. Edit the date range and hit "Apply" Note: pick a useful date range like the end of the last quarter to the present if you reconcile your portfolio quarterly.
    4. Then hit "download" and the dialog pop-up will give you a choice to open or save; choose "save." The file should be usefully named something like "QQQ.csv" Note, it is not necessary to open or manipulate this file in any way.
    5. Go to the next security on your list and repeat the process, etc. The date range you chose for the first security should stick on the Yahoo site, but you will need to hit, "Apply" for each new security before downloading it. This is really fast! But you can go faster than the Yahoo site! If you do, you'll get a page error. Do a page refresh. You'll then have to reset the date range. So go a bit slower and you'll get no errors.
    6. Once you have all your security.csv files, go back to MD > Accounts > Portfolio_View and click on the first security_symbol listed at the top left of the page. The Price_History of that security will open. Do an Import > from_file and navigate to the just downloaded file for that security, and hit "enter." The MD history will look unchanged, so close and re-open to refresh and your new prices will appear. Make sure that the top box with the "current" price is correct.
    7. Go back to MD > Accounts > Portfolio_View and repeat with the next security, etc.

    After creating my securities list, it took me about 20 minutes to get the histories of 25 securities from Yahoo. It took about the same amount of time to import them into MD. Not as great as the old now-broken (by Yahoo) built-in MD system, but not horrible for a once a quarter process. Note that your will overwrite some prices using this method. Historical prices should remain the same, so this won't be a problem unless you've manually changed a price for some reason. If you own mutual funds, their .csv file will have entries for "high," "low," etc. You could edit them to only have "Close" prices and the "Date" by opening the file in a spreadsheet like Excel, or like me, just import them "as is" and don't worry about the extra meaningless columns.

    Although I have a Yahoo user/password, I tested this process w/o logging in and it worked fine. I see that Yahoo has a "Premium" version of the site to monetize it, so this may eventually break this method, but for now it works on the "free" site. Any comment or corrections would be appreciated.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

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