Security Price/History Loader

Mike Bray 's Avatar

Mike Bray

13 May, 2018 11:08 AM

The two extensions above were originally written to help me load prices not available via the Quotes extension, i.e mostly UK prices.

Since the closing down of the Yahoo service my extensions have been used (along with hleofxquotes) as a replacement and I have made a number of changes to help accommodate different users.

Both extensions will load the following:

Securities assigned to an Investment Account for which there are registered holdings.
Securities assigned to an Investment Account for which there are no registered holdings.
Securities that are not assigned to an Investment Account but have the 'Show on Summary Page' flag ticked on the Security table.
Currency Exchange Rates for the Base Currency which have the 'Show on Summary Page' flag ticked on the Currency table.

No other prices can be loaded by these extensions.

Please read the extensions part of my Wiki at https://bitbucket.org/mikerb/moneydance-2015/wiki/Extensions for information about file structures and using the extensions.

Regards
Mike Bray

  1. 1 Posted by rfh on 08 Aug, 2018 12:44 AM

    rfh's Avatar

    Hi Mike,
    First thank you much for making these extensions available, the alpha vantage solution has just never worked for me, multiple missing and wrong quotes each time I use it. Both of these tools you provided have worked well for me, I'm currently using the 2038 version of both of them. I can use the history loader along with the other tool to obtain quotes but have recently switched to using the price loader. I have very little experience using other investment platforms as I exclusive use Fidelity for all my investments. Fidelity provides the ability to download a CSV file for all your accounts and securities that almost works with price loader. I would naturally like to get it to work without my having to do anything beyond running price loader. The problem is price loader encounters a dollar sign $ that precedes the price which the price loader balks at. I have been getting around this by using sed (sed s/\\$//g <in >out) to remove all the $ in the downloaded CSV file which makes price loader "happy" and allows me to import the quotes. I can't imagine it would be much of a change to ignore the $ symbol, or to generalize it for various currency symbols maybe a bit more work. So I was hoping to request a modification so I could eliminate the sed step and just run the price loader on the downloaded Fidelity csv file.

    Thanks, Ron

  2. 2 Posted by Mike Bray on 08 Aug, 2018 06:17 AM

    Mike Bray 's Avatar

    Hi Ron

    Thanks for the vote of confidence. I can do that, though it is worth
    looking at a more general solution. Is your base currency USD and you
    just want to remove the $? Or do you have quotes in different
    currencies? What I can do is remove any prefix/suffix for the base
    currency.

    Regards
    Mike

  3. 3 Posted by bosie88 on 09 Aug, 2018 04:20 PM

    bosie88's Avatar

    Hi Mike -
    Also wanted to thank you for your excellent work on these extensions. They have been the only reason why I have stayed with Moneydance since all these problems have arisen with Alphavantage. I do have a question about the Security Price Loader extension: Is there a reason why there is no option to load a DATE field? I often download prices from Yahoo Finance during the trading day. My mutual funds have the previous day's closing price but ETF's and stocks have today's current price . RIght now I'm loading them in two batches and changing the date manually at the top. Is there a way around this so that the appropriate date in the .csv file could be loaded? I tried using the Security Price History extension which does include the DATE field, but it will not load today's date and defaults to yesterday for stocks and ETFs. Just wondering if there is a way around this.

    Thanks again.

    -AJ

  4. 4 Posted by Mike Bray on 10 Aug, 2018 04:44 AM

    Mike Bray 's Avatar

    Hi AJ

    Let me give it some thought. I need to think about the rules. The
    history loader obviously uses a date. I will look at the code on this.
    What it does do is that if the date is newer than the last historical
    date it will update the current price.

    Mike

  5. 5 Posted by Mike Bray on 10 Aug, 2018 06:15 AM

    Mike Bray 's Avatar

    Hi AJ
    I have just run a file through the History loader with today's date and it updated the current price and created a history record for today. Is this what you want? Try creating a file with a single record in it using today's date and load it through the History loader.

    Mike

  6. 6 Posted by bosie88 on 10 Aug, 2018 05:05 PM

    bosie88's Avatar

    Hi Mike -
    Thanks for your response. I did a little experimenting. The Security Price Loader works just as I described in my previous post. But there was some odd behavior with the Security History loader. When I created a file with a single record (actually I used two - 1 stock and 1 ETF) it loaded exactly as you stated it would with the correct dates. However, when I loaded the entire .csv file with 22 entries, the history loader recorded the date as 02/08/2177 when it should have been 08/09/2018 and 02/08/2178 when it should have been 08/10/2018. I'm attaching screen captures of the original .csv and the security history loader screen in both instances. Do you know why this may be happening?

  7. 7 Posted by Mike Bray on 11 Aug, 2018 03:21 PM

    Mike Bray 's Avatar

    Please could you post the csv file. I will experiment with it.

    Thanks
    Mike

  8. 8 Posted by bosie88 on 11 Aug, 2018 04:32 PM

    bosie88's Avatar

    Sure, Mike. Here it is. As I said, I get different results when I load the entire file and when I just load for instance the first and last rows.

    -AJ

  9. 9 Posted by Mike V. on 13 Aug, 2018 01:38 AM

    Mike V.'s Avatar

    Mike,

    Great work -- thank you. Like Ron, I have been experimenting with downloading my positions from Fidelity and then removing the USD dollar signs. The data I have looks like this:

    "Account Name/Number","Symbol","Description","Quantity","Last Price","Last Price Change","Current Value","Today's Gain/Loss Dollar","Today's Gain/Loss Percent","Total Gain/Loss Dollar","Total Gain/Loss Percent","Cost Basis Per Share","Cost Basis Total","Type" "36753xxxxx","SPAXX**","FIDELITY GOVERNMENT MONEY MARKET",8430.720,$1.00,$0.00,$8430.72,n/a,n/a,n/a,n/a,n/a,n/a,"Cash"

    The Last Price contains the $ sign. Removing the $ sign would be useful for me.

    Thanks

    MikeV

  10. 10 Posted by Mike Bray on 13 Aug, 2018 03:41 AM

    Mike Bray 's Avatar

    Please download the extension again, build 2039 removes the prefix and
    suffix for the base currency. If you have USD as your base currency it
    will accept prices with a $. If GBP it will accept £.

    Mike

  11. 11 Posted by rfh on 13 Aug, 2018 06:25 AM

    rfh's Avatar

    Hi Mike,

    Thanks for the update to 2039, I gave it a shot but it didn't seem to work for me. After loading the file and selecting the "select all" box at the bottom of the window nothing in the window gets a check mark but when I preprocess the file with my sed command it then likes the file. Am I missing something?

    Ron

  12. 12 Posted by Mike Bray on 13 Aug, 2018 07:37 AM

    Mike Bray 's Avatar

    Hi Ron

    Is your base currency USD and the prices contain a $? Are there any
    spaces in the price?

    Regards
    Mike

  13. 13 Posted by rfh on 13 Aug, 2018 07:51 AM

    rfh's Avatar

    Hi Mike,

    Yes my base currency is USD and yes the prices do contain a $ but there are no spaces. Attached is a CSV file downloaded directly from fidelity as an example.

    Thanks
    Ron

  14. 14 Posted by Mike Bray on 13 Aug, 2018 07:56 AM

    Mike Bray 's Avatar

    Ron, Can't see the file
    Mike

  15. 15 Posted by rfh on 13 Aug, 2018 07:59 AM

    rfh's Avatar

    That’s because I’m an idiot and forgot to attach it. Sorry!

    Ron

  16. 16 Posted by Mike Bray on 13 Aug, 2018 08:18 AM

    Mike Bray 's Avatar

    Ron

    The issue is the " " around each text field. The symbols don't match. I will have a look overcoming this.
    Mike

  17. 17 Posted by Mike Bray on 13 Aug, 2018 09:23 AM

    Mike Bray 's Avatar

    Please download new build (build 2040) and retry.

    Mike

  18. 18 Posted by rfh on 13 Aug, 2018 09:31 AM

    rfh's Avatar

    Hi Mike,

    A quick test indicates PERFECTION, I’m heading out to work in a few minutes but tonight I’ll give it a little more of a work out. All indications are this work just fine, thank you so much!!!

    Ron

  19. 19 Posted by Mike Bray on 13 Aug, 2018 11:49 AM

    Mike Bray 's Avatar

    bosie88 I sent a reply to your message but it has not got through.

    I ran your csv file and it worked correctly.
    Please could you run it again but this time before you load the data turn debugging on. Click on Help (top right), select Turn debug on/off, select Detailed. Load the data. Then post the error log on here. You will find it in c:\Users{userid}.moneydance\errlog.txt.

    Thanks
    Mike

  20. 20 Posted by bosie88 on 13 Aug, 2018 04:15 PM

    bosie88's Avatar

    Mike - I'm attaching the error log. I loaded the file, the dates loaded incorrectly as before, but I didn't save the values as they are incorrect and I would have to manually delete them from all the securities. If you need me to save the values in order to troubleshoot this, let me know.

    -bosie88

  21. 21 Posted by Mike V. on 13 Aug, 2018 06:37 PM

    Mike V.'s Avatar

    Mike,

    Build 2040 works like magic! Thank you very much. I tested it against two brokerages and ran like a champ.

    FYI, I have a couple of money market securities that Fidelity lists as "SPAXX*", -- don't know why Fidelity adds the two *. Since these are money market funds the price does not change. I was going to try to limit the compare to 5 digits, but the minimum is 6.

    Anyway, you are da' man! Wow!

    I am going to try Load Transactions on the Fidelity account next.

    Thanks again.

    MikeV

  22. 22 Posted by Mike Bray on 14 Aug, 2018 01:35 PM

    Mike Bray 's Avatar

    Thanks bosie88

    I will investigate further
    Mike

  23. 23 Posted by Mike Bray on 16 Aug, 2018 03:47 PM

    Mike Bray 's Avatar

    Hi bosie88

    Can I ask what is your date preference set to in MD. I can create issues when the date in the csv file is in a different format than the date preference. See the 2 attached files. Both came from your csv file, one date mmddyyy.jpg was with the date preference set to MM/DD/YYYY and the other datemm.dd.yyyy,jpg is with the date preference set to MM.DD.YYYY.

    Mike

  24. 24 Posted by bosie88 on 16 Aug, 2018 07:25 PM

    bosie88's Avatar

    Hi MIke -
    Thanks, you figured it out! It is a date format issue, but with Yahoo Finance, not Moneydance. My Moneydance date format is set to MM/DD/YYYY. However, when I opened the downloaded .csv file into a text editor, I saw that the dates are formatted as YYYY/MM/DD. Hence the odd looking dates in the Security Price History extension. Interestingly enough, if I open up the .csv file in Excel, make an edit, and resave (selecting the option to save it in the same format), when I reopen it in the text editor, the dates have been changed to the correct MM/DD/YYYY format. That explains why the Security History extension loaded the dates correctly after I deleted all but two of the entries.
    I don't see any way to make Yahoo export in a different format, and I don't want to change Moneydance's format to YYYY/MM/DD, so I guess the best solution is to just make sure to open the .csv file in Excel and then immediately resave it as a .csv in order to make the dates compatible with Moneydance.
    Thanks again for your help. I would have never figured this out on my own...

    -AJ

  25. 25 Posted by lddavis on 16 Aug, 2018 07:49 PM

    lddavis's Avatar

    I'm so glad I found this discussion. I see I'm not the only one who has been ripping out their hair trying to update security prices. I have printed out the PDF attached above and will now attempt to understand (and implement) it. It looks intimidating. Like others, I tried contacting Infinite Kind for assistance and never received an answer. Well, I'm off to exercise my brain. Wish me luck.

  26. 26 Posted by Mike Bray on 17 Aug, 2018 04:52 AM

    Mike Bray 's Avatar

    Hi AJ

    Glad I could help, I was pulling my hair out on this one. You could set the date format to yyyy/mm/dd, upload your file and set it back, though doing the change outside MD sounds safer.

    Mike

  27. 27 Posted by lddavis on 17 Aug, 2018 04:52 AM

    lddavis's Avatar

    I apologize for this automatic reply to your email.

    To control spam, I now allow incoming messages only from senders I
    have approved beforehand.

    If you would like to be added to my list of approved senders, please
    fill out the short request form (see link below). Once I approve you,
    I will receive your original message in my inbox. You do not need to
    resend your message. I apologize for this one-time inconvenience.

    Click the link below to fill out the request:

    https://webmail.pas.earthlink.net/wam/addme?a=[email blocked]&id=11e8-a1d9-4e85cd1c-9a2b-00212800f034

  28. 28 Posted by Mike Bray on 17 Aug, 2018 05:14 AM

    Mike Bray 's Avatar

    MikeV

    I have uploaded build 2041 which allows you to restrict the number of characters to match to 5.

    Mike

  29. 29 Posted by Mike V. on 17 Aug, 2018 04:55 PM

    Mike V.'s Avatar

    Mike B.,

    Thank you. I'll go give it a whirl.

    ***** Awesome -- thank you for your efforts, I appreciate your hard work!

Reply to this discussion

Internal reply

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

Attaching KB article:

»

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