Third-party Quote and Exchange Rate Program from hleofxquotes.
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 8 out of 10. View the first page
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
211 Posted by derekkent23 on 06 Jun, 2018 07:42 PM
I am not support staff just a user.
Hi Mike.
I did some testing with the CSV file Tony provided.
It seems that with the Process Currency box ticked the Security Price Loader does not like the security symbol “T”. Change it to “TE” in Moneydance and in the CSV file and it all works. I tried “W” instead of “T” and that did not work. It looks like the Security Price Loader does not like single character symbols when the Process Currency box is ticked.
Also tested the Security History Loader.
The second window of the Security History Loader opens but the single character security symbol is not listed.
212 Posted by Mike Bray (Quot... on 06 Jun, 2018 08:01 PM
Hi Derek
Having looked at Tony’s file i was thinking along the same lines. I will fix it tomorrow.
Regards
Mike
Sent from my iPad
213 Posted by Mike Bray (Quot... on 07 Jun, 2018 05:56 AM
Tony
Please download the latest build of the extension (2029). I have corrected the problem. As Derek said it was the single character ticker.
Mike
214 Posted by derekkent23 on 07 Jun, 2018 07:55 AM
I am not support staff just a user.
Hi Mike.
Both Security Price and History Loader version 2029 now work with single character ticker symbols.
Good work!
215 Posted by Tony Blunt on 07 Jun, 2018 12:41 PM
Thank you, works perfectly.
216 Posted by nigelf on 13 Jun, 2018 08:31 AM
Hi Derek/Mike
I have recently made the switch to using your program to update security prices and find it much more useful for funds - thanks and well done!
One thing I can't fathom is as follows: I have a variety funds in two currencies (USD and GBP) and load the security prices from the Converted Price table preserving those local currencies (my Converted Prices are GBX/GBP go to GBP and USD remain USD). When using Security Price Loader to import into Moneydance, I get the correct Current Price for valuation but it also creates a new line entry in the Price History table with the same date but with any USD price converted to its GBP equivalent. I then have to overwrite these manually as wish to keep a USD time series.
Can I stop this behaviour as I suspect it is something to do with how the Price Loader creates the new entry rather than Moneydance behaviour? It appears Current Price is in the specified currency of the security but historical price is converted to GBP - my preference is simply that both Current Price and Historical Price are denominated in the currency of that security.
Thanks in advance
217 Posted by Mike Bray (Quot... on 13 Jun, 2018 10:29 AM
Hi Nigel
I will have a look. By default MD stores all prices in the
base currency so the price loader does the conversion.
Regards
Mike
Sent from my iPhone
218 Posted by nigelf on 13 Jun, 2018 10:59 AM
Hi Mike
I have kept the time series for years and always have it showing in the security currency. I suspect there is an element of MD storing in base currency (eg GBP) as there is a slight conversion/discrepancy sometimes in the past probably caused by FX rate.
However what is happening now is as follows:
1 Refresh prices in your app - USD securities are set with the \USD\USD extension so that the table of Converted prices is either GBP or USD depending on currency of security
2 I export all lines to the CSV file
3 I load the CSV file in Security Price Loader
4 Select all and save to MD
5 Looking at the Price History for a security, MD has updated the Current Price equal to the import (which is either GBP or USD) depending on the security. It also adds a new line to the Price History table below - if the imported price is USD-denominated, it inputs a number which is the GBP equivalent. Before using your extension, I would simply manually update Current Price, select New and the new line was USD - the only GBP conversion was applied to the Base Currency valuation of that security and all the Price Info (current and historic) is in USD (or the denomination of that security)
I suspect there must be some element of having to take the 'GBP Valuation Price x FX Rate of security denomination’ to create the time series line. That then means it is always multiplied by 1 if the security is GBP (base currency) and the error would disappear.
Hope those thoughts help - thanks for the speedy reply
Kind regards, Nigel
219 Posted by derekkent23 on 13 Jun, 2018 12:17 PM
Hi Mike
Just ran a test based on nigelf posts.
Base currency GBP
Security Apply Inc. This Security is set to have a currency of US Dollar under TOOL – SECURITIES – APPLY INC.
In hleqfxquotes program symbol used is AAPL/USD/USD so that the price exported to the CSV file is in USD. In test $192.28
When the Security Price loader is used the new price in the loader window is correctly listed as 192.28 as is the Last Price.
On import, in the security price history window the Current Price is correctly listed as 192.28
However, the Dated Price is incorrect, having been converted into Pounds. See screen shot. Also note the High/Low values are way out.
Tested the Security History Loader with a value for Apply of 193.98 in the CSV file. The Current Price was correct but the Dated Price had a Small error at 195.02720347. See screen shot.
By the way nigelf, hleqfxquotes and Mike get the credit for the programs, I just produced the How To.
220 Posted by Mike Bray (Quot... on 13 Jun, 2018 02:58 PM
Thanks Derek
Looking at the screen shot from the history loader the date is 3/6/18.
Was this the date used in the file?
Regards
Mike
221 Posted by derekkent23 on 13 Jun, 2018 03:10 PM
Hi Mike
When using the Security Price loader I set the date in your loader to the 03/06/2018 in case I needed to do further imports using a later date. As it turned out I did not need to.
When using the Security History loader the date in the CSV file was 12/06/2018 as shown in the screen shot.
222 Posted by derekkent23 on 13 Jun, 2018 03:49 PM
Mike just had a thought regarding the difference in the value of the Current Price and the Dated Price in the security history window when using the Security History Loader, see screen shot in post 219. In the CSV file the security is dated the 12/06/2018 and the exchange rate is dated 13/06/2018 (rate 0.74979381). If I look in TOOL – CURRENCY – US Dollar and take the exchange rate equal to the security date (12/06/2018) or in my case before that date (08/06/2018 rate 0.74576777) and calculate a ratio from the two exchange rates (.74979381/.74576777) and multiply it by the Current Price (193.98) it gives me the value in the Dated Price list for the 12/06/2018 (195.02720347) which could be considered as being correct?
223 Posted by Mike Bray (Quot... on 13 Jun, 2018 04:39 PM
Hi Derek I too am looking at exchange rates. I have run a test file through (AAPL(1).csv) and it gave the result (After History load.png). This looks correct.
I then went into the file and looked at the actual data and it too looks correct. Have a look at (data file plus raw rates.png), it shows the calculations that are done and the currency rates for USD/GBP from MD. Which too looks correct.
I suggest that the USD/GBP currency rate is incorrect.
By the way of you have not done so already you might want to download my FileDisplay extension. It allows you to interrogate the MD file.
I will investigate further and post what the program does.
Regards
Mike
224 Posted by derekkent23 on 13 Jun, 2018 05:26 PM
Hi Mike
Sorry you lost me with
"I suggest that the USD/GBP currency rate is incorrect."
Did my post 222 make sense?
225 Posted by Mike Bray (Quot... on 13 Jun, 2018 05:56 PM
Hi Derek
If we have a base currency of GBP and a security currency of USD then MD will use the USD rate
to calculate the value to be stored in the MD file. All values are held in the base currency as
you can see if you use the File Display extension.
Both price and history load use the currency rate to calculate the value to be placed in the file.
My test worked, hence i think the currency rate is incorrect.
I am happy to talk on the phone if you wish, email me on [email blocked].
Regards
Mike
Sent from my iPad
226 Posted by derekkent23 on 13 Jun, 2018 09:05 PM
Hi nigelf
When I first ran a test using the Security Price Loader I got the same result as you. post 129.
My base currency was GBP I set up a security Apply in US Dollars and used AAPL/USD/USD in hleqfxquotes program.
The New Price in the Security Price Loader window was correct. I set the "As of date for new prices" to 03/06/2018.
In the Security History window, the Current Price was correct, but the Dated Price had been converted into Pounds using the exchange rate in the CSV file.
However, when I repeated the test the dated Price was correct, it had not been converted. I have repeated the test a number of times and the results are so far always correct.
Can I ask you to:
Post the build of moneydance you are running. E.g. 2017.8(1682)
Post the build of the Security Price Loader you are running.
Tomorrow run the hleqfxquotes program again and post the CSV file.
Run the Security Price Loader and take a screen shot of the list of prices in the second screen. Then select and save selected values.
Post a screen shot of US Dollar currency window under TOOLS – CURRENCIES.
Post a screen shot of the Security History window for one of your USD securities.
Hopefully this may give us a clue to what’s going on.
Thank for your help.
227 Posted by nigelf on 14 Jun, 2018 08:34 AM
Hi Derek
Thanks to both of you for persevering. I was out the latter part of yesterday so couldn’t joint in the discussion.
I have done as you request - screenshots attached in time sequence showing ‘before’, 'iteration 1’ & ‘iteration 2’ - and have the following observations (constant with your comment that it only happens once!):
1 The problem occurs on the initial ‘Save’ in Security Price Loader. When you simply repeat again, the behaviour disappears and the correct USD Dated Price is there. There is some small discrepancy with the Current USD Price but that looks like rounding rather than anything else.
2 By my reckoning everything is working fine up to the point of ‘Save’ on iteration 1. The security ‘Ashmore’ shows Last price $96.16 and New Price $95.82 on the list. When you save into MD, the Current Price = the correct USD Price (95.82), the GBP Valuation is correct = USD Current Price x Today FX (95.82 x 0.74649149). But the Dated Price is wrong as it is Current Price x Yesterday FX (95.82 x 0.74917591) and the High/Low is even more wrong as that is Dated Price x Today FX (=71.78604432 x 0.74649149)
3 On the second iteration, the Last Price shows as $71.7861 from the incorrect inputs above. Then save produces an update where Current Price correct, Valuation Price correct (as above) and Dated Price correct subject to some rounding. High/Low are also not converted (or are possibly converted using FX = 1).
4 The only difference I can see in the runs is dates in Security Price Loader list - Last Update = 13/6, Price Date = 14/6 in iteration 1 whereas both dates = 14/6 in iteration 2. That may help track down the line in the code where the selection of the ‘wrong’ price data is happening?
I hope that helps. At least in the short run, a simple fix appears to be Load twice and Save twice!
VERSIONS ARE: Moneydance = v 2017.7.1 (1671), SPL = Build 2029, hleqfxquotes = 20180512_207
Kind regards, Nigel
228 Posted by derekkent23 on 14 Jun, 2018 09:42 AM
Hi nigelf
I have been able to repeat the problem you are seeing.
Took some time to establish it only happened on the first iteration.
I have created a completely new data set to illustrate the issue and have sent a Moneydance backup to Mike for him to look at.
Thanks for your hard work in documenting the issue, with that and my Backup Mike should be able to fix the issue.
Thanks
229 Posted by Mike Bray (Quot... on 14 Jun, 2018 11:54 AM
Hi Nigelf, Derek
I have reproduced the issue and will investigate.
Regards
Mike
230 Posted by Mike Bray (Quot... on 14 Jun, 2018 01:54 PM
I think I have got to the bottom of this. In the test example Derek sent me there were two USD dollar rates, one for 13/6 and one for 14/6, they were different. When the historical rate was uploaded it used the dollar rate for 14/6 rather than 13/6. When you display it in MD it uses thew dollar rate for 13/6.
Nigelf, when you uploaded the historical data it was before the currency and therefore the USD rate was 1.0. This is why the second time it worked as you had uploaded the USD rate by then.
Mike
231 Posted by Mike Bray (Quot... on 14 Jun, 2018 02:45 PM
Build 2030 uploaded. This should correct the issue. Please make sure you have valid currency rates for the dates of historical data. It will default to 1.0.
232 Posted by nigelf on 14 Jun, 2018 02:57 PM
Mike
Thanks - I will test out on tomorrow’s run.
I was responding to Derek when your message came in. My reply to his earlier post above follows:
I agree there are two USD rates for 13/6 (last update) and 14/6 (current update). However the behaviour on the historical table is still strange on the 1st iteration for the USD security as:
Dated Price shown on 14/6 = New Current USD Price x FX Rate of 14/6 - it should be New Current USD Price x 1.0000 (ie USD/USD FX Rate)
High & Low Prices on 14/6 = Dated Price of 14/6 above x FX Rate of 13/6 - it should be same as Dated Price of 14/6
If the Security currency is set as USD that FX Rate should ALWAYS be 1.00 as essentially you are always looking at the USD/USD Fx Rate for price and time-series purpose.
It is really no different to if the Security is denominated in GBP as the FX Rate always used there is 1.00 (given its the GBP/GBP rate) for price and time-series purposes.
The only part where GBP/USD FX is relevant is in getting your valuation in Base Currency where the relevant date price is multiplied by the relevant date FX rate for the currency pair for each security (ie USD Price and GBP/USD FX on 14/6 give valuation in GBP on 14/6 & same two inputs for 13/6 do same for 13/6). The only complication should be where there is a discrepancy on the dates of the two inputs and that should probably take the most recent relevant input that is for the valuation date or earlier date (ie for 13/6 assume there was a security price for 13/6 but no FX price, the relevant FX would be from the closest date earlier than 13/6 which could be 12/6 or before)
Interestingly this may also help IK solve what is the problem with Alphavantage and the ever-changing pounds or pence dilemma. I suspect that might somehow relate to a similar issue if FX Rates are being applied in the background and sometimes being GBP1=GBP0.01 (rather than always being GBP1=GBP1) in the same lines of code. I say that because I often noticed that if I did a second iteration, the UK funds causing the problem would very often correct their price input by a factor of 100 (ie pence to pounds).
Thanks to you both, Nigel
233 Posted by nigelf on 14 Jun, 2018 04:04 PM
Mike
I have just run Build 2030 having updated the price on the Ashmore security and USD/GBP Fx Rate. The main behaviour looks much improved and correct in the main but I am still confused where the calculation/creation of the Dated Price/High/Low is coming from - screenshot below.
The Current Price = USD 95.61 (last current price was 95.82 from this morning’s run included in iteration 2 of earlier post screenshots)
The valuation price = GBP 71.806234 which is correct (= USD 95.61 Current x 0.75103267 USD/GBP rate of 14/6). (Last valuation price was 71.528815 from this morning’s run included in iteration 2 of earlier post screenshots)
The dated price = 95.03182401 (last dated price was 95.8199371 on 14/6 from this morning’s run included in iteration 2 of earlier post screenshots). This new dated appears to have been derived by the following calculation:
Dated Price = Current Price x (Last FX Rate/New FX Rate) = 95.61 x 0.74649149 (14/6 rate from this morning) / 0.75103267 (14/6 rate from now below)
This can’t be correct as, in practice, it could only be 95.82 (the previous Current Price dated 14/6) or 95.61 (the new Current Price dated 14/6) or close to those with rounding caused by truncating if it is derived from rounded prices & FX rates. I only give the options as it depends what view you take on what the last piece of 14/6 data is - given we are not working with a minute-by-minute database, the answer is probably 95.61 as the most recent bit of data stored/entered for 14/6.
Once I had worked this out, I then ran v2030 a second time and produced below which I believe is a more correct version - this time when I loaded the CSV file and loaded data, the current line for USD had the same number in both Last Price and New Price. When I the Saved, it clearly factored the Current Price by 1.000 and thus Dated Price equals Current Price (with rounding).
My earlier point is probably still relevant - the factor should always be 1.000 and not (Last FX Price/New FX Price). This is the same for any currency denomination as once you specify the Currency of the Security, that is also the Currency of the time-series.
It’s clearly not a major issue (except in a currency crash!) as the time series is second order to current valuations and the error factor is 0.5% but it looks strange to me. It is also still ‘corrected’ by a second iteration.
Kind regards, Nigel
234 Posted by Mike Bray (Quot... on 15 Jun, 2018 05:06 AM
Hi Nigelf
The issue seems to be one of timing. If you run the currencies and
security prices in the same file the securities are loaded first and
will therefore use yesterday's currency prices. The currency prices are
updated after the securities. Please can you load the file and when you
get to the Load Prices screen select the currencies only and save those
values, then select the securities and save those values.
Regards
Mike
235 Posted by nigelf on 15 Jun, 2018 06:47 AM
Hi Mike
I understand your point though it’s probably just as simple to simply do two full iterations which will produce the same result (as Select All is very easy!).
My point is that if you have a new current price dated today and also create a new entry to the time series with today’s date, yesterday’s FX rate can not be relevant in any way when creating that entry.
However, I am very happy with ‘as is’ because it is a major upgrade to keeping track of prices of a much wider variety of funds/stocks and the time series is of lesser importance.
Thanks to both you for the app and Derek for the guidance.
Kind regards, Nigel
236 Posted by derekkent23 on 26 Jul, 2018 04:26 PM
I am not support staff, just a user.
NEW BUILD RELEASE “Build_20180723_209”
To receive Email updates on this thread, ensure the box at top right of page has been set to SUBSCRIBE.
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, Cryptocurrencies Rates and Market Indices along with your Security Prices. The program is in java, like Moneydance and thus runs on Windows, Mac and Linux computers.
UPDATES
1. Correct some issues related to obtaining and importing Cryptocurrencies.
2. Add the ability to set up Cryptocurrencies as a investment.
Download the attached updated PDF, use the links to download the programs and follow the instructions on how to use.
If you are using Mike Brays extensions you must run build 2038 or later of the Security Price Loader and/or Security History Loader extensions, see PDF for link.
You must also update to at least the latest released build of Moneydance 2017 at
https://infinitekind.com/download-moneydance-personal-finance-software
to ensure compatibility.
Also attached is a second PDF that covers the creation and importing of Cryptocurrencies and Market Indices such as the Dow Jones Industrial Average, FTSE 100 and Canadian S&P/TSX into Moneydance.
See PDF “Hleofxquotes Program used with Moneydance Cryptocurrencies and Market Indices V4”
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!
237 Posted by derekkent23 on 26 Jul, 2018 07:10 PM
I am not support staff, just a user.
Attachment in post 236 “Hleofxquotes Program used with Moneydance Cryptocurrencies and Market Indices V4” was wrong format.
Replacement PDF attached.
238 Posted by Tony Blunt on 26 Jul, 2018 07:33 PM
?? The latest version of Security Price Loader is 2029, not 2038 as in your email?
239 Posted by derekkent23 on 26 Jul, 2018 08:03 PM
I am not support staff, just at user.
Hi Tony, Mike Bray updated both his Security Price and History Loader to build 2038 on the 20/07/2018.
Check using the link in the PDF.
Hope this helps
240 Posted by Tony Blunt on 26 Jul, 2018 10:33 PM
But it was not updated in Moneydance when managing extensions…thanks