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 4 out of 8. View the first page

  1. 91 Posted by derekkent23 on 07 Apr, 2018 03:02 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi Uxr.

    Thinking further. I don't think hleofxquotes inverting the value in the CSV export file would help, even if he increased the number of decimal places, as it looks like the Security Price Loaded can only handle 4 decimal places.

    Most quote sources including Yahoo seem to quote GBPBTC to no more than 4 decimal places.

  2. 92 Posted by UxR on 07 Apr, 2018 03:22 PM

    UxR's Avatar

    Hi Derek.

    I see. I think this might then call for both Security Price Loader and hleofxquotes to be updated, which might be too onerous for both developers.

    I think in this case manual updates are still the best bet for cryptos then. Whilst 4 decimals places are probably enough for most fiat currencies, GBP/BTC 0.0002 could mean a rate of anything between BTC/GBP 4000.01 and 5000, depending on how one decides to round up the digits.

  3. 93 Posted by hleofxquotes on 07 Apr, 2018 03:46 PM

    hleofxquotes's Avatar

    @UxR, I think we could accommodate this by allow user to specify the decimal digits (currently default to 4).

  4. 94 Posted by hleofxquotes on 07 Apr, 2018 03:50 PM

    hleofxquotes's Avatar

    As a quick test, I increase the decimal digits to 8
    The CSV file will look like this

    Price,Ticker_ISIN
    "0.00020149","GBPBTC=X"
    "0.00365924","GBPETH=X"
    "0.01194150","GBPLTC=X"
    
  5. 95 Posted by hleofxquotes on 07 Apr, 2018 03:53 PM

    hleofxquotes's Avatar

    @derekkent23; though the Yahoo shows only 4 digits (0.0002), look like the data I was able to query does have more precision.

    If the 'Security Price Loader' does not support higher precision, then you are right. It is moot point.

  6. 96 Posted by Mike Bray on 07 Apr, 2018 04:36 PM

    Mike Bray 's Avatar

    I will have a look at it for Security Price Loader, though it will be a couple of weeks until I can get to it.
    Mike

    Sent from my iPad

  7. 97 Posted by Mike Bray on 07 Apr, 2018 04:38 PM

    Mike Bray 's Avatar

    You could manually amend a csv file and try it to see if the loader accepts the extra precision.
    Mike

    Sent from my iPad

  8. 98 Posted by derekkent23 on 07 Apr, 2018 04:53 PM

    derekkent23's Avatar

    Hi Mike, good thought. I looks like it does in fact import more than 4 decimal places

  9. 99 Posted by derekkent23 on 07 Apr, 2018 04:58 PM

    derekkent23's Avatar

    Looks like Summary page will display 6 decimal places, the six being rounded from the 7th after Security Price Loader Import!

  10. 100 Posted by derekkent23 on 07 Apr, 2018 05:21 PM

    derekkent23's Avatar

    So, taking the value shown in hleofxquotes screen shot 0.00020149 and importing via the extension the Moneydance summary page reads 0.000201 and the price history window 4963.02546 (inverse 0.00020149) current.

    Mike it would be good at some time if you could increase the number of decimal places displayed in your extension from 4 to 8.

  11. 101 Posted by Mike Bray on 11 Apr, 2018 01:04 PM

    Mike Bray 's Avatar

    I have added a new parameter. You can choose the precision from 4 to 8 DP. Defaults to 4. As an aside I have improved the screen size for the prices screen. It also remembers the size you set it at. Let me know if there are errors.

  12. 102 Posted by derekkent23 on 11 Apr, 2018 02:42 PM

    derekkent23's Avatar

    Hi Mike, thanks for your efforts, seems to be working without any issues.

    Another step forward.

  13. 103 Posted by UxR on 11 Apr, 2018 05:39 PM

    UxR's Avatar

    Hi Mike - thanks for this.

    Derek - how did you get the data from Yahoo to download more than 4 digits for GBPBTC=X? I can't seem to find this setting in the hleOfxQuotes app.

  14. 104 Posted by derekkent23 on 11 Apr, 2018 07:04 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi UxR

    Hleofxquotes is working on a new release build, some aspects to finalise.
    There is a test build 200 that you can try. Remember this is a test build, so use with care.
    The number of decimal places is set under EDIT – QUOTES – DECIMAL DIGITS. This may change in the release build.

    So for test build 200 see http://help.infinitekind.com/discussions/investments/3036-third-par...

    Hope this helps.

  15. 105 Posted by derekkent23 on 21 Apr, 2018 07:14 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    NEW BUILD RELEASE “Build_20180415_205”.

    This free stand-alone program was originally developed to help out users of MS Money when Microsoft stopped supporting that product. The developer has adapted it to work with Moneydance and is actively supporting the program. This build, is very stable and the quotes very consistent with the option of two sources, not Alpha Vantage. No strange prices. The program delivers the last traded price, not a price history, thus it does not overwrite your existing history. It provides quote for stocks & funds, from many stock exchanges US, Canada, UK, Europe and Australia to name but a few. Provide a means for importing all this data into Moneydance. You can also import exchange rates along with your security prices. The program is in java, like Moneydance and thus runs on Windows, Mac and Linux computers.

    UPDATES
    1. Add a feature to allow the range of decimal places to be set by the user. Securities return no more than 4 decimal places, most less. Exchange rates can return more. Cryptocurrencies supported by this program Bitcoin, Ethereum and Litecoin can require even more. To accommodate this, you can now set the minimum and maximum number of decimal place. The default settings are minimum decimal places 4 and maximum 8. With these setting values of less than the minimum will have trailing zeros added. Values with more than the maximum will be rounded and truncated. For example, 143.4 becomes 143.4000 whereas .0327964557 becomes 0.03279646. This approach helps readability in the results tabs.
    2. Fix bug associated with quotes returned in sterling from Yahoo.
    3. If quote returned is a non-positive price value enter result into Not Found Prices tab.
    4. Fix bug associated with adding attributes to symbols that have been opened from a saved text file.
    5. Clarify in the PDF the method to be used if the Moneydance and quote source symbols are different.

    Note: If you have securities in non-base currencies and want to import exchange rates you must update to the latest released build of Moneydance at https://infinitekind.com/download-moneydance-personal-finance-software as this fixes bugs related to this feature. You must also run the latest build of the Security Price Loader extension, see PDF for link.

    Download the attached updated 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 try this program and if you find it useful please take the time to post comments and outline any problems you may find.

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

    Hope this helps.

    Note: the link in the PDF takes you to the programmer, hleofxquotes, development site. This site lists the current released build for download, along with a number of other folders. The folder “old” contains old released builds. The folder “test” contains builds under development that are not ready for release and may contain bugs.

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

  16. 106 Posted by JimRB on 22 Apr, 2018 03:04 PM

    JimRB's Avatar

    Hi Mike
    I have used previous versions, and have just updated to the latest v205 as above. I cannot complete the import. The CSV saves OK, and looks as before. The security price loader installs and opens OK. I can choose and load the file, but clicking the Load Data button does nothing.
    I have reverted to the SecurityPriceLoader that I downloaded on 1 December 2017 which still loads the data and updates MoneyDance.

    Suggestions:
    One thing I have noticed is that as mentioned before, I have to get most of the data data from Yahoo plus 2 from FT. The Yahoo securities all need -2 as a multiplier, but the FT ones need 0. Can that be set somewhere in hleofxquotes or remembered by the updater? I make a point of saving the FT ones with a different filename of mdQuotesFT.csv, rather than just mdQuotes.csv - it would be useful if hleofxquotes could do that automatically.

  17. 107 Posted by derekkent23 on 22 Apr, 2018 04:29 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi, JimRB

    Can you post a copy of the two CSV files you are having problems with so I can test?
    The securities prices from Yahoo does Moneydance expect these to be in pence?
    The securities prices from FT does Moneydance expect these to be in Pounds?
    The build of the Security Price Loader that you are having issues with is it 2022?
    With this information I should be able to find a solution for you.

  18. 108 Posted by JimRB on 22 Apr, 2018 06:24 PM

    JimRB's Avatar

    Hi Derek.
    Thanks for replying so quickly.

    Two csv files enclosed
    MoneyDance is expecting all prices in pounds
    hleofxquotes tab for converted prices divides by 100 and shows pounds for Yahoo, but the csv shows all of the prices in pence.
    The tab for FT shows the downloaded prices in pence, but in the converted prices tab, divides the stock price by 100, and leaves the Unit Trust price alone - which it should as it is denominated in pounds. However, the csv for the FT quotes actually includes the pounds (the converted prices), not the pence.

    The prices in the FT section are odd, and different from any in the Yahoo section :-

    • BAE Systems, which has an odd symbol of 'BA.' with a dot but without a suffix, and

    • Pictet-Biotech which has a prefix of LU and no suffix.

    The loader that I am having issues with is the latest I just downloaded today from https://bitbucket.org/mikerb/moneydance-2015/downloads/ . I can't find anywhere that tells me which version that is. In MoneyDance it shows all of your priceloader extensions as v1

    I hope this makes sense...

    Jim

  19. 109 Posted by derekkent23 on 22 Apr, 2018 06:57 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi jimRB

    One point at a time. The prices in the CSV file for Yahoo should be in GBP as shown in the Converted Prices tab. I ran three of your securities and they were correctly converted to GBP. This was one of the bugs fixed in the way Yahoo returns sterling prices. The values in you Yahoo CSV file do not match those in the Converted Prices tab making me think the file was created at a earlier date with build 199. Can you check.

  20. 110 Posted by derekkent23 on 22 Apr, 2018 07:57 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi jimRB
    Next point. For BA. FT returns the price in Pence (GBX) and for the two LU securities in Pounds (GBP), as in the Quote Source Prices tab. Thus, the only price that needs to be converted to pounds is the BA. price, so that in the Converted Prices tab all prices are in pounds. In the CSV file for FT prices are GBP as shown in the Converted Prices tab. As there was no bug in the way FT returns sterling prices the CSV file should be correct.

    The build number for the Security Price Loader is shown on Top Left of its setup window. See screen shots in PDF.
    Can you confirm it was both CSV files you could not load?
    I set up your 3 FT securities in Moneydance and used your CSV file and Security Price Loader 2022 and they imported correctly. The one thing I did notice was after clicking Load Data the screen that appeared was very small and I had to drag its edges to make it a readable size. Can you try 2022 again and check this.

    Your point about Yahoo and FT having different default CSV file names I will put to hleofxquotes. He is working on an upgrade that will merge the two outputs into a single CSV file, but the programming in somewhat complex, setting the needed rules etc and he has only so much free time to devote to the project.

    If there is anything I have missed post again and also let me know how you get on.

    Hope this helps.

  21. 111 Posted by derekkent23 on 22 Apr, 2018 08:37 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi jimRB

    Two further points. It looks like all the securities in your Yahoo tab could be move to the FT tab. Just remove the “.L” and for AV.L just remove the “L” to give AV. Then you would have only one CSV file to import each time. All the symbols work in FT tab, but did not check very returned Security Name and price to make sure, but it would be worth your time to test. By the way if in Moneydance you have the “.L” and don’t wont to edit Moneydance you can enter “MoneydanceSymbol/QuoteSourceSymbol”
    e.g. LU0320646986.L/LU0320646986 instead of just LU0320646986 in the FT tab and the “.L” will be present in the Converted Prices tab and the CSV file as outlined in the PDF
    You should only enter the exchange rate required starting with GBP. E.g. GBPUSD no need for USDGBP. When using FT for exchange rate no need for =X on end.

    Hope this helps.

  22. 112 Posted by JimRB on 23 Apr, 2018 03:45 PM

    JimRB's Avatar

    Hi Derek

    Post 109 - Odd. The file date is 02/04/2018. But I have saved data as csv files twice since then. Where have they gone?

    Post 110 - Loader 2022: You said "The one thing I did notice was after clicking Load Data the screen that appeared was very small and I had to drag its edges to make it a readable size. "
    Aha!
    After I had reinstalled this loader, it still would not load data at all until I unticked "Remove exchange from ticker".
    Then I found the pop up window was almost off the screen at the top. I dragged it back on to find it showing only the date and two buttons, then expanded it to show the securities as well. I'm now using my 24 inch screen rather than the laptop, and it is 1200 deep not 1080 - perhaps that is why I was now able to see the pop up? Having made it visible, and closed the loader, using the loader again has it pop up fully visible where I had moved it to.

  23. 113 Posted by Mike Bray on 23 Apr, 2018 03:48 PM

    Mike Bray 's Avatar

    Sorry about that folks, I thought I was making it more usable. I had
    been asked on one of my other extensions to remember the screen size. I
    added this to this extension but on first use it comes up very small. I
    will try and fix that.
    Mike

  24. 114 Posted by derekkent23 on 23 Apr, 2018 04:16 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Thanks Mike.

    Hi jimRB

    When you install a new build of the program it still saves the CSV to the file location it was previously using. To change the location, use directory buttons when saving. If you still can’t find the new save rename the one you have and do a search for “mdQuotes.csv”.

    When you find the new CSV file please confirm if you still have any problems.

    Next build will have different default names for the two CSV files.

  25. 115 Posted by JimRB on 23 Apr, 2018 04:43 PM

    JimRB's Avatar

    Hi Derek

    Post 111 - I had tried to simplify them back in February when I started with hleofxquotes, but could not get all of them in either Yahoo or FT. At the time, there were 3 failed in Yahoo and more in FT, so I chose to leave most in Yahoo.

    Back then I could not get BAE Systems in FT, but a month ago, I found BAE systems was BA.
    Trying all of my securities in the FT tab of hleofxquotes just now still didn't find National Grid, until I used the same trick to just put NG. without the L, like AV. that you identified, which finds it - so yes, all of my securities are now in the FT tab.
    None of those three with a dot but without an exchange letter would update in Moneydance using the loader. They stayed untickable!

    I don't have ".L" in Moneydance for any symbol, but I extrapolated on your note about “MoneydanceSymbol/QuoteSourceSymbol”
    e.g. LU0320646986.L/LU0320646986 instead of just LU0320646986 in the FT tab.

    The FT tab in hleofxquotes needed

    • BA/BA.

    • AV/AV.

    • NG/NG.

    I didn't reverse teh exchange rates! In the Symbols section of hleofxquotes, I only have exchange rates with GBP first. Those are shown the same in the Quote source prices tab, but in the MD>Save csv file, they have =X added, and are duplicated the reverse way round.

    Thank you for your time, your help and for your pointers. All now seems to be working delightfully.

  26. 116 Posted by derekkent23 on 23 Apr, 2018 04:53 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi jimRB

    Really glad the process is now working for you and giving you what you need.

    Keep a lookout for the next build coming soon!

  27. 117 Posted by JimRB on 23 Apr, 2018 05:10 PM

    JimRB's Avatar

    Thanks Mike re post 113 - it was a good idea for improvement.

    Thanks Derek re Post 114 - I had already searched as you now suggest, and found nothing on either hard disk, so can only assume I was dreaming about saving them. Perhaps I opened them in LibreOffice Calc and didn't actually save them (but they aren't in temp either). Anyway, the newly saved csv files from v205 do work perfectly.
    (hleofxquotes v199 did fetch prices as GBp and FT prices as GBX, so it seems that is what I'd had trouble with). No bother now with either Yahoo or FT csv using v205 which both fetch GBX, and as above, all updates are now in the FT tab so no more mucking around loading 2 files.

    Thanks again to both of you for your work in creating the loader (Mike), and in helping make hleofxquotes work with MD, and in troubleshooting (Derek).

    Jim

  28. 118 Posted by JeremyB on 24 Apr, 2018 12:44 PM

    JeremyB's Avatar

    Hello,

    I have been using hleOfxQuotes to import security prices for several months and following a bit of trial and error early on (related to getting the ticker codes correct and matching them in MD), it now works flawlessly, downloading from FT and importing to MD using the QIF import method. So, a big thank you to everyone involved in making it work so well.

    I recently decided to try importing using the ‘Security Price Loader’, as it allows for currency imports. I downloaded and installed Build 2022 of the extension, but I have encountered a major problem when using the ‘Choose File’ interface on the loader screen. I am a Mac user running the latest version of Mac OS (10.13.4, High Sierra).

    The default location that the loader goes to when I click on ‘Choose File’ is:

    Macintosh_HD/Users/username/Library/Containers/com.infinitekind.MoneydanceOSX/Data

    If I try to navigate to a different folder, (‘username’ being the obvious choice since it contains my personal files/folders), no files or folders of any kind are displayed. Changing the ‘File Format’ option to All Files did not help either.

    I also tried typing the full path to the desired file location into the filename box, but the loader did not recognise it.

    When I save a QIF file from hleOfxQuotes, I do so onto an SD card plugged into my MacBook, but there is no way to see that drive either, as the ‘Choose File’ interface only allows me to navigate up to the root of the hard disk and doesn’t show other drives.

    The Library folder is hidden in MacOS and is intended to contain preference files, application caches, and application support data, not user created files. It's like the hidden directory Appdata in Windows.

    A workaround is to save the csv file from hleOfxQuotes into the folder quoted above, or copy it there after saving, but that is not helpful, as that folder is not normally visible.

    I will continue to use the QIF import method which works fine, but can anyone help me with this problem, as I would like to be able use the loader? The file finder interface does not work for me, as it is only able to find a csv file that is in the default directory quoted above.

    Jeremy

    P.S. I might be posting this to the wrong thread as it relates more to the Security Price Loader extension than to hleOfxQuotes. Sorry if that's the case.

  29. 119 Posted by Mike Bray on 24 Apr, 2018 01:57 PM

    Mike Bray 's Avatar

    Jeremy

    I use the standard Java interface for file handling. On Windows the
    left hand of the screen has a number of different locations to look at,
    i.e:

    Recent Items
    Desktop
    Documents
    This PC
    Network

    You can click on any of these and you see the normal file explorer.

    Now, the Mac might be different. I can not test this as I do not use
    Macs. Can I suggest you click on 'My PC' or equivalent and navigate down
    to the file required. If you are using an SD card this will show under
    'My PC'.

    Regards
    Mike

  30. 120 Posted by derekkent23 on 24 Apr, 2018 02:11 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi JeremyB

    Unfortunately, Like Mike my computer runs on Windows 10.

    The ‘Security Price Loader’, ‘Choose File’ allows me to navigate all my folders down to username where I can see and enter Dropbox and then down to the root of C where I can see an attached memory stick. Access all areas. No help to you.

    IF THERE IS A MAC USER READING THIS CAN THEY HELP?

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