Security History has gone haywire for current price - increases 1/3 each time
Every time I open a stock price history, and close it again without changing anything at all, the Current price goes up by a third.
I'd been importing prices using the hleofxquotes system, and trying to understand why my values make no sense. I've closed the program and reopened it, and it does exactly the same each time I open the history of any stock.
The attached shows screenshots of before and after one such close/reopen.
What on earth is going on?
-
2018-02-10.png
47.4 KB
-
.png)
2018-02-10_(1).png
44.4 KB
2018-02-10.png
.png)
2018-02-10_(1).png
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
1 Posted by derekkent23 on 11 Feb, 2018 09:41 AM
I am not support staff, just a user.
That’s very strange. As it is happening by just opening and closing the Price History window it is unlikely to be anything to do with data imported from data exported from the hleofxquotes program. As an aside which file type CSV or QIF did you import.
You don’t state which version or build of Moneydance you are running. See under HELP – about Moneydance. For example, I am running 2017.7(1665)
I would do an EXPORT – BACKUP then update to the latest preview build of 2017 at https://infinitekind.com/preview
It looks like your dated prices are correct? It’s the Current Price that way out. Manually correct them and close and reopen the window to set if the problem has been fixed. Let us know how you get on.
Hope this helps
2 Posted by JimRB on 11 Feb, 2018 12:58 PM
Problem arose using 2017.6 (1656) which says it is up to date.
Windows 10 Version 1709 (OS Build 16299.192)
64 bit versions
CSV files were imported from hleofxquotes, using the Security Price Loader and following the instructions in your pdf about the latest NEW BUILD RELEASE “Build_20180201_190” and using that version of the java app.
This is my first import using that app and loader. Up to now I had been playing around trying to work out how to import all of the stocks and funds correctly using only one source. Finally settled on Yahoo.
The dated prices are fine. If I manually correct the prices, as soon as I reopen the history window, it shows double what I input. The way I noticed was looking at the Summary page, and seeing the prices down the right hand side under the "Stock Prices" section. I was checking what had been imported using hleofxquotes. Some of them looked odd. I clicked on the stock, which opens the history window. Closing it added a third to the price!
I have now updated to 2017.7(1665).
I typed in the price of 15.12368002 to the current price field, and closed the history window. The entry under "Stock Prices" is now 20.91, which (on opening the history) is what now shows in the Current Price. Closing the History without changing anything makes the price 28.91.
OOH, I think I have found something!
Under exchange rates, I have a rate for GBP of 1.3826! I am in the UK and my accounts are all in GBP!
In fact, the exchange rates all look completely wrong. It would be great to have 150 Euro to the pound!
I have realised that in copying and pasting codes between FT and Yahoo in the hleofxquotes app, I have all rates set to import both ways. Is this causing a problem?
(I have checked and the rates are exact reciprocals to 5 decimal places. NONE of the rates is that 1.3826.) I cannot see how to view the history of currencies, or how to correct them.
Tools > currencies does not allow me to delete a currency.
I've done an import from Alphavantage of currencies ONLY, and that has corrected the Euro and others, but leaves GBP as an exchange rate.
Time for lunch. Perhaps food will help me understand a little more.
3 Posted by derekkent23 on 11 Feb, 2018 03:24 PM
I am not support staff, just a user.
The first thing I notice is your screen shot of exchange rates on the summary page. If your base currency is pounds you should not see British Pounds 1.3826. With a base currency of GBP it must always be 1, but it is not displayed.
The only time an exchange rate for British Pounds should be displayed is if another currency is the base currency. Did you change the base currency at any time? If you change the base currency Moneydance looks like it has frozen, recalculation takes some time. If you force closed the program this could have unexpected results. You may need to restore a backup.
For information.
If you open the CSV file in a text editor, I Notepad++ but notepad will do you will see a header.
Price,Ticker_ISIN
Followed by a list.
In this list, amongst the security prices, will be say the conversion for USD and GBP there will be two lines
“1.4”,”GBPUSD=X”
“0.7142...”,USDGBP=X”
This format will be the same regardless which tab was used Y or F. Also, in the symbol window you only need GBPUSD or USDGBP the other is calculated. Same for Y tab using GBPUSD=X or USDGBP=X
When the Price Loader extension is used only the “1.4”,”GBPUSD” line is used to obtain the value to be imported. The line with the base currency first. In this example 1.4. when imported this will appear as 0.7142... in the TOOLS – CURRENCY – US DOLLAR window.
Let me know if I can provide any more information.
4 Posted by JimRB on 11 Feb, 2018 06:13 PM
Thanks Derek
I have never had anything bar GBP as the base currency.
So far as I am aware, I've never forced MD closed.
Thankfully I have it set to backup after every closure, as well as having one off backups.
TLDR version:
Don't use currency in both directions in the Symbols list in hleofxquotes - you will create an exchange rate for your base currency with itself, and it is not error-checked by the Price Loader.
FULL VERSION (In case you want to understand this in more depth)
By restoring an earlier file and reimporting the same files as I did before, I can identify the errant import to a particular CSV file, an export from Yahoo that has FOUR lines for USD, similar to the way you show but not the same.
"1.3826","GBPUSD=X=X"
"0.7232","USDGBP=X=X"
"1.3826","GBPUSD=X"
"0.7232","USDGBP=X"
Following the import of that file, the Exchange Rate for GBP exists. SO one of those lines is causing the problem.
After that import, When I use the Price Loader extension, it shows a line that it cannot update for ticker GBP CurBritishPound that is set at 1.3826!
So, I created a new csv file in the same format and made it say "1.0000"," GBPGBP=X", which I loaded into MoneyDance.
GBP then disappeared from my list of Exchange Rates. Opening and closing a price history does not amend the current price. Thank goodness. HOWEVER, even without looking at any price history, the list of Stock Prices are all wrong by that factor of 1.3826. So that is not a solution, just a work-around.
In following this investigation, I re-saved that CSV file without these lines (which appeared in this order)
"1.7685","GBPAUD=X"
"1.7385","GBPCAD=X"
"1.2990","GBPCHF=X"
"8.7058","GBPCNY=X"
"1.1280","GBPEUR=X"
"150.4003","GBPJPY=X"
"1.3826","GBPUSD=X"
...and when I use that revised file to import into a freshly restored earlier version of my accounts, there is no Exchange Rate created for GBP.
So, it would seem that my problem was that in creating the Symbols input for hleofxquotes, I had inadvertently used the currencies both ways round (e.g. my Symbols list had BOTH GBPUSD=X" and also "USDGBP=X"), and that was what created the problem. This USD rate was picked up as it was the last in the CSV file.
Repeating the exercise, but starting from a fresh Symbols list that only has each currency expressed in one way (either, it does not matter as long as it isn't both!), the CSV only has two lines per currency, and importing it does not create an erroneous entry for a GBP exchange rate to itself.
I guess that there is an issue to solve for the error checking in either hleofxquotes or the Price Loader.
The two CSV files attached are the output for exchange rates only for the errant Symbols and for the correct ones only, in case it helps.
Jim
5 Posted by derekkent23 on 11 Feb, 2018 06:49 PM
I am not support staff, just a user.
Looks like you learn something new every day.
Can you post a text file created by right click on the exchange rate symbols you used in the security window then SAVE AS. Do this for the good set and the bad set. I will then take up the issue with the developer. Thanks.
6 Posted by JimRB on 11 Feb, 2018 07:05 PM
That would be the two CSV files that I uploaded in my previous post.
The screenshot of the Symbols used was in post 2 which created the duplicated exchange rates CSV.
The screenshot of the correct symbols is attached to this which created the PROPER exchange rates CSV.
7 Posted by derekkent23 on 11 Feb, 2018 07:20 PM
I am not support staff, just a user.
I was trying to understand how the program outputted the =X=X part for each exchange rate in the CSV file?
It would help to see the formats you used for GBP/USD exchange rates in the symbol window.
Just enter the symbols you used in the symbol window and right click to save a text file. If you could do this for just the one exchange rate. One text file showing what worked and one that gave problems.
Thanks
8 Posted by JimRB on 11 Feb, 2018 07:26 PM
Do you mean this?
9 Posted by JimRB on 11 Feb, 2018 07:29 PM
Single currency written each way in Symbol list,
USDGBP=X
GBPUSD=X
with output as CSV file
Price,Ticker_ISIN
"1.3826","GBPUSD=X=X"
"0.7232","USDGBP=X=X"
"0.7232","USDGBP=X"
"1.3826","GBPUSD=X"
10 Posted by derekkent23 on 11 Feb, 2018 08:01 PM
I am not support staff, just a user.
I understand. I will start by putting a warning in the PDF to the effect that only one exchange rate attribute should be used. For example, for the F tab GBPUSD or USDGBP but not both. Same for the Y tab GBPUSD=X or USDGBP=X but not both.
Thanks for your import on this. Now this issue is understood I hope you find the program of value.
11 Posted by JimRB on 11 Feb, 2018 08:22 PM
Thanks Derek
It is useful, just has teething troubles
Jim
12 Posted by derekkent23 on 14 Feb, 2018 11:13 PM
I am not support staff, just a user.
Hi, JimRB
Hopefully you seem the update to the program in the other thread to add error trapping for the exchange rate problem you encountered, along with the addition of some other features.
The developer is also looking at way to run, then merge, from the two sources into a common export file. Not sure how long it will take as some of the issues are quite complex.
13 Posted by JimRB on 18 Mar, 2018 12:57 PM
Just a short post to say THANKS to @derekkent23 and to the developer, the error trapping fix has worked well.
14 Posted by derekkent23 on 18 Mar, 2018 01:09 PM
I am pleased to read you are still using the program and hopefully finding it of use.
Latest build 199, more to follow!
System closed this discussion on 17 Jun, 2018 01:10 PM.