Reply to topic

How to Bulk Update Multiple Online Stock and Currency Prices

mdolnik
Registered Member
Posts
10
Karma
0
This post is outdated, refer to the post below

I have been able to download today's prices for all of my investments using the following data in Online Quotes:
Code: Select all
Name: Yahoo API
URL: https://query1.finance.yahoo.com/v7/finance/quote?fields=regularMarketPrice&symbols=%1
CSV Url: [empty]
Symbol: %1
Price: "regularMarketPrice":((\d+|\d{1,3}(?:[,]\d{3})).\d+)
Date: "regularMarketTime":([\d]+)
Date Format: [empty]
Skip HTML stripping: [checked]


But this will only work for updating today's date, when I attempt to select a date range in "Update Stock and Currency Prices" I get the error "CSV source Yahoo API does not exist."

This is obviously because I have CSV Url as blank above.
My question is, does anyone have any working CSV URLs that they are having success with? All of the following stock CSV URLs that KMyMoney comes with just result in errors:
  • http://www.nasdaqbaltic.com/market/?instrument=%1&pg=details&tab=historical&lang=en&date=&start=%d.%m.%y&end=%d.%m.%y&pg=details&pg2=equity&downloadcsv=1&csv_style=english
  • http://stooq.pl/q/d/l/?s=%1&d1=%y%m%d&d2=%y%m%d&i=d&c=1
  • http://stooq.pl/q/d/l/?s=%1%2&d1=%y%m%d&d2=%y%m%d&i=d&c=1

This is likely because the URLs above lead to error pages or redirects (when filling in the placeholders), so it's safe to say they're outdated.

I have found a Yahoo Finance url which points to a CSV file download: https://query1.finance.yahoo.com/v7/finance/download/goog?period1=1554307723&period2=1585930123&interval=1d&events=history (link is for symbol goog) but when using this URL (with hard-coded date/symbol) in the CSV URL and updating a date range
I still get the error "CSV source Yahoo API does not exist.".

Related posts:
viewtopic.php?f=69&t=156160&p=407954

Last edited by mdolnik on Thu Apr 16, 2020 8:05 pm, edited 1 time in total.
mdolnik
Registered Member
Posts
10
Karma
0
I figured it out... Annoyingly there's no documentation for this process, so I may be going about it wrong and I'm not sure the exact order needed, but...

  1. Make temporary file or backup of your KMyMoney file, I don't want to be responsible if this messes anything up.
  2. Add/Update the "Yahoo API" Online Quote to the following configuration:
    Code: Select all
    Name: Yahoo API
    URL: https://query1.finance.yahoo.com/v7/finance/quote?fields=regularMarketPrice&symbols=%1
    CSV Url: http://query1.finance.yahoo.com/v7/finance/download/%1?period1=0&period2=9999999999&interval=1d&events=history
    Symbol: %1
    Price: "regularMarketPrice":((\d+|\d{1,3}(?:[,]\d{3})).\d+)
    Date: "regularMarketTime":([\d]+)
    Date Format: [empty]
    Skip HTML stripping: [checked]
  3. Make sure you press the Update and Dump CSV buttons (I honestly don't know what the Dump CSV button does, I can't find any documentation)
  4. Go to https://query1.finance.yahoo.com/v7/finance/download/goog?period1=1584307723&period2=1585930123&interval=1d&events=history and download the CSV (the fact it's for goog does not matter, we just need the format of the CSV)
  5. In KMyMoney, go to File > Import > CSV
  6. Select Stock Prices
  7. In the text box, enter Yahoo API or whatever you named the Online Quote above (I believe you have to ensure the name matches)
  8. Click Select File and choose the CSV you downloaded earlier
  9. Configure the following settings:
    Code: Select all
    Encoding: UTF-8
    Field Delimiter: comma
    Text Delimiter: quote
  10. Click Next and configure the following settings:
    Code: Select all
    Start line: 2
    End line: (default aka the maximum allowed)
  11. Click Next and configure the following settings:
    Code: Select all
    Date: 1 (Date Column)
    Price: 5 (Close Column)
  12. Click Next and configure the following settings:
    Code: Select all
    New symbol: TEMP
    New name: TEMP
  13. Click OK and configure the following settings:
    Code: Select all
    Decimal Symbol: auto
    Thousands Symbol: [blank]
    Date format: y m d
  14. Click Import CSV
  15. Go to Investments > Securities
  16. Click TEMP then click the Delete button and confirm Yes to both warnings
  17. Click Import CSV
  18. Go to Investments > Equities and ensure all of them are using Yahoo API or whatever you named the Online Quote above.
  19. Go to Tools > Update Stock and Currency Prices, select a single equity, change the date range to something else and select Update Selected (and cross your fingers)
  20. If the last step didn't work, update the Yahoo API (or whatever you named it) Online Quote and press the Update (if applicable) and Dump CSV button. (Pressing Dump CSV should now ask you if you want to overwrite the CSV Profile, click Yes)
  21. Go to Tools > Update Stock and Currency Prices, select a single equity, change the date range to something else and select Update Selected (and cross your fingers)

A few notes on the above:
  • For unknown reasons, in order to update historical prices, you cannot click Update All when selecting a date range, it won't fail, but it won't update anything but today's prices. You must select the date range, then select all (or any) equities you want to update, then click Update Selected.
  • The CSV URL I am using uses Unix time stamps to tell Yahoo what date range we want.
  • I have set the "to" date (period2) to 9999999999 which is the year 2286 but will only (obviously) provide prices up until the current date.
  • I have set the "from" date (period1) to 0 which essentially tells Yahoo to get ALL known dates for the security. I do not know the implications of how much load this will cause for securities that go back a long time (eg for goog, all dates from 2004 ends up being a 288kb csv file). So I would HIGHLY SUGGEST you set this to the earliest date you would like to get transactions from. You can use something like http://www.unixtimestamp.com to calculate the number.
  • Ideally we should be passing in the unix timestamp dates into the URL using placeholders, but the only available placeholders that I know of are %y, %m, %d, %H, %M, and %S. This post from ipwizard seems to imply there is no ability to have unix timestamps as an option.

 
Reply to topic

Bookmarks



Who is online

Registered users: Baidu [Spider], Bing [Bot], cylverbak, funkyskywalker, Google [Bot], ineuw, Majestic-12 [Bot], maxmu, MRK0017, Sogou [Bot], sylvainrousseau, Yahoo [Bot]