Third-party Quote and Exchange Rate Program from hleofxquotes.

derekkent23's Avatar

derekkent23

01 Jan, 2018 11:16 AM

I am not support staff, just a user.

I am a UK investor and have found that Alpha Vantage provide very poor support for UK/Irish/Luxenberg etc, domiciled funds.
I am currently testing a stand-alone program that was originally developed to help out users of MS Money when Microsoft stopped supporting that product. The developer is in the beta stages of adapting it to work with Moneydance. When I say beta, this build as far as I can see is very stable and the quotes very consistent. No strange prices or for UK funds switching back and forward between Pence and Pounds. It has a lot of useful features. These include, obtaining prices from many stocks and fund quoted on different exchanges. The ability to convert quotes from one currency to another. Convert prices in Pence to Pounds, if required, for UK stocks and funds. Provide a means for importing all this data into Moneydance. Importing exchange rates.

Anyway, download the attached PDF, use the links to download the programs and follow the instructions on how to use.

Please keep this thread to this subject on how to use the program, hleofxquotes in association Mike Brays Security Price Loader and how to import the data into Moneydance.

Please provide feedback on how well UK, Mainland Europe, USA and Canadian stocks and funds are covered.

Hope this helps.

ALWAYS CHECK TO SEE IF THERE IS A NEW PDF IN A LATER POST!

Showing page 6 out of 8. View the first page

  1. 151 Posted by Mike Bray on 08 May, 2018 04:24 AM

    Mike Bray 's Avatar

    Hi Jonthaler

    There are a few things to think about.

    1. The ticker must match the ticker in MD including case unless the ‘ignore case’ check box
    is checked.
    2. It is worth reading my Wiki about the extension https://bitbucket.org/mikerb/moneydance-2015/wiki/Home
    it might help with what to select
    3. When you get the prices screen you will see which prices have been matched, you will be able
    to select the prices you want. Or use the check box at the bottom left of the screen, it will select
    all possible prices. Then click on save.
    Mike

    Sent from my iPad

  2. 152 Posted by derekkent23 on 08 May, 2018 07:40 AM

    derekkent23's Avatar

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

    Its Jon screen shot SLP_2 that concerns me.
    I have added his security “aapl” to my securities. When I run the Security Price Loader I get the attached screen shot. Note all my securities are listed but as only one corresponding symbol exist in his posted CSV file, so only “aapl” has a new price. His equivalent screen shot is not listing any securities.
    I am running windows, Jon Mac. Should the second window when run on Mac show all Jon securities regardless of if is a match in the CSV file as on windows?

  3. 153 Posted by derekkent23 on 08 May, 2018 09:33 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi Jonthaler

    Can you post a screen shot of the window you get under TOOLS – SECURITIES – APPLY – EDIT
    The equivalent of my screen shot.

  4. 154 Posted by Mike Bray on 08 May, 2018 03:17 PM

    Mike Bray 's Avatar

    Thanks Derek

    Jonthaler if you can reproduce this error please can you send me the
    error log. It isn't too easy to find on a Mac so the easiest way is to
    use Help/Console Window which will show you the error log, you can then
    copy and paste into a text file.

    Regards
    Mike

  5. 155 Posted by jonthaler on 08 May, 2018 03:46 PM

    jonthaler's Avatar

    Mike,
    I do not get any errors. SPL, produces no new entries in the console. I do see, for example, entries from the "Quotes and Exchange Rates" extension.

    BTW, I am running MD build 1671 and SPL build 2025, on a Mac (10.13.4).

    Derek,
    I didn't know that ticker symbols were case sensitive (an abomination, for which I blame Dennis Ritchie ;). I have fixed that – everything is upper case. I also checked the "Ignore Ticker Case" box. This did not have any effect.

    For unknown reasons, "Load Data" does not seem to match any of the ticker symbols. There is no list of found securities. I attach the simple CSV file I am using to test the program (just AAPL).

    Thanks for your help,
    Jon

  6. 156 Posted by derekkent23 on 08 May, 2018 04:11 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    With Apply set up as Edit Security window see? Apply screen shot.
    And Apply purchased as Screen shot Apply Purchase
    When I load your AAPL.CSV file
    I get this screen shot of the second screen of the security price loader
    Can you post the same screen shots?
    Can you also try Mikes Security History Loader extension?

  7. 157 Posted by Mike Bray on 08 May, 2018 04:13 PM

    Mike Bray 's Avatar

    Just thought do you have any buys against these stocks? If not click on
    the 'Include Zero Accounts'. By default I only load accounts that have
    actual holdings.

    Mike

  8. 158 Posted by jonthaler on 08 May, 2018 05:00 PM

    jonthaler's Avatar

    Mike,
    Checking "Include Zero Accounts" makes no difference. In any case, I have nonzero Apple holdings (but zero of some others in the MD portfolio).

    Derek,
    I attach SPL and SPH screen shots of the window that opens after "Load Data" is clicked. They are not the same, but neither shows that AAPL was matched. For completeness, I attach the AAPL.csv input file.

    A thought: I maintain three investment accounts in MD. Might that cause problems?

    Jon

  9. 159 Posted by derekkent23 on 08 May, 2018 05:08 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    I have the same security in more than one investment account, so no I don’t think that is the problem.

    Could you post the two other screen shots I requested in this post http://help.infinitekind.com/discussions/investments/3036-third-par...

    The equivalent of my first two screen shots.

  10. 160 Posted by jonthaler on 08 May, 2018 06:13 PM

    jonthaler's Avatar

    Here they are.

  11. 161 Posted by derekkent23 on 08 May, 2018 07:17 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    All my testing is on a windows 10 PC and your CSV files import flawlessly.

    Very strange its not working for you on your Mac.

    As far as I can see the CVS files are OK.
    The Edit Security window for Apply is OK.
    did you try Mikes test of ticking the 'Include Zero Accounts' box?
    Can you post a screen shot equivalent of my screen shot attached?

    After taking a FILE EXPORT BACKUP can you try the QIF import method described in my PDF.

  12. 162 Posted by derekkent23 on 08 May, 2018 07:24 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi Mike.
    If you still have access to a Mac were you able to test the AAPL.csv file with the Apply security set up as in jonthaler Edit Security window screen shot?

  13. 163 Posted by jonthaler on 08 May, 2018 10:00 PM

    jonthaler's Avatar

    Derek,

    Here are the screenshots. I'll try the QIF import and let you know.

    Jon

  14. 164 Posted by jonthaler on 08 May, 2018 10:25 PM

    jonthaler's Avatar

    Derek,

    QIF import worked. See the new AAPL history window. Is it correct to have volume = 0 and no high-low information? (They are not so important to me.)

    Thanks,
    Jon

  15. 165 Posted by Mike Bray on 09 May, 2018 06:37 AM

    Mike Bray 's Avatar

    I think I have got to the bottom of this. My extensions only loads securities that are allocated to an investment account even if they do not have any holdings. If you use Add Security on any Investment Account then it will be picked up. You do not have to register any buys.

    Please give this a try whilst I work on a change to load all securities.

  16. 166 Posted by derekkent23 on 09 May, 2018 06:45 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi jonthaler, really glad something has worked for you.

    The fact that the QIF import has worked is proof that there is nothing wrong in the way your securities are set up.

    Really don’t know why both Mikes extensions are not working for you, strange.

    The hleofxquotes program does not capture volume or high low prices. As you say it's not really important and just enlarges the size of your data set.

  17. 167 Posted by derekkent23 on 09 May, 2018 07:41 AM

    derekkent23's Avatar

    Hi Mike

    As you can read jonthaler has successfully used the QIF file exported from hleofxquotes to import prices into Moneydance.

    I did some tests importing QIF files and found.

    Once you add a security to moneydance if you don’t add it to an investment account and make a purchase you get the message “Error reading file: java.lang.NullPointerException” on attempting a QIF import. No imports occur even of correctly set up securities.

    If a security exists in the QIF file but does not exist in Moneydance at all, the import works for all other listed securities in the QIF file, provided they are purchased in Moneydance.

    This indicates that jonthaler securities have been entered correctly and a purchase made.

    This is further confirmed by the screen shots of the Security Price History window for Apply.

    It looks like something strange is still occurring when your extensions are used on a Mac.

  18. 168 Posted by Uffe on 09 May, 2018 07:36 PM

    Uffe's Avatar

    Hi Mike,

    Thanks for the ‘show on summary screen’ help, that solved most of my problems.

    I have one problem left. When I try and upload price for cryto currencies it seems that it will not upload to all of my cryto currencies. As you can see from below it will not upload price for Bitcoin Cash and Ripler even if i use the same notation as for all other currencies.

    I fun fact if I use DKKBTH=X instead of BTHDKK=X then your program will upload, but I cannot use it as it will upload the reciprocal rate.

    Do you know how we can solve the issue?

    Thanks in advance

    Uffe

  19. 169 Posted by derekkent23 on 10 May, 2018 09:43 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi Uffe.
    What approx. values are you hopping to see for Bitcoin Cash and Ripler on the summary page of Moneydance?

  20. 170 Posted by Uffe on 10 May, 2018 08:52 PM

    Uffe's Avatar

    Hi Derek,

    For Ripler I would expect something like 0.1961 and for Bitcoin Cash I would expect 0.0001 showing on the summary page of the Loader as it seems to show the reciprocal rate.

  21. 171 Posted by hleofxquotes on 11 May, 2018 06:15 PM

    hleofxquotes's Avatar

    Hi Uffe, Derek,

    Using Yahoo, I am getting

    BCH-EUR  Bitcoin Cash / BCC EUR  1171.2900 EUR
    EURDKK=X    EUR/DKK 7.4517574 DKK
    XRP-EUR Ripple EUR  0.5769 EUR
    

    and converted to DKK as

    BCH-EUR  Bitcoin Cash / BCC EUR  8728.16892505 DKK
    EURDKK=X    EUR/DKK 7.4517574 DKK
    XRP-EUR Ripple EUR  4.29891884 DKK
    

    Using symbols

    XRP-EUR
    BCH-EUR
    EURDKK=X
    

    Are the values returned from Yahoo not correct? Or the conversion not correct?

  22. 172 Posted by hleofxquotes on 11 May, 2018 06:20 PM

    hleofxquotes's Avatar

    ^^^ and the associated exported *.csv

    Price,Ticker_ISIN,Date
    "8728.16892505","BCH-EUR","05/11/2018"
    "7.4517574","EURDKK=X","05/11/2018"
    "4.29891884","XRP-EUR","05/11/2018"
    "0.13419653","DKKEUR=X","05/11/2018"
    
  23. 173 Posted by derekkent23 on 11 May, 2018 07:11 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi hleofxquotes

    Lets take Ripple EUR as an example.
    Your line is XRP-EUR
    Giving "XRP-EUR and 4.29891884 the value is the inverse of what it should be.
    As it is an exchange rate it needs to be in the format DKKXRP=X in the converted window and in the CSV file before Mike extension can import it into Moneydance, not XRP-EUR.

    Uffe line
    XRPDKK=X/XRP-EUR
    Results in XRPDKK=X Ripple EUR 4.82448091 DKK but is incorrectly formatted in the CSV file as XRPDKK=X. For Mikes extension to work the Moneydance base currency must come first. DKKXRP=X

    This line gives the correct format
    DKKXRP=X/XRP-EUR
    Results in DKKXRP=X Ripple EUR 4.82448091 DKK and can be imported using Mikes extension.
    However again the values is the inverse of the true value i.e. 4.82448091 DKK should be 0.20727619 DKK

    So, the question is there another way to format the line or failing that a way to add an attribute that will invert the value?

    I must confess I don’t use crypto currencies and when I looked at the value returned and then converted it into DKK 4.82448091 looked correct. However, if I go to a site such as https://www.coingecko.com/en/price_charts/ripple/dkk
    I get XPR 0.20727619 DKK 1

    CONFUSED!

  24. 174 Posted by Uffe on 11 May, 2018 08:57 PM

    Uffe's Avatar

    Hi Derek,

    I think the issue is more in Mike's loader program then in hleofxquotes downloader. The reason for this is the following:

    Just taking an example with normal currencies USD and DKK:

    As I have base currency DKK, the quote for USD would be e.g. 6.2372

    If I use the format USDDKK=X the loader program will show the inverted quote 0.1603 but update MD with 6.2372

    If I use the format DKKUSD=X the loader program will show the quote 6.2372 but update MD with the inverted 0.1603

    So from this point of view the correct format is USDDKK=X as that gives me the correct quote in MD.

    I would prefer it is the loader program would show the quote it would actually update MD with as that is less confusing.

    The problem with Ripler and Bitcoin Cash is that the loader program seems only to recognize them in the format DKKXRP=X and DKKBTH=X - and that I cannot use as that will give me the inverted quotes in MD.

  25. 175 Posted by hleofxquotes on 11 May, 2018 09:42 PM

    hleofxquotes's Avatar

    I see ... so the data is correct. A given 1 XRP will cost 4.xxx DKK (at time of Yahoo retrieval, that calculated value is 4.29891884)

    The value at coingecko confims that:
    https://www.coingecko.com/en/price_charts/ripple/dkk

    But we need to write to Mike CSV as

    "0.2326166269","DKKXRP=X","05/11/2018"

    correct?

    There are 3 currencies here:
    XRP, EUR, DKK

    Quote source give
    XRP -> EUR
    EUR -> DKK

    We need to convert XRP -> EUR -> DKK

    And Mike extension needs it in a form of DKKXRP

    Let me chew on this.

  26. 176 Posted by derekkent23 on 11 May, 2018 09:55 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi Uffe

    In your example you can use USDDKK=X or DKKUSD=X in hleofxquotes program, it does not matter. If you look into the CSV file you will see hleofxquotes includes values for both USDDKK=X and DKKUSD=X

    Mike only reads exchange rates that start with Moneydances base currency. In your case DKKUSD=X

    If you look at the Price History window in Moneydance you will see that one USD = 6.2372 DKK, which is correct.

    The summary page is more confusing as by clicking on 1/x you invert all the exchange rates. There is no indication which way round the exchange rate is expressed, one DKK = so many units of the exchange currency or one unit of the exchange currency = so many DKK.

    To me expressing all the currency rates to one unit of the base currency make the most sense.

    In your case on the summary page US DOLLAR 0.1603 That is one DKK = 0.1603 USD

  27. 177 Posted by derekkent23 on 11 May, 2018 10:02 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi hleofxquotes

    Yes, you are correct.
    You have outlined the issue very clearly.

  28. 178 Posted by Uffe on 12 May, 2018 06:08 AM

    Uffe's Avatar

    Hi Derek,

    You are correct, I had not looked into the CSV file and seen that all the normal currency pairs have two entries.

  29. 179 Posted by hleofxquotes on 12 May, 2018 02:42 PM

    hleofxquotes's Avatar

    I will do a quick fix for this (I will add another comment to explain more ...)

    https://github.com/hleofxquotes/hleofxquotes/tree/master/dist/test/...

    Uffe,
    Use the crypto symbols such as XRP-EUR as-is. Don't use DKKXRP=X/XRP-EUR

    My test using Yahoo with the following symbols

    XRP-EUR
    BCH-EUR
    EURDKK=X
    
    Exported MD CSV file
    Price,Ticker_ISIN,Date
    "8894.13464258","BCH-EUR","05/12/2018"
    "7.454893","EURDKK=X","05/11/2018"
    "4.23959765","XRP-EUR","05/12/2018"
    "0.13414009","DKKEUR=X","05/12/2018"
    "8894.13464258","BCHDKK=X","05/12/2018"
    "0.00011243","DKKBCH=X","05/12/2018"
    "4.23959765","XRPDKK=X","05/12/2018"
    "0.23587144","DKKXRP=X","05/12/2018"
    
  30. 180 Posted by hleofxquotes on 12 May, 2018 02:59 PM

    hleofxquotes's Avatar

    On handling the CryptoCompare format symbol such as BCH-EUR (available from Yahoo quote source)

    Prior to crypto support, we general have two types of symbols

    • stock, fund ...
    • currency rate

    For the former, if possible, we will try to convert to the base currency.
    For the latter, we leave the rate alone (no conversion)

    For crypto such as BCH-EUR, if falls somewhere in between. It is a currency rate but user really want BCH-DKK (need conversion).

    I think the right fix is to treat BCH-EUR as a currency rate but add an option to ensure that at minimum we will always generate a rate to the base currency. For example

    • If we have BCH-EUR and EURDKK=X, and base rate is DKK, we will generate BCHDKK=X
    • If we have BCH-EUR and no EURDKK=X, then no action
    • If we have BCH-EUR, EURUSD=X, USDGPB=X and base rate is GBP, we will generate BCDGPB=X

    This way BCH-EUR crypto symbol will be treated as a normal currency rate and generated rate will show up in the converted table so it will be clear to user.

    Doing this will require a bit more work.

Reply to this discussion

Internal reply

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

Attaching KB article:

»

Already uploaded files

  • Hleofxquotes_Beta_Program_used_with_Moneydance_V2.pdf 565 KB

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