How to load currency history data use the "load security history" extension

Chris's Avatar

Chris

17 Nov, 2020 07:18 PM

I now have one security that isn't in my base currency. I successfully use the "load security history" extension to load price history for all securities, including the one that is in USD rather than GBP. I believe this extension can be used to also load daily exchange rates, which I now need for all my reporting to be accurate. But I've no idea what needs to be in the .csv file for the extension to recognise the exchange rate data.

I tried using the same four colums I was using for securities, "Ticker,Date,Volume,Close" with ticker values of USD and no values for VOLUME, but that didn't work.

There is something in the documentation about putting in "fffttt=X" where fff is the from currency and ttt is to currency, but no indication of what column it goes in. I have no idea if X is supposed to be typed literally, or if it's meant to be a placeholder for an actual exchange rate.

A two-line example of an acceptable .csv file with one header and one data record would help a lot.

  1. 1 Posted by Chris on 17 Nov, 2020 07:38 PM

    Chris's Avatar

    I've worked out the answer to my own question. The ticker needs to be "GBPUSD=X".

  2. 2 Posted by derekkent23 on 17 Nov, 2020 08:35 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Chris

    Have you considered using Quote Loader to obtain Security prices an exchange rates automatically. It an extension by the same developer as "load security history" extension, Mike Bray.

    For information see post 63 at
    https://infinitekind.tenderapp.com/discussions/investments/4914-the...

    Download the PDF attached to posts 63 and have a read.

    To get an overview take a look at the YouTube videos by using the links on page 1 and 2 of the PDF.

    Let us know how you get on or if you need help just post.

    Hope this helps.

  3. 3 Posted by Chris on 17 Nov, 2020 11:30 PM

    Chris's Avatar

    I looked at the documentation, it looks like it can in theory download historical information, which is new since I last looked at it. But it will only do it for ETF's (if getting data from FT) so it won't give me data for many of my holdings.

    I mention FT because I'm extremely reluctant to even test using Yahoo again. My past experience with Yahoo data is not just that prices (for a particular ticker) would randomly fluctuate between being quoted in pounds or pence, but that the actual currency in their price histories would switch between Euros and pounds or dollars and pounds without warning. Literally within one download spanning a period of say 90 days, there would be period where the prices were one currency, then another period where they were in a different currency. Every time I use Yahoo to see whether it can be relied upon, it messes up my historical data by replacing it with nonsense, and I have to go back to my normal methods to repair the data.

  4. 4 Posted by Chris on 18 Nov, 2020 12:00 AM

    Chris's Avatar

    I've decided to try the quote loader with Yahoo prices. Since it apparently only loads prices I don't already have, it can't cause too much harm if the Yahoo prices are as bad as they used to be.

  5. 5 Posted by derekkent23 on 18 Nov, 2020 08:41 AM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Chris

    I would be interested in your finding.

    Quote Loader has been widely used since 2018 and there has not been a single post I can remember describing the issues you found with Yahoo. Alfa Vantage as used by the extension “Quote and Exchange Rate Updater” is another thing. I abandoned it because it regally switched from pounds to pence then back again.

    Quote Loader checks the unit of currency and in the case of any securities quoted in pence converts then to pounds to conform to the way Moneydance stores sterling.

    Have Fun, Stay Safe.

  6. 6 Posted by Chris on 19 Nov, 2020 08:47 AM

    Chris's Avatar

    Quote loader has loaded ticker IESU wrongly. It has recorded a closing price for 18/11/2020 of £0.0317 instead of £3.17.

    Looking at the Yahoo historical data page, the page says prices are quoted in pence, the current price is quoted pence, but the historical prices on the same page are quoted in pounds. At least most near the top are, as you run your eye down the page you will see that some prices are in pence, but most are in pounds.

    The Yahoo data is garbage and I can't see anything quote loader can do to overcome this. This is for an ishares tracker ETF, so hardly some obscure security that no-one has ever looked at before.

    https://uk.finance.yahoo.com/quote/IESU.L/history?p=IESU.L&.tsrc=fin-srch

  7. 7 Posted by Chris on 19 Nov, 2020 08:57 AM

    Chris's Avatar

    FT HD does not retrieve data for that ticker. (It does work for some other random ticker I tried, so it looks like a ticker-specfic issue.)

    The only data source that currently brings back a correct figure is "Yahoo" (without HD.)

  8. 8 Posted by Chris on 19 Nov, 2020 09:00 AM

    Chris's Avatar

    You may be wondering what my alternative source of correct data is. I use Googlefinance function in a google spreadsheet to get 90 days of historical data for each of my holdings. I have a daily job that downloads the spreadsheet to a local .csv file, and runs it through some code to produce an input file that the price history loader can use. I obviously have to invoke the price history loader manually at least once every 90 days to keep my data up-to-date.

  9. 9 Posted by Chris on 19 Nov, 2020 09:13 AM

    Chris's Avatar

    Sorry, looking at IESU some more, I realise the error is worse/more complicated than I initially thought. Google is telling me the correct price is £2.3915. So I'm guessing that maybe some other currency (probably dollars) is being quoted, though not even consistently.

  10. 10 Posted by Chris on 19 Nov, 2020 09:16 AM

    Chris's Avatar

    My guess is that the Yahoo prices I initially thought were in pounds are actually in dollars. So the price history is mingling prices in dollars with prices in pence, while claiming that all prices are in pence.

  11. 11 Posted by Chris on 19 Nov, 2020 09:28 AM

    Chris's Avatar

    Another ticker I use, VFEM (Vanguard emerging markets tracker ETF) also appears to have Yahoo historical prices that vary randomly between dollars and pounds.

  12. 12 Posted by Mike Bray on 19 Nov, 2020 12:43 PM

    Mike Bray 's Avatar

    So looking at FT and FT HD the prices for IESU are in GBX and seem to be
    correct (today 230.6 = £2.306).  Yahoo is in GBp which is the equivalent
    of GBX.  Its current price is also 230.6 = £2.306.  Yahoo HD is
    incorrect. Starting on 21/9/2020 it is recording a strange prices on
    some days.  Quote Loader will only load what is there and this is an
    issue with Yahoo Historical Data.  For IESU your could delete the prices
    from 21/9 to present, run QL, change all sources to Do Not Load, change
    the source for IESU to FT HD and get prices.  Select the returned prices
    and click on Save Selected Prices (DO NOT SAVE PARAMETERS).

    Remember, FT does not allow access to historical data for shares, only
    funds.

    Hope this helps.
    Mike (not support)

  13. 13 Posted by Chris on 19 Nov, 2020 12:59 PM

    Chris's Avatar

    I noticed that there are (correct) historical prices for IESU and VFEM on the FT web site, but selecting FT HD brings back nothing (red box) for me. FT (without HD) doesn't work either. Other securities I tried with FT HD do work, not sure why these don't.

  14. 14 Posted by derekkent23 on 19 Nov, 2020 01:00 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Chris.

    On your two example securities I have to agree Yahoo has gone crazy on historical prices. As you say for IESU Yahoo historical data is sometime in Pound, Pence or US Dollars.
    However, both seen to return correct prices when the Quote Loader source is FT HD, see screen shot.

    Hope this helps.

  15. 15 Posted by derekkent23 on 19 Nov, 2020 01:02 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Chris.

    Screen shot

    Hope this helps.

  16. 16 Posted by Chris on 19 Nov, 2020 01:05 PM

    Chris's Avatar

    Thanks Derek, after looking at your screenshot I removed the exchange information for those two, and now FT HD works.

  17. 17 Posted by derekkent23 on 19 Nov, 2020 01:09 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Chris.

    Great.
    I hope Yahoo get there act together, but at least FT HD works.

    Have Fun, Stay Safe.

  18. Maddy closed this discussion on 19 Nov, 2020 03:51 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

Recent Discussions

27 Jan, 2021 10:58 AM
27 Jan, 2021 10:37 AM
27 Jan, 2021 10:09 AM
27 Jan, 2021 09:02 AM
27 Jan, 2021 06:55 AM