Wednesday, February 16, 2011

Code update available for testing (beta)

I have been (very) slowly implementing code updates, and had planned to do it all before putting an update out.  It’s simple, really… “try not to fix what isn’t broke”.  There have been a few repeat issues, including the recent Yahoo server glitch, plus the nice Yahoo screen scraper from Cal.  Since a few of these are important updates themselves, I decided to update the code a bit and push it out.  I’ve uploaded the beta version, and will be happy to hear comments, problems, etc.  I’d like to move this from beta to release pretty quick, so feedback (good or bad) is much appreciated.

Please note:  this is a lot at once. I have tried to test every change, but it has been coded over several weeks (some of it 2 months ago) in my spare time, which means "not much time for focused thought".  I have tested against my own Money files, but I would highly recommend that you install separately (it's own directory) and test using a copy of your Money file.  - Robert

Change Summary:  (There’s probably a few more, but I didn’t write it all down).   

Note: Review the sites.template file for the placement and usage of the flags described below.
 
  1. Look at sites.template for newly added flags.  Any of these can be defined (or not) in your existing sites.dat file.
  2. Write .\xfr\quotes.htm when collecting stock/fund quotes.  Defined the following user options in Sites.dat:
    1. ShowQuoteHTM: Yes/No : Always show quotes.htm following download
    2. AskQuoteHTM: Yes/No : Ask to show quotes.htm following download (overrides ShowQuoteHTM)
    3. Setting both to “No” (default) will never display quotes.htm, but the file will still be written.
    4. Rather than showing the file every time, or asking each time, you can also open the quotes.htm file in your browser once and then copy/paste the link as a shortcut inside Money.  Dan has also indicated that he will use the link in his Money add-in.
  3. Enabled sanity check for <SECLIST> when a <INVPOSLIST> section exists.  Hopefully, this avoids the dreaded Vanguard issue that *bombs* Money.
  4. Scrubbers:
    1. Recoded some routines for less complexity.
    2. Added routine for missing DTEND when a DTSTART exists.
    3. Scrubbers are more verbose, telling you what’s happening.  Connection exceptions are handled more gracefully.
  5. Extended timeout for OFX connections to 30 secs.  This will create delays when a server is offline, but provides a more reliable service for slow (or highly active) servers.
  6. Added Yahoo "screen scraper" code for *funds* provided by Cal Learner.  Edited to fit code structure a little better (hope it still works correctly)  A few notes:
    1. Added EnableYahooScrape: Yes/No option in sites.dat (default = yes).
    2. Grabs quotes for *funds* not supported by the native Yahoo csv interface.  Only executes if the Yahoo CSV-API fails.
    3. Screen scrapers may fail at any time (cross fingers).  There are a couple of sanity checks in place, so hopefully…trash doesn’t get to Money.
    4. The scraper does not currently support “last price” or “change in price”.  This feature is used by the new quotes.htm writer, but won’t affect Money import operations.
  7. Added timeOffset option for site entries in sites.dat.
    1. timeOffset: hrs :   Add hrs to statement DTASOF field(s).  Default = 0 (zero).  Negative hrs will adjust DTASOF time backwards.
  8. Added QuoteCurrency: USD option to sites.dat.  Defines currency for quotes.  Default = USD.
  9. Use ticker symbol for quote name when a null name is received, but only if a quote value other than 0.00 is provided by Yahoo.
  10. The "latest date/time" for ticker quotes is now used for the statement date (DTASOF field).  Until now, ~ "today's date" has been used.  Example, if there are 10 stock/fund tickers retrieved, but the latest date/time stamp for any of those is "15-Feb-2011 @ 12:00", then that time stamp is used for the "statement date" (which is used by Money when setting each quote date/time).
  11. Added slight delay when sending results to Money (between file submits).
  12. Added 10 sec timeout for Yahoo quote downloads.
  13. Added options for Yahoo quotes:
    • m:value:  Currency multiplier option. Quote values will be adjusted (multiplied) by this (optional) value during download.
    • s:value: User defined symbol to send to Money (rather than Yahoo ticker symbol).  
      • ExampleBP.L  m:0.01  s:MyBP
      • Multiply quotes received for BP.L by 0.01 (i.e, convert from pence to pounds), before sending the results to Money.
      • Pass quote to Money with symbol=MyBP (rather than BP.L).  The Yahoo quote will be for ticker = BP.L, but Money will see it as ticker=MyBP.

106 comments:

  1. Robert,

    Just tried out the beta and it worked perfectly!!

    I really like the new quotes.htm file that is created. I'm incorporating it into version 2 of my add-on so that it can be displayed from within Money along with links to your scripts.

    -ameridan (Dan)

    ReplyDelete
  2. Also wanted to point out that everytime the quotes are updated throughout the day, the quotes.htm page is updated, even though the newer values are ignored within the Money program. And the page shows Last Closing Price as well as Percent change in price since the last closing price!

    Great Job Robert!!
    -ameridan (Dan)

    ReplyDelete
  3. Beta works perfectly for me, too. Thanks Robert, I'll sleep better tonight!
    -Fred

    ReplyDelete
  4. Thanks for testing guys. I guess nobody else has had a chance to give it a try?

    ReplyDelete
  5. Robert,

    Hopefully, the quotes.htm doesn't change much because I've tuned my "webpage addon for Money" to include the display of your file. Check it out at: http://microsoftmoneyoffline.wordpress.com/2011/02/17/version2/

    You did a great job!

    ReplyDelete
  6. Robert,

    just tested the quotes and they seem to be working perfect. Thank you so much.

    ReplyDelete
  7. Robert:
    Copied ofx_config.cfg and sites.dat from current install to new folder
    with the new code. I did not copy over my scrubber.py file.

    Restored a backup from about a week ago to a test file. Downloads
    went without a problem. Quotes from Fidelity were correctly dated.
    Yahoo quotes were also OK.


    I had looked thru the code some. I noted there was a new rlib1.py file.
    Saw things that pointed to future planned enhancements.

    Looks good. Works well. Thanks.

    ReplyDelete
  8. Updated BETA again. You can now define a "multiplier" for a ticker symbol. The quote result will be multiplied by this value before sending to Money. Updated post above to describe a little better.

    ReplyDelete
  9. Beta works for me. Thanks again Robert!!

    ReplyDelete
  10. Robert,

    Can you make a couple of changes to make the scripts work on other versions of Python, such as the Python.org one?

    Here they are:


    1. Add the following line on TOP of the Python executable scripts, i.e. Setup.py and GetData.py

    #!/usr/bin/python


    If the Python script is just an include this line is not necessary.

    2. Replace calls to os.system(ofxfile)

    with

    os.system('explorer "' + ofxfile + '"')

    The os.system(file[2]) call in GetData.py also needs to be replaced with os.system('explorer '+file[2])

    We need to specify Windows Explorer as the application and OFX file as the file to open.
    (Specifying just an OFX file will make other versions of Python think that it is an executable file. Also, we cannot use msmoney.exe because it may not be in the PATH, while explorer.exe is accessible from anywhere.)

    ReplyDelete
  11. Quotes.htm didn't work for me, or at least not how I expected it to work.

    The page is fine, but the the stock links don't link to the yahoo quote pages?

    I was able hack this be editing the table section of the quotes.htm file:

    a href=http://ca.finance.yahoo.com/d/quotoes.csv/q?s=
    to.....
    a href=http://ca.finance.yahoo.com/q?s=

    ReplyDelete
  12. What do you have entered for the alternate YahooURL in your sites.dat file? It should read:

    YahooURL: http://ca.finance.yahoo.com

    ReplyDelete
  13. Robert,
    Yes that was the problem sites.dat had the YahooURL over-ridden.

    -Thanks

    ReplyDelete
  14. Beta updated to reflect comments thus far, and a few small things I've noticed. Yuriy: I haven't looked at your request yet... but will.

    ReplyDelete
  15. Robert,

    After looking thru sites.template I realized that my old sites.dat was missing alot, so I manually updated the file. When you update your instructions, you might want to include a note to that effect for others, although it's nice that your scripts use a default setting not dependent on sites.dat

    -ameridan (Dan)

    ReplyDelete
  16. Dan: I had the same thought. I need to add a "sites.dat / user options" description link to the instructions, listing all of the options and their meaning.

    ReplyDelete
  17. Just realized an important omission on my post above. The code was also updated to use an actual quote date/time rather than "today's date" for the DTASOF (statement date) field. Post updated.

    ReplyDelete
  18. Yuriy: Does the "other" Python interpreter support os.startfile()? I'm using this call when showing quotes.htm, so that the script doesn't wait for the browser to close before continuing.

    ReplyDelete
  19. Robert: No. It appears that os.startfile() is only present in the ActiveState version. However, I did the search for os.startfile() and didn't find this call in any of the BETA files. You replaced previous instances with os.system() calls. To open an html file I would again call os.system('explorer "'+ htmlFile +'"').

    ReplyDelete
  20. Yuriy: changed to os.startfile() in latest snapshot, since I noticed the script waiting for the browser to close on an XP machine. I could use a call to the Windows "start" command, but that is only supported in XP and newer, I think (?).

    ReplyDelete
  21. Robert: No, the "start" will not work with the CYGWIN + Python combination. I tried the "subprocess.Popen" that does not wait for return but it kept crashing the system after the second invocation.

    I then checked the API documentation again and found out that Python.org's version also supports os.startfile() on Windows platform.

    Your code should work on Python.org's version ran directly under Windows but not in combinatin with CYGWIN. And that's ok.

    CYGWIN is a UNIX command line emulator that introduces additional layer of problems with different path structure and lack of Windows "start", "dir" commands etc.

    It seemed that the call to os.system(explorer + filename) would solve the problem but it's a synchronous call. The workaround (such as sending the process into the background by appending "&" at the end, e.g. os.system("explorer "+fileName + " &")) would not work on Windows.

    Considering that my CYGWIN+Python combination is not used widely I think it is not worth the hassle of maintaining compatibility. Those who use GYGWIN are familiar with coding and should be able to modify your code on their own.

    I think it makes more sense to concentrate on business logic instead.

    ReplyDelete
  22. Just tried to add an ING account and got an error during the test in SETUP.PY:

    '.\xfr\ING' is not recognized as an internal or external command,
    operable program or batch file.

    Help?

    ReplyDelete
  23. One Vanguard account reads OFX statement is missing required in getdata. Any clues as to resolve this.

    ReplyDelete
  24. OFX reads OFX statement is missing required SECLIST in getdata.

    ReplyDelete
  25. 1. '.\xfr\ing': Please post site.dat SiteName entry for 'ING'.

    2. The 'SECLIST' message is intentional, and is an attempt to keep Money from bombing. Vanguard sometimes omits this section, and Money does *not* like it. Passing this particular malformation to Money *bombs* the program (as best I can tell from testing). The fix: Set your 'minInterval' value for the Vanguard site entry such that at least one transaction is received with each statement download. If the account receives at least one transaction per month, then 35 days will work. If it only has a transaction every 3 months, then a value of 100 days should work).

    ReplyDelete
  26. Also, I'm assuming these last few comments relate to the latest beta code, and not 13-Oct-2010 release.

    ReplyDelete
  27. Robert - regarding xfr/ING error, SiteName for ING is: ING DIRECT BANK

    I'm using the 16-FEB-2011 BETA.
    (PS - I'm "Anonymous" that posted the 4:08 comment regarding ING, only.) Thanks!

    ReplyDelete
  28. Robert - an update to my xfr/ING error. The OFX file imports to money just fine when I double click on it. So, I think the problem is not with the OFX file, but rather with the way setup.py or getdata.py is sending the OFX file to Money. Hope that helps.

    ReplyDelete
  29. I'm running the latest beta (Feb 19th). Two issues I'm seeing:

    1) Running the Setup.py - it seems to fail on option "7. Test Account" for all accounts whose site name is a multi-word string. For example I have sites: "TD Ameritrade" and "Pershing Advisory Solutions". For both download completes successfully, but when I choose to Send results to Money the error message comes up that says: '.\xfr\TD' is not recognized as an internal or external command, operable program or batch file. Similarly for the pershign site the error is identical but the first part of the error message has '.\xfr\PERSHING .... in it. I don't see this problem when I run Getdata script on the other hand, so this is something that is specific to Setup. Also testing accounts that have single-word site works fine too.

    2) The second problem I'm seeing was there in the non-beta version as well. That is why i upgraded to beta thinking that the problem might be solved - but no luck. So here it is: I don't have a separate account where i keep track of stock quotes. Reading various comments on this site it appears that most of you do. I don't care to track quotes other than for those stocks that i happen own. When i initially ran this program it picked up the latest pricing for all my stocks and everything looked fine across the board for all my brokerage accounts. However, since that initial run, the stock prices don't seem to change in Money anymore. They're stuck on 2/18/2011 pricing even though today is 2/23/2011. I'm not sure what is going on, but an interesting thing is that the stock quotes actually do get downloaded but for whatever reason Money choses to ignore them. When i run Getdata and my statements get pulled and money goes to that "You have X statements to read" page where all my accounts are listed and Last downloaded/ Bank Balance / Local Balance Columns are shown. Clicking on any of my brokerage accounts, I do see the latest pricing just fine. But for some reason that pricing doesn't propagate to the rest of the program. All other pages of MS Money (Account List/Portfolio/Price History) seem to be stuck on the 2/18/2011 pricing. Why would that be? Am I missing something obvious? The very first time I ran the scripts (2/18/2011) the correct pricing was picked up and propagated everywhere in money where such pricing is shown. But now the scripts just download the correct pricing, but money doesn't do anything with it. The only page where i see this new pricing is on that new statement page. Nowhere else.

    Any help would be much appreciated.

    PS. Thanks for releasing this code to the world. You're the man.

    ReplyDelete
  30. Hi Robert,
    I think we should be able to put this into a web service and divert Money to this - that way Money would work exactly as it did (does) before MS closes the service. The problem then is split into 2:
    1. Hook Money to redirect the lookup
    2. Include this lookup in a suitable web service.
    If there is any interest in this approach, we can take it further.

    ReplyDelete
  31. Re the ING issue: I'd like to see the SiteName entry for the site in sites.dat. The reason is that there may be a character there that I haven't allowed for.

    ReplyDelete
  32. David: A less ambitious goal would be to automate the import of the OFX results into Money. I personally have very little time and don't have intentions of hacking the Money interface, but anyone that can, and wants to contribute, is *very* welcome. It would be a nice improvement.

    My "to do" list still has "try to combine similar ofx files before upload". Even that turned out to be a little messy, as Money doesn't handle DTASOF fields the way I'd think it should. If we had a direct hook into the Money data table for "downloads", then that would be ideal.

    ReplyDelete
  33. Robert - Re the ING issue - see my posting from 2/22/2001 @7:25pm for SiteName.

    ReplyDelete
  34. I posted something pretty long yesterday, and my post disappeared. I'm going for a shorter version now. 2 Issues I'm seeing

    1) Setup.py account testing seems to fail for every multi-word syte. "TD Ameritrade" fails "Pershing Advisory Solution" fails. The error occurs when attempting to import to Money and the error for TD Ameritrade is '.xfr\TD is not a recognized as an internal or external command..... for Pershing its the same except that the first part says .xfr\Pershing. This failure only happens when running setup. Getdata imports statements successfully. Also setup seems to work fine for one-word site accounts

    2) Stock prices never change for me. What's happening is kinda strange. I don't have a dummy account to do download quotes for a set of tickers. I only care about prices updating for the stocks I happen to own. When i first ran your scripts (non-beta ones) on 2/18/2011 it picked up new statements just fine and picked up new prices for all my stocks across all brokerages just fine. These prices propagated through all the Money. So far so good. However, since then all my prices are stuck on the 2/18/2011 levels even though today is 2/23/2011. I downloaded the new beta (2/19/2011 version) hoping this would fix the issue, but no luck. To make matters even more confusing, the quote download seems to work, but money seems to ignore the prices. After I run Getdata script and money takes me to that "You have X statements to read" page, clicking on any brokerage account, brings up a "Review Investment Holdings" page with the "Finish" Button on the bottom. Here I see all the tickers and all the brand new pricing. But this pricing doesn't seem to propage to the rest of the software. Portfolio/Accounts/Price History/ etc. Are all stuck on the 2/18/2011 pricing. Why would this be? Am I missing something?

    ReplyDelete
  35. How does posting here work? I posted twice, and all my posts have been removed? Are they pending your approval or what is going on?

    ReplyDelete
  36. No approval needed, and nothing deleted (by me). Vanishing posts have been seen on here before, but I don't know why. It probably works better if you use an account, rather than anonymous(?).

    ReplyDelete
  37. Regarding the ING issue, what version of Windows?

    ReplyDelete
  38. I posted something pretty long yesterday, and my post disappeared. I'm going for a shorter version now. 2 Issues I'm seeing:

    1) Setup.py account testing seems to fail for every multi-word site. "TD Ameritrade" fails "Pershing Advisory Solution" fails. The error occurs when attempting to import to Money and the error for TD Ameritrade is '.xfr\TD is not a recognized as an internal or external command..... for Pershing its the same except that the first part says .xfr\Pershing. This failure only happens when running setup. Getdata imports statements successfully. Also setup seems to work fine for one-word site accounts

    2) Stock prices never change for me. What's happening is kinda strange. I don't have a dummy account to do download quotes for a set of tickers (as a lot of you seem to do). I only care about prices updating for the stocks I happen to own. When i first ran your scripts (non-beta ones) on 2/18/2011 the new statements were picked up just fine and picked up new prices for all my stocks across all brokerages were shown. These prices propagated through all the Money. So far so good. However, since then all my prices are stuck on the 2/18/2011 levels even though today is 2/23/2011. I downloaded the new beta (2/19/2011 version) hoping this would fix the issue, but no luck. To make matters even more confusing, the quote download seems to work, but money seems to ignore the prices. After I run Getdata script and money takes me to that "You have X statements to read" page, clicking on any brokerage account, brings up a "Review Investment Holdings" page with the "Finish" Button on the bottom. Here I see all the tickers and all the brand new pricing. But this pricing doesn't seem to propagate to the rest of the software. Portfolio/Accounts/Price History/ etc. Are all stuck on the 2/18/2011 pricing. Why would this be? Am I missing something?

    ReplyDelete
  39. New beta posted. I could only think of one recent change that *might* affect pricing updates, so I rolled that one back. Updated file calls, hopefully handling spaces better for all. It should have worked before, and does on my machine (?). Please report.

    ReplyDelete
  40. The issue with space is gone. So testing an account in Setup.py works fine now in the 11-23-2011 version.

    As far as stock pricing - it doesn't work. Bare in mind that it didn't work in the non-beta version either. It only worked the first time i ran it. Since then the stock prices have been stuck to whatever numbers were downloaded the first time I ran the scripts.

    ReplyDelete
  41. Posting some some troubleshooting info:

    I just enabled stock/fund quotes and added a single ticker to the tag (this stock was in my brokerage account and was stuck on 2/18/2011 price). I've done this and uploaded the quotes to money, and the new price shows up everywhere - but only for that single ticker. All other tickers are still stuck on 2/18/2011 prices.

    Now this is the first time I've tried this. I'm not sure if tomorrow the price will update again for those single ticker. After all, updating prices from the brokerage statements also worked the first time i ran it, but not since then.

    ReplyDelete
  42. Another troubleshooting info which might be interesting:

    For those tickers that are stuck on the 2/18/2011 pricing, if i go to update the price manually vi MS Money GUI, I see 2/22/2011 data point. But the value is identical to the 2/18/2011 price. So it seems like Money added another data point to the price history, but it didn't change the price. If i go ahead and delete the 2/22/2011 entry manually, and then re-run the Getdata (without having anything in tag in sites file) the new price gets updated for that ticker alone!

    Strange behavior.

    ReplyDelete
  43. Robert: I just saw that there is a PocketSense beta and have not tried it but I looked through the changes and blog entries so thought I would pass along some comments. I will start by describing how I currently use the PocketSense scripts. I am running Windows 7 and Microsoft Money 2007, and I have three separate Microsoft Money files that I access by using a batch script to specify the file to be opened by Microsoft Money. I created a separate ofx_config.cfg file for each Money file, and I use my Microsoft Money launch script to move the appropriate ofx_config.cfg file into the PocketSense directory before starting Microsoft Money. This ensures that only the specific accounts will get downloaded for the specific Microsoft Money file that is open. I also set up a link to execute the Getdata.py script from the Microsoft Money Favorites/Favorite Web Sites menu. I am not using the Ameridan add-on for this but am directly executing the Getdata.py script. I don’t use the PocketSense quote retrieval functions, which is what most of the beta changes seem to be for, so my comments only relate to doing OFX downloads for accounts at financial institutions in the U.S.

    ReplyDelete
  44. Transaction Download Process: With Microsoft Money open my transaction download process is: 1) Navigate to Favorites/Favorite Web Sites and click on the transaction download link that I created, 2) Select Open on the Getdata.py security warning, 3) Press Enter on the PocketSense Download Transactions script, 4) Enter my password and press Enter again, and 5) Press Enter for each Import Summary.

    This is easy enough to do, but it would be nice if I could eliminate steps 2, 3, and 5. The most annoying item for me is the need to press Enter for each Import Summary, so that would be highest on my wish list.

    It would also be nice if there were a way to start Getdata.py with a parameter that specifies which account(s) to download. With this it would be possible to emulate the Microsoft Money process of only downloading specific accounts. This could also be a better way to do transaction downloads for separate Microsoft Money files.

    Even without these changes the PocketSense scripts are doing the job for me. I am happy to continue using Microsoft Money as long as this continues to work.

    ReplyDelete
  45. so1dieroffortune, check the date an time of that OFX file. What the situation may be is that you downloaded that OFX file before your broker put Tuesday's prices into the file. It might be after midnight (00:01 Wednesday) before the Tuesday prices are in.

    Monday the markets were closed. So prices would be from the previous Friday.

    The date that Money thinks is the date the quotes are for is the date in the first DTASOF field.

    Would you care to name the broker involved in the stale quote situation?

    ReplyDelete
  46. PHWhite, see post 200 at http://pocketsense.blogspot.com/2010/11/replacing-microsoft-money-continued-nov.html and then post 201 on
    http://pocketsense.blogspot.com/2010/11/replacing-microsoft-money-continued-nov.html?commentPage=2

    ReplyDelete
  47. Cal, Both TD Ameritrade and Pershing both seem to have the issue i described.

    If what you're saying is right (and it could be cause i was playing with this very late at night after midnight, and it is consistent with the observed behavior as far as I can tell) then I'm troubled by the behavior of MS Money. I understand sticking in an older price if the new one is not available, but what i don't understand is why MS Money then fails to update this stale price when the new one becomes available from a broker's statement?

    Hypothetical scenario:

    Say it is 1 minute after midnight - I download my statements but they pull in older prices because that is what the broker shows. Then 15 hours later in the middle of the next day I download the statements again.. these statements now contain the new updated price - but these end up being ignored because Money already recorded the price for that day 15 hours earlier. Why wouldn't money always update to the newest price (Regardless if there is a price entry for that date already or not). Once money records a price for a certain date from a statement, can that price never be updated again via a new statement?

    I'm just brainstorming here. Cause clearly the price does get updated if I stick the ticker in the tag, and it also gets updated if I manually remove the stale price and then download the statement again. But the price DOES NOT get updated from the statement if a price entry already exists for that date.


    IF this is how the thing works, is there a way to work around this? I don't want to add and remove items in the tag because I buy and sell stocks frequently and I just don't feel like doing the manual work. But I also don't want to run the risk of downloading stale prices that can never be changed.

    ReplyDelete
  48. For example, would it be possible to force the stock price update for all the tickers available in the statements in a way that overrides the previous value? Kinda like what happens for the tickers in the "stocks" tag?

    ReplyDelete
  49. so1dieroffortune said "But the price DOES NOT get updated from the statement if a price entry already exists for that date."

    That is normal (if frustrating) behavior for the Money OFX import routine. Only one quote per date is accepted. If a ticker/quote price already exists for a date, any attempt to load another for the same ticker/date via OFX will be ignored.

    ReplyDelete
  50. Robert 2 questions:

    1) In the OFX is there a date associated with the price, or is the date taken based on when the file is downloaded?

    2) Would you consider providing a flag which if turned on would force the update of all the tickers that are present int he statements regardless of whether there is price for the current date or not? (I'm assuming this is what happens for all the tickers in the "stock" tag right now. But I'd rather not maintain this list if it is always going to be the exact duplicate of what's in the statements already.

    ReplyDelete
  51. so1dieroffortune, Money works the way that it works. That way is that it accepts the first price for a given date, and ignores later prices with the same date.

    There is some art in supplying the right date for a given price. If the PocketSense scripts don't do that for a given FI, then there is the potential to write an FI-specific scrubber. I have one for Fidelity that I hope to improve. Other than that, the the best way you can handle this is to find a good time to download. To download from Ameritrade, downloading after 1 AM and before midnight eastern time will probably give your best results. Only do that downloading the day after the markets were open. For example, you would avoid that download on Sunday or Monday.

    ReplyDelete
  52. To reiterate what Cal said, we have no control over how Money handles OFX imports. It only accepts a single quote per day per file, and there is no way to over-ride the behavior.

    Money uses the statement date/time value for price updates, meaning that a single date/time is used for all quotes in the statement. If the same symbol/value/date shows up in multiple ofx statements, only the first is recorded.

    ReplyDelete
  53. Sure, but when I added the ticker to the "stock" tag and enabled quote download from yahoo, this overrode the latest price. So can't this mechanism be re-used to fetch all the quotes for all the tickers that appear in the statements as if they were listed under the "stock" tag? I could probably figure out how to do this myself (I'm a programmer) but i'd rather not fork the project if you're goign to keep adding enhancements. Let me know if there is any chance you would add this feature. Thanks!

    ReplyDelete
  54. The quote values in the statements are being sent to Money, but Money ignores them, because the dates haven't changed since the previous download (i.e., the date/time provided by the ofx provider hasn't been updated). If we took those same values and sent them separately, they would still be ignored, as they would still have the same value/date. The Yahoo quote symbol was imported because it had a newer date/time, as provided by Yahoo. If you run the Yahoo quotes for a stock during the day, you'll find that only the first is remembered by Money (for one that is actively trading during the day).

    This can be verified by looking at the last ofx file sent from the bank, and then looking at the quotes*.ofx file to see that the date/values are different.

    ReplyDelete
  55. Cal: Thanks for the response to my Transaction Download Process post. It looks like I could write a batch script to automatically respond to the Getdata.py script when it asks:
    Download transactions? (Y/N/I=Interactive) [Y]
    However, I do not see how I could use a batch script to automatically press Enter for each Import Summary because that happens after the batch script returns control to Microsoft Money to process the OFX downloads. Am I missing something?

    ReplyDelete
  56. PHWhite: You cannot get the batch file to click the window within Money. You could, however, have the PocketSense scripts queue up the files for Money without interaction. You could even have your batch file launched on a schedule.

    To press the button for the Import Summary window within Money, you could use PTFB to push that button. I have not tried the new version that works with newer versions of Windows. I did read that one person had tried it successfully.

    ReplyDelete
  57. PHWhite: I can easily add an "autostart" flag, but it couldn't really be "automated" with encrypted settings, as the user still has to enter their password.

    ReplyDelete
  58. Robert: I don’t expect to completely automate the process but would like to minimize what I need to do each time I download transactions. An "autostart" flag plus a "combineOFXfiles" flag would get my vote so that I would only need to enter my password and respond to a single Import Summary window instead of one per account. That would probably be about as good as it could get without using something like PTFB as suggested by Cal.

    ReplyDelete
  59. A couple of comments regarding the possibility of "combining statements".

    1. Money appears to ignore all but the first statement date when assigning quote values. If multiple statements are combined, all quotes would show the first DTASOF date it found.

    2. At best, the number of statements could be reduced to 3 (bank, credit, investment). Only "like types" will be accepted during import.

    I have most of the logic worked out for this feature, but have been hesitant to code / include for reason #1.

    ReplyDelete
  60. Combining Statements: Regarding the quotes date issue, perhaps it would make sense to have a flag to controls whether or not investment statements are conbined. Presumably combining bank statements and credit statements would not have this issue. In my case, I only have investment accounts at a single financial institution so I expect that it would be safe enough for me to combine my investment statements, but others might prefer to avoid the risk of quotes with incorrect dates.

    ReplyDelete
  61. Missing Quote Updates: I looked at the quotes today for the investments in my Ameritrade accounts and noticed that one of the investments has no new quotes since 2/2/2011, which is the last day there was any investment activity for the investment. Other investments appear to get quote updates every day that I download transactions regardless of whether or not there is any investment activity. As a result, the Microsoft Portfolio value shown for this one investment is out of date. I am downloading my Ameritrade statements but I don’t track anything other than the specific investments in my Ameritrade accounts. The specific investment where I am not getting quote updates is RPIBX (T Rowe Price International Bond Fund) if that makes any difference.

    ReplyDelete
  62. PHWhite: It is very likely that the broker is not providing an updated statement date in the ofx message. You can include the ticker symbol in your sites.dat for more frequent updates. There is a user flag, SaveTickersFirst, that controls whether statements or quotes are loaded first. By default, the symbol will only be updated from Yahoo if the broker did not send a quote on the same date. If the option is enabled, then the Yahoo quote value is always chosen first (if found). If the broker uses a different symbol, then this will not work. I have considered adding a quote option for "alternate ticker symbol". This would allow a user to define the Yahoo symbol, but have it send an alternate symbol to Money.

    ReplyDelete
  63. Robert -
    Regarding the ING error, you asked on 2/23 @ 9:23pm what version of Windows - I use XP. I only get the error while first setting up an ING account and "testing" it using setup. Using GetData, the OFX from ING imports just fine.

    I've had another issue for the past few days with quotes. They are being imported into Money with tomorrow's date (I downloaded on 2/28, quotes were entered into Monday as if from 3/1. I downloaded in 3/1, quotes entered as if from 3/2). Looking at the OFX, I saw that all quotes had the correct date except for the currency conversion "quotes" which had today+1. So, I just deleted currency conversions from my list of stocks, and now all quotes are imported with the correct date. So, I'll do without currency conversion updates for now, until this problem is resolved.

    Thanks for taking time to work on these great scripts! They're real time savers.

    ReplyDelete
  64. Beta updated. Quotes should ignore date/time values greater than today. Setup test with 'spaces' in sitename should work.

    ReplyDelete
  65. I have uploaded a zip file with my revised Fidelity Investments scrubber. http://www.speedyshare.com/files/27186596/CL110302.ZIP

    Its method is to base the DTASOF and even the DTEND on the DTSERVER value. The existing DTASOF in the file was found to not be a good thing to base the revised DTASOF in that I found downloads near the critical time to have the same DTASOF with different vintage quotes.

    Note that all of these date concerns are only for quotes. There is not an ambiguity with the more important thing: the transactions. Even tho the quote dates are less important, making them as accurate as we can is good.

    The zip file includes scrubber.txt which discusses the scrubber. There are two scrubber versions in there-- one with some logging to a log file and one without.

    ReplyDelete
  66. One more comment on my Fidelity scrubber: if the date that I would have used is a Saturday or Sunday, it backs up the date to the preceding Friday. Even more sophisticated would be to look for market holidays, but that is not worthwhile. Plus I don't think that will be a problem anyway. I should probably have logged when/if such a weekday correction had been needed, if ever.

    ReplyDelete
  67. Robert: Thanks for maintaining these scripts. They're great.

    I am fetching quotes for close to 100 stock and fund ticker symbols and most of the time the quote fetch fails for at least 2 or 3 of the symbols. It happens with different symbols every time and it typically works on subsequent retries.

    I think it would be worthwhile wrapping the URL fetch into a simple retry loop to work around the spottiness of the Yahoo! servers.

    Here is what I did and it has made the quote getting much more reliable (formatting will probably not come through):

    for retry in range(10):
    try:
    csvtxt = urllib2.urlopen(url).read()
    self.status = True
    break
    except:
    print "Unable to get quote for ", self.ticker, ". Retrying..."
    time.sleep(1)
    self.status = False

    if not self.status: print "*** Error retrieving quote from Yahoo!"

    Perhaps you can incorporate that change into your script if you agree that it would make sense.

    Another worthwhile enhancement may be to fetch all quotes in one go using SYMBOL1+SYMBOL2+... in the query string and then process the multi-line CSV file. I have played with that a little bit and it should speed the quote updating up tremendously for people with a large number of symbols.

    ReplyDelete
  68. All works fine on my windows xp laptop. However, same files fail on my Mac running XP under Parallels. The downloads go fine but won't upload fox files into Money due to error in line 120 (prior version not this beta) - module not found. I think I need to add something to the ofxscript line similar to a post above where "explored" was added. Please advise.
    And great thanks, Robert, for saving me a ton of work.

    ReplyDelete
  69. Added 10-sec timeout for Yahoo quote response. Beta updated. I actually implemented the csv parse routine with the intention of doing a single fetch for quotes, but adding the screen scrape option complicated a tad. The code will need to be restructured a bit to incorporate both methods. I had no idea anyone would grab 100+ quotes into Money.

    ReplyDelete
  70. Shanmuk: The code is written and tested on a Windows machine. I can't offer much assistance.

    ReplyDelete
  71. Has anyone else experienced an OFX update causing the bank balance to be incorrect? I have a pretty simple installation at present with only a checking and savings account receiving updates. Both of them seem to be grabbing the correct transaction details, but sometime between the beginning of February (when I first used the script) and now, the "Bank Balance" got out of whack with the bank's actual balance. Worse, the difference between Money's "bank balance" and the bank's actual balance does not seem to match any value (or halved value that was debited/credited to my accounts in the past few months. Double checking the downloaded transaction details against my online (and, therefore, current) banking information shows that there were no skipped transactions, Money's "ending balance" matches up with my checkbook register, and everything balances when I put in the bank's (actual) current balance and ignore Money's suggestion.

    Thoughts?

    ReplyDelete
  72. Okay, I'm going to step out on a limb here. First let me say Thanks! as is this is a great service you have done for abandoned MS Money community. Now I will stretch and ask for a new feature for the quotes.htm page. Which I have found to be very useful.

    Yahoo finance isn't a bad link for stock tickers, but is pretty lame when in comes to mutual funds. I vastly prefer morningstar's content. I understand Yahoo usefulness as the quote server, but what about providing an alternative method to link to an a user defined URL in quotes.htm? I was able with some trial and error to hack rlib1.py so it pointed to morningstar, rather an Yahoo. But this is a crude hardcoded alternative. I would be great if quote.htm could be customized to link to alternative sites.

    Hard coded hack replaces:
    YahooURL + '/q?s=
    with
    "http://quote.morningstar.com/fund" + '/f.aspx?t

    But actually coding an alternative URL function into the Python is beyond my current capability.

    ReplyDelete
  73. Not a bad idea regarding an alternate quote site for displaying mutual fund info. The current quote engine supports a screen scraper, that happens to support some foreign exchange funds. Those symbols would not match Morningstar or any other site. Coding would therefore be more involved, and I'm still not sure we could guarantee that a randomly selected Yahoo symbol would be identical on another site (?).

    ReplyDelete
  74. So I guess this would be of limited utility for someone using non-US stock or mutual funds. As long as it didn't crash I'd consider not having a link for these quote to be a far trade off for being able to have a link to the site I wanted.

    I'm going to keep my hack for now, I'll just have to re-implement the hack for every new release.

    For anyone else trying follow my footstep the more accurate substitution is:

    Hard coded hack replaces:
    YahooURL + '/q?s='
    with
    "http://quote.morningstar.com/fund" + '/f.aspx?t='

    ReplyDelete
  75. For the past several days I've been getting an error from Ameritrade when using Pocket Sense.

    The \xfr\*.ofx contains the following:

    "/amer/html/fatal.html"
    The HREF takes you to: https://wwws.ameritrade.com/amer/html/fatal.html

    I didn't see anything in Google and searched this blog for Fatal but didn't see anything posted.

    Appreciate any help. Thanks.

    ReplyDelete
  76. Ameritrade fail: See http://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/ for Ameritrade info. I suspect you are locked out somehow as discussed in note on Ameridan's Money blog. If you try setting up an account with 123446789 as the account number and get your same symptoms, that could be informative.

    ReplyDelete
  77. First of all thanks for the great service you are doing to the former MS Money community. The beta version works great for me except for a minor issue.
    This happens when I download quotes for my stocks. For example when I downloaded quotes today (Mar 12) the stocks had a price for date Mar 11 but the price quoted was actually as of Mar 10th. In MS Money when I go to "update price manually" and delete Mar 11th price and close,MS money automatically updates with the right price as of Mar 11th. So every time I download quotes, I manually have to go to each stock and delete the current day's price and close to get the right price.
    Just to clarify I am downloading quotes only once every day at night.

    ReplyDelete
  78. Re: wrong quote date/time, where are you located and which Yahoo server? There have been various issues with date/time recording ahead/behind a day from folks far from the EST timezone. The latest beta has a new parameter in sites.dat:

    YahooTimeZone: -5:EST

    This parameter is appended to the quote time. When Money reads an OFX file, it adjusts the time provided to local time. This doesn't always work as you'd expect. I made it a user-defined parameter, so that it can be adjusted forward/backward--with the intent of adjusting date/time differentials between the server and user. I haven't tested it for other values... but it's an option to play with.

    ReplyDelete
  79. I should add: although the YahooTimeZone should provide a user-adjustable time adjustment, it may be preferred to add a timeOffset parameter for quotes, the same I as I added for site entries. (?)

    ReplyDelete
  80. Robert, does the beta version now handle quotes of Canadian Mutual Funds?

    ReplyDelete
  81. Re: Canadian Funds: Give it a try... it worked for a couple I tested before. Report back.

    ReplyDelete
  82. Robert, I downloaded the Beta Version and when I double click on Setup.PY, I get the options and when I select 0 (Save & Exit), it does not seem to be creating the SITES.DAT file. I cannot find it anywhere. Help!

    ReplyDelete
  83. Anonymous,
    Make sure all your python files are not within the Program Files folders, but rather in a subfolder of My Documents (or the equivalent).

    -ameridan

    ReplyDelete
  84. @ameridan....you ROCK!. It worked by moving the python files to My Document folder.

    ReplyDelete
  85. Robert, Lets take TD Canadian Bond Index-e. The TD Symbol is TDB900. Yahoo shows it as F0CAN05MYF.TO

    So, I entered F0CAN05MYF.TO in the SITES.DAT file. I downloaded the quote successfully. However, since it was showing as TDB900 in Money, the quote did not get updated. So, I tried changing the symbol in Money. However, it can only update as F0CAN05MYF.T (without the O at the end). Tried to update it several times in Money with .TO extension but it does not.

    So, the quote for TD Canadian Bond Index still shows the old quote.

    Help?

    -Sam

    ReplyDelete
  86. Sam: I'll add an "optional symbol" parameter for the sites.dat ticker entries. By this, I mean "grab quotes for the Yahoo symbol", but pass it to Money as an (optional) user-defined symbol. I can't work on it until maybe ~ Sunday... stay tuned for an update.

    ReplyDelete
  87. Funny about the sites.dat file, as I never even considered how Vista/Win7 handles user files located under \Program Files. As Dan mentioned, placing the files *anywhere* else should fix that oddity. The file was being created, but in a separate system folder that Windows maintains for user-related Program Files. I believe this was an added 'feature' beginning with Vista.

    ReplyDelete
  88. Beta updated. See #13 in "change summary" post above.

    ReplyDelete
  89. Hi Robert,

    I downloaded the new Beta Version and it appears to be linking the yahoo ticker symbol with what I have in Money has but the quote in Money is not getting updated to the correct one. I opened the browser after taking the option GETDATA, it does show TD Bond Index as follows instead of the yahoo ticker symbol in the site.dat (F0CAN05MYF.TO):

    TDB909 TD Canadian Bond Index - e 11.0 3/21/2011 4:00pm

    it shows TD Canadian Index as follow:

    TDB900 TD Canadian Index - e 22.41 3/21/2011 4:00pm

    But in both cases the quote shown in money is 10.99 and 21.65 (the old quote).

    Here is how my Sites.DAT file is setup:

    F0CAN05MYF.TO s:TDB909 # TD Canadian Bond Index-e
    F0CAN05NJO.TO s:TDB900 # TD Canadian Index-e

    Help Pls....

    -Sam

    ReplyDelete
  90. Robert, and by the way I had created an investment account Z-Quotes in Money to import these downloads. Since it did not work, I even deleted this account and re-created it before the second try just in case.

    ReplyDelete
  91. Sam: When you first add a new symbol, Money asks to associate it with an existing symbol, or to add a new one. Did this happen when you redefined the symbols? If not, *delete* any tickers you have created *other* than the original that you want to associate the values with.

    ReplyDelete
  92. Deleting a symbol (Money Plus):

    Investing -> Work with investments -> Delete an investment from all accounts

    ReplyDelete
  93. Yes, Money did ask to associate the new investment with an existing one. Since it did not work i.e., the quotes of TD Bond and TD Index did not update to the new value, I deleted the "dummy" investment account and re-created it. This time when GETDATA finished downloading the quotes, it imported all the investments without asking for an association. Despite this, the quotes did not get updated.

    -Sam

    ReplyDelete
  94. And Robert, when I click on Investing, it takes to the web page MSN Money. I do not get the option of "Work with Investments"

    -Sam

    ReplyDelete
  95. My previous post did not get "Posted"...so, here it is once again:

    Yes, Money did ask to associate the new Symbol with the existing investment. Since it did not work i.e., TD Bond and TD Index still showed the old quote, I deleted the "dummy" investment account and re-created a new one. So, when I downloaded the quotes again, it imported all the investments without asking for any association as it recognized the symbols with the existing one.

    -Sam

    ReplyDelete
  96. Robert, could it because of the currency code?

    For example, when I download a combination of US Stocks and Canadian Funds, I get an error during import in Money that the currency of the account should be set to USD as the downloaded file contains USD transactions.

    When I change the account currency to USD, I was hoping to receive a similar error for CAD denominated Mutual Funds. But did not. I suspect the CAD Mutual Funds are mistaken as USD denominated and consequently Money has trouble updating it?

    ReplyDelete
  97. Anonymous Canadian: If you want to bring in quotes for the USD-denominated quotes and the CAD-denominated quotes, I think you would have to have separate PocketSense folders with separate sites.dat files for each. Have the CAD quotes brought into a CAD account and have the USD brought into its own account. I think you would need to modify the account number in quotes.py from "0123456789" in one of the instances to maybe "CAD12345" or some such. That way Money would be able to track to which account the OFX file goes.

    Once you get this working, you could make a batch file to CD to each folder and to getdata separately for each.

    I have not experimented with this, but it seems as if it should work.

    ReplyDelete
  98. A comment to Cal's: The scripts only support a single currency now. So, quotes are imported into whatever currency you define in sites.dat (default is USD). I have not given much consideration to multi-currency configurations. I guess that, at a minumum, the account number passed to Money for quotes should be a user-defined value in sites.dat. Ideally, the currency would be a quote parameter option, and quotes would be auto-grouped by currency and sent separately to Money. The problem, however, is that this would require the user to associate each account correctly inside Money. Money doesn't tell you anything about the import, so it could get confusing.

    Regarding the deletion of symbols, when you go to Investing and a web page shows, there is an option on the page that takes you back into Money, with menu on the left. If you don't find it, then disable your Ethernet connection temporarily, and Money will default back to the internal page.

    ReplyDelete
  99. Cal: I tried with CAD and it did not work. Here is what is I did:

    1) Create a new dummy investment account Quote in CAD Currency
    2) Amended the SITE.DAT file as follows:
    QuoteCurrency: CAD # Currency for quotes. Default = USD
    3) Added just the 2 entries for CAD Mutual Funds:

    F0CAN05MYF.TO s:TDB909 # TD Canadian Bond Index-e
    F0CAN05NJO.TO s:TDB900 # TD Canadian Index-e



    Removed all entries in Indexes, Sticks, Currency:


    #--- indexes ---

    #--- stocks ---


    #--- Currencies ---



    Still the Quotes do not seem to be getting updated in Money. Not sure if I am doing something wrong or the script is not working.

    Sam

    ReplyDelete
  100. Hello everyone....breakthrough....figured out what needs to be done. Apparently, Money recognizes US stock with US prefix and CAD Stocks/Mutual Funds with CA Prefix.

    I amended the SITE.DAT file as follows:
    F0CAN05MYF.TO s:CA:TDB909 # TD Canadian Bond Index-e
    F0CAN05NJO.TO s:CA:TDB900 # TD Canadian Index-e

    This time it updated the quotes to the latest value.

    I will test further over the week-end and report back to you all. Good Night.

    Sam

    ReplyDelete
  101. Thanks Sam. Did Money list the funds as CA:*, or is the "CA:" prefix hidden?

    ReplyDelete
  102. I use Canadian Version of Money where CAD is my base currency. Therefore, the US stocks symbol show with US prefix. For example Aloca is shown as US:AA. The Canadian Stocks & Funds show only the symbol but CA: prefix is hidden.

    Cal: I suspect the default currency in SITE.DAT should match with the currency of the money dummy account but within SITE.DAT we can have combination of US and CA stocks/funds. The caveat is that they should be distinguished with US: or CA: prefix. This would make updates very easy.

    I will confirm this over the week-end after thorough testing. Stay tuned.

    -Sam

    ReplyDelete
  103. Sam: USD and CAD are close enough to each other that it may not be obvious which currency the quotes are coming in as. If you want the exchange rate known by money to be applied, then that will need some attention.,

    ReplyDelete
  104. I've been following this blog very much from the beginning and am exteemely grateful for the scripts.For some reason I had the impression that the stock prices in the portfolio would only update once a day,but I have noticed that they always refresh whenever i log in to Money.
    Not that I am complaining.

    ReplyDelete
  105. gun,

    If you do a manual update of one of your stocks, the right column will indicate either UPDATE or ONLINE. The scripted OFX stock quotes will result in UPDATE and only the first entry for the day will take, unless you delete that entry. However there's a chance you are still getting ONLINE quote updates, as they haven't been completely turned off for everyone. Those quotes will update in Money as they are received.

    -Dan

    ReplyDelete
  106. Evidently I am one of the few, online quote updates hav'nt been turned off. everytime I log in to Money-6 my whole portfolio quotes are automatically updated. I did not convert to the "Sunset-Edition". I wonder how that is going to last?

    ReplyDelete