Sunday, August 15, 2010

Replacing Microsoft Money (continued)

This is a continuation for discussing the PocketSense python scripts, since comments were up to 200 on the last entry.  Please post any issues, bugs or comments.  Positive feedback is appreciated just the same as bug reports!  A few more updates are in the works, although it shouldn't affect how most folks currently use the scripts (at least I don't think that they will).  For information about the scripts themselves, and for installation/configuration instructions, go to: http://sites.google.com/site/pocketsense/home/msmoneyfixp1

200 comments:

  1. I added a routine to scrubber.py to fix null time values (i.e., no time given, or a time value of 000000). Beta snapshot updated for anyone interested.

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  2. Robert, if you could crack the Canadian Mutual Fund issue, you will be a hero. The utility will become all encompassing and of the highest value!

    MS Money (Canadian version) does support download of Canadian mutual fund quotes and my portfolio value is adjusted accordingly. This means there is definitely some provider that offers this functionality.

    Eagerly waiting for your response (Smile).

    -Sam

    ReplyDelete
  3. Robert, sorry to bug you but could you please confirm if this is how the utility works:

    On Friday, Aug. 13, I took the option Getdata in the morning around 8:30AM PST. All U.S. and Canadian stocks reflected the new quote. I returned back in the evening from work and took the option Getdata once again but this time it did not update the stocks with the closing price. Next, just to play, I deleted the stock quote of MCD for Aug 13th within Money. I took the Getdata option and this time it updated MCD with the closing price but the rest remained as of 8:30AM in the morning.

    Finally, I took the option once again on Saturday, Aug 14 and this time all stocks were updated with the closing prices of Aug. 13.

    Does this mean we should take the Getdata option only once a day at the end of trading hours?

    Thanks!

    -Sam

    ReplyDelete
  4. Sam - Yes, Money will only store a single quote per day when using the OFX interface (or manual entry for that matter). There is no way to bypass the constraint that I'm aware of.

    There's actually another limitation of the current quote script, which I hope to improve. The script assigns the "script execution date" to the DTASOF field in the OFX file for the statement, and then assigns the actual quote date/time (DTPRICEASOF) to each quote. The way it's written now, Money uses the statement DTASOF for every quote result, and ignores the quote date/time. This issue is on my "to do" list, but I haven't had much time lately to look into it.

    ReplyDelete
  5. My tests on the new scrubber.py look good. I want to watch it after monthly close.

    Thank you for the update.

    Mike

    ReplyDelete
  6. I have problems with TD America
    When I run setup.py I find the following error in the ofx file
    Gun



    TITLE>Error 400--Bad Request/TITLE

    ReplyDelete
  7. gun: Do you mean TD Ameritrade, and when you run "Getdata.py"?

    First, verify your sites.dat entry against the entry on ofxhome.com, and verify the account data in Setup.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Stupid me!!!!
    in sites.dat I had "TD America" instead of TD Ameritrade. After correction and comparing with OFX home again
    I ran setup.py again, but still the same error:
    Error 400--Bad Request
    Gun

    ReplyDelete
  10. Robert said...

    gun: I'm assuming you verified your Setup entry for Ameritrade also? As a first step, post your sites.dat entry for Ameritrade here.

    ReplyDelete
  11. Thanks again for your patience with me.
    Found another mistake of mine in setup data.
    Had ID and account no. mixed up.
    Everything d/l into Money now.
    One more question, please. As I understand, Money can only be updated once a day, after Jan 31/2011 with these scipts?
    And can I keep running Money 2006 after that or do I have to get the Sunset edition? Gun

    ReplyDelete
  12. Glad you figured it out gun.

    You can download as often as you want, but only the first quotes download of the day will be used. You won't need to switch to Sunset edition - just keep it around in case you need to reinstall Money on your computer(s).

    -Dan

    ReplyDelete
  13. thanks for the info. that's good to know. everything is working fine now thanks to this blog and all the people involved. this is the best d.. blog in the universe Gun

    ReplyDelete
  14. I have a second laptop with Money 2006 installed. Now I just installed the pocketsense zip also to my second laptop. In order to avoid entering all my data again i copied the original sites.dat file into the new pocketsence folder on my 2. pc. But that doesn't seem to work. Should I also copy the setup.py file?

    ReplyDelete
  15. Copy the ofx_config.cfg and sites.dat files.

    ReplyDelete
  16. Yep, that did it, thanks
    And how about quotes.py?
    When i click it, only a split second screen appears. All my stock symbols are in the data file and tested.
    I guess quotes only works once a day and then nothing else is uploaded after that for that day?

    ReplyDelete
  17. Quotes.py cannot be run "stand alone". There are only two "direct execute" programs: Setup and Getdata. You're the first to ask, but it would be easy for me to add a "quotes only" option to Getdata, if that's of interest.

    ReplyDelete
  18. Thanks for the scripts. I am using them from Canada to update stock quotes and FX rates. Got a dummy account set up in Money, and after some tinkering got quotes in CAD working (had to change currency from USD to CAD in Quotes.py) but cannot get FX rates updated. The quote does get imported into my dummy account, however the exchange rates that Money is using is not updated. Is there a way to get this working?

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. If quotes can only be run once a day I don't think it's worth your effort. I would just like to run it to by pass all those pop up confirmations you get with a full download of all the accounts.

    ReplyDelete
  21. I don't use the exchange rates feature of Money, so I haven't tested this myself (just going by net info). If you look in the sites.dat file, you will see a section for exchange rates. Define the one you want here, and see if it updates correctly.

    I believe that USDCAD=X, would be correct for USD to CAD (if that's what you want)

    ReplyDelete
  22. I should clarify the above: The scripts support the "exchange rate" quote format above, but the use of such to update the rates in Money is based on user posts I've read on the net. I'm not at home, so I cannot test at the moment.

    ReplyDelete
  23. Yes I tried USDCAD=X and the quote loads into Money ok but it loads as a security, not an exchange rate. Exchange rate does not get updated

    ReplyDelete
  24. I verified the issue. The "built-in Money exchange rate tables" do not update using the Yahoo! exchange quotes. I have not yet seen a method to automatically update these rates via OFX. Unless something is found, rates will have to be manually updated inside Money.

    ReplyDelete
  25. Hi Robert;
    Many, many thanks to you and Dan for the generous code and the evaluations of alternatives that I didn't want to go through. I have MSMoney data back to 1985 and I still use most of it.

    I found PocketSense to be quicker & more straight forward to set up than what I remember from MSMoney. A mistake that I made several times was closing the Setup.pyc window after successful tests (assuming that it was writing to the ofx_config file as I was going along). Eventually figured out that "Exit" means "Save & Exit". Got this after trying all of the wrong solutions first.

    Next, I had much grief with Vanguard. I knew it must work since reading all of the blog posts showed that you use V. I am using the Sunset Version of MSMoney on Win XP. Each account tested "good" but MSMoney crashed as soon as the upload got to one of the retirement accounts. Would not restart & had to start Sample.mny, then cancel the import (postpone also gives crash), then open my Money file. The OFX files looked good as far as I could tell. Finally figured out that it was two Roth accounts where the most recent transactions were 6/3/10 and 12/30/09, respectively. Your many references to "Vanguard doesn't like short intervals" prompted me to try 90 days & got success. Doesn't seem to be any downside to 90 days, as either the Sunset version or your method gave me lots of old transactions but they all resolved w/o duplicate entries like I used to get in the past.

    Thanks again, Dudley

    ReplyDelete
  26. Thanks for the feedback Dudley... glad it works for you. Regarding Vanguard, did you begin with a 30 day window (the default I have for Vanguard)? If so, I'll change that to a default of 60 or 90 days. I remember 20 days failing for me, and 30 days working. The "result" if too short an interval was exactly as you described. Crash!

    I'm going to rename "Exit" to "Save and Exit".

    ReplyDelete
  27. Robert, I wasn't sure if the crash response might be just the Sunset version -- never experienced this kind of crash before. My guess is that the problem comes from an account with no entry that is recent enough. I have 8 Van. accounts and only the 2 Roth ones caused crash. Each of the others had activity w/in past 30 days. In the past, had bothersome double entries after a Money synch followed by a Van download (with "remove shares" entries that had to be fixed). I didn't try 60. 90 gave me lots of entries from my Advantage (checking) and money market accounts. But they all resolved properly with only 1 confirming window and no further steps. So I don't see any disadvantage to the longer period.
    Thanks, Dudley

    ReplyDelete
  28. For Robert or Dan,
    I would dearly love to get daily activity from BoA and Capital One credit cards. Dan's site gives a GnuCash link that notes BoA NY and BoA CA possibly working for other states. (I am in WA but have also entered CA with no impact on log on.) After reading all 3 pages of your blog twice and everything on Dan's site, I see lots of reference to BoA but no mention of solution except possibility of Yodlee, pay fees, or open a "free" checking account. Is there any experience of satisfaction or issues with Yodlee?
    Re Capital One, I can get daily activity manually but my OFX files say "The user cannot signon because he or she entered an invalid user ID or password". And Dan's links show "CapitalOne (Banking)" and "fi_name" = "Capital One Bank (Not Credit Cards)". Does this indicate that I'm wasting my time trying to hack a PocketSense configuration and must continue to log into the bank site?
    Thanks, Dudley

    ReplyDelete
  29. I've decided that my BoA Mastercard transactions aren't worth signing up for another checking account just to be able to download the transactions daily, so I logon to the website once per month and download the statement into Money, as I always have. I only use this card when Discover isn't accepted anyways.

    Sorry I can't offer help regarding CapitalOne.

    -Dan

    ReplyDelete
  30. I don't use CapitalOne. A quick search produced the following on the GNUCash site:

    http://wiki.gnucash.org/wiki/OFX_Direct_Connect_Bank_Settings

    "Note on Capital One - credit card: Customer service states that Capital One does not support Direct Connect services for credit card accounts (as of 6/15/09). However, a manual download of transactions from the web browser online access in ofx format..."

    ReplyDelete
  31. Robert,
    I noticed that there is a folder called “xfr” in the Pocketsense directory which contains a copy of the ofx file generated. Any way to give me the option to keep a copy of each ofx file whenever I run "getdata.py" for history?

    If possible, could this same file include a remarks line similar to what I see on the command screen when I run "getdata.py" in the interactive mode. I’m looking for the mutual fund/stock NAME as well as the symbol, price, date and time all on one line, so I may manually update the prices for a certain date if need be.
    Sal

    ReplyDelete
  32. Sal: One of my next "to dos" is to write out a .htm file with a summary of the most recent quotes. When doing so, I will be writing more data than is contained in the current scripts. When I do this, I could also add a "quotes history" file that gets appended each time with the same info.

    Is there another reason to keep a copy of each file? I don't recall seeing any OFX files with stand-alone comment lines, and I didn't see any mention of it doing a quick search of the OFX Standard.

    ReplyDelete
  33. Hi Robert,
    MSMoney crashed again last night during the upload. I didn't have much time but individually uploaded each of the 8 Van accounts that I have. All worked except the Roth account that has no activity since 12/30/09. I tried setting the interval to 120 and still crashed. If it's relevant to you, when I then went back to Vanguard and downloaded, they give me activity back to 02/09. If it would be helpful to you, I could send this problem OFX file or post something from it.

    And a question please. I remember a discussion about getting the % Change column in Portfolio Manager working again. Solution wasn't clear to me. I almost thought the idea was to add tickers to the dummy account but those that I have in there are giving me quotes but no % Change.
    Thanks, Dudley

    ReplyDelete
  34. Dudley: I looked at the resulting OFX files for my own accounts for both a 30 day interval and a 5 day window. One was a Roth, and IIRC... was the one that originally caused an issue. There was nothing obviously different between the two. An easy fix may be to use a window of more than 1-year (~400 days) for Vanguard accounts. That would at least capture an EOY transaction. My accounts get at least one transaction per month, so the minimum window of 30 days may have been "just barely working" before I upped the defaultInterval value to 90 days. Luck, if you will.

    To go any further, I'd need to see an OFX statement from another investment firm with "zero transactions in the period", and that that *is not* causing Money to crash. I only use Vanguard myself. If anyone has one to share, shoot it to pocketsense1/at\gmail*c0m. Please edit and rename account details to placeholders (e.g., "*account#*" in place of the actual value).

    ReplyDelete
  35. Robert, OK, nothing else I can contribute on Vanguard.

    Can you clarify how to get the %Change column of Portfolio Manager working?
    Dudley

    ReplyDelete
  36. Robert: I was looking for an easy-to-read text which would provide the mutual fund/stock NAME as well as the symbol, price, date and time all on one line. If I run “getdata.py” before market close, I could re-run it later and use this list to manually update Money for the end-of-day prices. I thought maybe you could easily convert the OFX file into an add’l text file as part of the "getdata.py" execution, or include it as part of the OFX file itself. I do not know the OFX standard; I was simply looking at it from a user perspective. Of course this is merely a temporary solution.

    The long term solution would be the ability to run “getdata.py” more than once per day, and the ability to run “getdata.py” for past dates. Unless I am mis-understanding you, I don’t think a “quote history” is necessary since the prices should be captured within Money when you run “getdata.py”, and that would be the quote history. Sal

    ReplyDelete
  37. Dudley: %change is not updated within Money via the OFX scripts.

    Sal: I think the best approach would be for me to create a quotehistory.csv file for the quote history. A user can then open that file in Excel and sort/view to their liking. The file would contain every quote gathered by Getdata, and would append new results to old. As my next update will be related to quotes and the creation of a htm file, I can very easily dump the same data to a history file in the form of a CSV.

    Additionally, I will add a user-defined flag to sites.dat to disable "deleting" previous quote*.ofx files.

    ReplyDelete
  38. Dudley: I tested my Vanguard accounts last night to try to get a "crash", and couldn't. Not sure why, but it worked with no transactions. If you have a chance to clean up your OFX file that crashes money (i.e., remove account info), I'd be interested to take a look. It bothers me that *anything* could crash Money during the import, so I want to give it a hard look.

    ReplyDelete
  39. Robert: Sounds good.
    Thanks, Sal

    ReplyDelete
  40. Robert, any break through on the Canadian Mutual Funds quote download?

    -Sam

    ReplyDelete
  41. Sam: I think so, but I haven't implemented it within the scripts yet. It will take me a bit to write & test, so I couldn't do it in the few spare moments I've had recently. Initial tests looked promising. I've only recently had time to even look at the scripts again, as it's been a *very* busy summer. Won't be much longer... and I hope to implement all of the quote-related updates at once.

    ReplyDelete
  42. YAY!...Thanks Robert. Most of us can now safely look forward to the Sunset. Even after the Sunset, it would be bright & sunny due to your utility.

    - Sam

    ReplyDelete
  43. (My previous comment didn't appear to work, so I'm shortening it and trying to post as anon):

    What sort of license is this Python code released under? Could I port it to another language and use it in a closed source program if I attributed you?

    Many thanks,

    Taylor

    ReplyDelete
  44. Hi Taylor: As of this time, the code is not licensed for distribution or repackaging. It falls under standard software Copyright protection rules. Some of the code originated a few years back, and a portion of that was edited and put up on http://thefinancebuff.com/. I have since performed extensive modifications myself. By keeping the source open, it is akin to a research paper, with cited references. Generally, any original work may be cited in other original works. However, if the original work is incorporated into another and packaged "for monetary gain", then written authorization from the original author(s) is generally required. That's my understanding, at least.

    That said, if a person wrote their own (unique) linked program (Java, c++, etc.) and followed the general logic of the scripts, I can't imagine anyone caring much, and it's probably legal (but I'm not a lawyer). A drawback would be that if someone began "selling" code derived from the work, then folks like me would stop working issues in our spare time (programming is easy, but working out connection/ofx issues can be time consuming). That's the spirit of open software (and literature), gathering the collective knowledge into an open "library". Honestly, I could translate the entire script logic into a nice compiled GUI interface in a few days, but I haven't really felt that it was the best path forward. Would folks prefer a GUI interface, but one where they can't see & understand how it works behind the scenes?

    ReplyDelete
  45. Although some users express a dislike of Python (although I don't know why), I feel that keeping the package in it's current format is best. Yes, the user can invest some time upfront in setting up the package for their circumstances, but atleast until the desktop version of Quicken ceases to exist, we should be able to enjoy the scripts on a daily basis with little subsequent intervention. It also allows users to modify things to suit their needs, although the flexibility that Bobby has coded in already probably eliminates that need. Even with a GUI, the user needs to invest time in setting up their accounts.

    ReplyDelete
  46. After running 'getdata' the following note appears. The data seems to incorporate into Money correctly. See below:


    Sending quotes to Money...
    '.\xfr\TD' is not recognized as an internal or external command,
    operable program or batch file.
    '.\xfr\CHARLES' is not recognized as an internal or external command,
    operable program or batch file.

    ReplyDelete
  47. First off, thank you so much for providing this great tool. I've looked at other software products to replace Money to track my investments, but haven't found one I like.

    I followed your very clear directions without a problem. I setup everything in the sites.dat file. I ran setup, I tested each account and matched them up in my Money file. No problems. I ran Getdata and it ran great. The only problem is, no new data is being updated in Money, including quotes.

    I went into the XFR folder and opened the individual folders. There seems to be minimal conent in each, and certainly no financial data. My Vanguard OFX file for instance show the account numbers, but that's it.

    I must be missing something small. I don't get any errors. I don't think this is a Money problem as the ofx files don't seem to have all the data.

    I've been trying different things for a week but can't seem to figure it out. Hate to bother people on here, but perhaps someone else ran into this problem and knows what to do?

    Thanks! Matt

    ReplyDelete
  48. Matt and gun: First, verify your entries in the sites.dat file. Second, run setup and verify that the account#, username, etc. are correct for each entry. Also, verify that the defaultInterval value is long enough to catch some transactions.

    Gun: Check the .\xfr subdirectory and see what files are there. There should be some files with naming like: "AccountName+datetime.ofx". Definitely something odd with the error, since you don't show a date/time in the file names.

    ReplyDelete
  49. This comment has been removed by the author.

    ReplyDelete
  50. Thanks for the prompt reply

    The .\xfr folder has files with all accounts listed
    TD AMERITRADE20100904071229.ofx
    CHARLES SCHWAB20100904071235.ofx

    All accounts test successfully.
    The error message shows in the command (script)window after getdata

    ReplyDelete
  51. Gun: Download the latest beta version at:

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    The issue was caused by using spaces in your site names ("TD AMERITRADE" rather than "TD_AMERITRADE"). I hadn't accounted for that possibility (I don't use spaces). Others may do the same, so I updated to (hopefully) handle spaces. Let me know either way.

    Note: If it doesn't work, delete your accounts for these sites in Setup, rename the SiteNames in sites.dat (without spaces), and then recreate your accounts (Setup) using the new names.

    ReplyDelete
  52. Robert - Thank you for the quick reply. I figured it out. I didn't realize that when setting up the accounts, that you needed to do one for each individual account within a brokerage. I assumed using the login and password was all I needed. I was not using an account number. Works great now, thank you!

    Now I just need to figure out how to import from Janus.

    Thanks again, Matt

    ReplyDelete
  53. I unzipped the new script, but I only copied the new 'Getdata' file.
    A few things have changed: After getdata is finished downloading
    the command screen disappears (it used to stay open) and immediately
    5 tabed Money popups (one for each account)appear, asking whether to
    start Money now. Only 1 popup used to appear. Now I need to click
    5 times to answer the popup.
    I thought you might want to know.

    ReplyDelete
  54. Gun: I updated the beta again this morning, to force sequential loading of the files one at a time. Grab the latest, and I recommend updating all files if you didn't previously have the beta code.

    The getdata window only stays open if there is an error during the uploads.

    ReplyDelete
  55. Thanks, Robert, I d/l the files.
    Please excuse my ignorance, how do i update without losing my data?

    ReplyDelete
  56. Copy all new files to the script directory, and overwrite the existing versions. Your personal data will not be overwritten.

    ReplyDelete
  57. Thanks again, everything works flawlessly.
    You saved my life, I have data in MS Money from 1987, always used the web version.When they discontinued the broker uploads I did it manually till recently when I found out everything except the useless Sunset version was going to be discontinued.
    Luckyly I ran into your blog and through it found out that older versions of Money software (Money 2006 in my case) was still available cheap on ebay and you could make it work by changing the date on your computer. And now all that was going to be useless in january.
    Thanks again for all your help and to the people that also had some input Gun

    ReplyDelete
  58. This looks like a great addition, but the devil is in the details. My first site worked (AT&T Credit Card), but my second site (USAA) setup did not.

    Once I got the BANKID entered properly (I needed to add some hyphens to the number), PocketSense was able to connect and "thought" it had downloaded OK. But the OFX file reads:

    OFXAdapter: Failed to parse request: Unable to parse a composite field "OFXRequest.OFXRequest": postTag "/OFX" not found.
    Remaining unparsed data:
    BANKMSGSRQV1>



    (and there are more lines, but no data).

    Any thoughts?

    ReplyDelete
  59. Ron: Verify your sites.dat entry first, and make sure all the fields are correct, including AcctType (CCSTMT, INVSTMT, or BASTMT). Review your account entries in Setup, including the type (CHECKING, SAVINGS, MONEYMRKT, or CREDITLINE).

    ReplyDelete
  60. Robert I just started following the blog. Can you re-post instructions on how to install pocket sense for those that recently starting following. Thanks

    ReplyDelete
  61. See info and instructions on the web site: http://sites.google.com/site/pocketsense/home/msmoneyfixp1

    ReplyDelete
  62. Robert,

    I'm Re-reviewing it again.

    In Setup, it is a checking account, and I copy/pasted the account number from the web site.

    In sites.dat, it sure seems to be setup correctly.

    I copy/pasted the relevant data from OFX Home and from the fedwire participant directory for the BankID. The account type line reads:

    AcctType : BASTMT #bank

    In the "remaining unparsed data" section of the downloaded ofx file, it does show CHECKING so I assume that setup got that correct, too.

    ReplyDelete
  63. Ron: Send me a copy of the ofx file at pocketsense1/at\gmail*c0m. Please edit and rename the account# to a placeholder (e.g., "*account#*" in place of the actual value).

    ReplyDelete
  64. Thank you very much, Robert. The file is on its way.

    ReplyDelete
  65. Let me first say Robert, you are awesome! Thanks so much for putting this together.

    Is there any way to disable the "Import Complete" dialog in Money when importing with pocketsense? I have 12 ofx accounts, so it would be great if I didn't have to click ok a bunch of times everytime I update. Just wondering.

    Thanks.

    ReplyDelete
  66. Robert please consider,

    Putting this great feature into GUI with fields performated and drop down selections. Otherwise the install and field formatting is very advanced for most Money users to achieve. This is a great tool but the implementation for the non advanced user is very diffcult.

    Thanks for your work and consideration
    Alan

    ReplyDelete
  67. Robert,

    Well, I finally got the USAA stuff working. I got it properly set up by comparing information from the OFX RQ files that Microsoft Money generates, and making sure things were identical. Apparently, with USAA, leading 0's in an account number are really necessary. And there were a few other gotcha's. In any event, it's working fine.

    It seems to be the case that I will need a different entry in "sites" for every account; unlike Money where a single FI entry would download all of the accounts at that FI, and even find new one's.

    Is that truly the case? Or am I missing something?

    Thanks.

    ReplyDelete
  68. Never mind my last comment about the multiple entries in "sites". I see, both by experimentation and also by !RTFM! that only a single entry is required in sites.dat for each type of account (investment, credit, banking).

    ReplyDelete
  69. Blake: With regard to the "import complete" dialog, I was not able to find a workaround. I actually played with this early on, but the only hope seemed to be to include multiple sites/accounts in a single OFX file. That turned out to be "not so simple", and maybe impossible, so I dropped it without any success. Granted, I didn't spend much time trying...

    ReplyDelete
  70. @Robert: It is something we can all live with for now. Your method of combining all of the data into a single OFX, was just what I was thinking. Then there would be a single 'import complete' dialog to acknowledge I assume. Thanks for the update Robert. When/If ever you get back around to this, it will be appreciated.

    After doing the install, I have a few ideas about streamlining the update process from a user level. Once I have all of that together, I will post.

    ReplyDelete
  71. Since an OFX download has to be paired with a Money account, a combined OFX file doesn't seem feasible to me. Online downloads thru the MSN servers were even seperate OFX files I think.

    Dan

    ReplyDelete
  72. I actually read through parts of the OFX Standard at one point, but did not find any support for "multiple accounts in a single OFX statement". That was the point when I moved on to something else.

    ReplyDelete
  73. Robert,

    OFX files can contain multiple accounts within the same file, so long as they are at the same FI. If you look at the files that Money sends and receives, you can see what's going on. It's covered in the OFX spec under Account Information. It seems that Money first asks the FI for Account Information -- that information is returned, and then Money sends an OFX request for all of the accounts that were returned for that FI.

    ReplyDelete
  74. Does anybody get problem with citi credit cards? I cannot download the transactions. I checked the ofx file. no transactions and the ending balance is not correct. Anyone got same problems?

    BTW. This is a great program. Thanks

    ReplyDelete
  75. Just had a problem this evening. All of a sudden my getdata script would grab my ofx transactions, but then would not send them to Money. This started happening on two of my computers simultaneously. Turns out when I turned quotes off, the imports go just fine. So then I turned quotes on, opened the debugger, and this is what I get. Not sure what this means. Is the quotes server not available and the code aborted?

    Getting security and fund quotes...
    Traceback (most recent call last):
    File "C:\PROGRA~1\Python26\Lib\SITE-P~1\PYTHON~1\pywin\framework\scriptutils.py", line 325, in RunScript
    exec codeObject in __main__.__dict__
    File "C:\Program Files\PocketSense\ofxpy_pocketsense\Getdata.py", line 72, in
    status, ofxFile = quotes.getQuotes()
    File "C:\Program Files\PocketSense\ofxpy_pocketsense\quotes.py", line 261, in getQuotes
    sec.getQuote()
    File "C:\Program Files\PocketSense\ofxpy_pocketsense\quotes.py", line 88, in getQuote
    if quote[1] == '0.00' or quote[2] == 'N/A':
    IndexError: list index out of range
    [Dbg]>>> !!! Already pumping - outa here
    >>>

    ReplyDelete
  76. TO ALL HAVING PROBLEMS DOWNLOADING QUOTES:

    Drop the ^DJI line from your sites.dat file and all should be okay for now. It looks like a yahoo problem at this time. I'm sure Robert will scope it out soon.

    Ray

    ReplyDelete
  77. Robert,

    Whereas Yahoo usually returns some type of data for each symbol, when you use ^DJI for the indistrials average, Yahoo returns nothing, not even a placeholder.

    Ray

    ReplyDelete
  78. Thanks Ray. Yup, the Yahoo api isn't recognizing their own ^DJI symbol today (for some reason), and gives a new error not previously encountered. I patched the code to handle it. Beta snapshot updated.

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  79. I installed the latest beta code, and it appears to handle the exception properly. No quotes get imported (makes sense), but the code now properly sends the OFX data off to Money, instead of bombing. Nice work Robert. Thanks.

    ReplyDelete
  80. Nice detective work on your part too Blake!

    I had gotten the same error yesterday and logged into the Blog to paste the results into a comment and saw that you had already narrowed it down to the Dow Jones quote. I'll turn quotes back on and try out the Beta :)

    Dan

    ReplyDelete
  81. Thanks all.

    Would it be possible to just discard the error record rather than discarding the entire quotes ofx?

    ReplyDelete
  82. The script records/keeps valid quotes and account statements. It tries to skip unrecognized ticker symbols and failed connections. The latest beta handles this better than previous versions.

    ReplyDelete
  83. Robert,

    As an FYI, I tried out the DJI symbol in the hleOfxQuotes java app and it simply skips DJI and provides quotes for all of the other symbols, rather than bombing out, so we know it is possible.

    Dan

    ReplyDelete
  84. The problem with Yahoo quotes via csv conversion that we are using here and hleOfxQuotes is two fold.
    If you go after a single quote as Robert does, Yahoo returns a message about missing symbol. If you go after more than one quote at a time, Yahoo returns nothing, not even an error message.
    The problem is Yahoo ignoring the symbol ^DJI. I am surprised it has not been fixed yet.

    Ray

    ReplyDelete
  85. And the following from Yahoo Finance:

    Quote....
    Because of new restrictions implemented by the Dow Jones Index, Yahoo! is no longer able to provide Dow Jones Index data for download in CSV format. We apologize for any inconvenience.
    EndQuote....

    Ray

    ReplyDelete
  86. I have just commented out ^DJI until something else can be done.

    BTW, I double clicked the hleOfxQuotes java saved quotes.ofx to load the into money an got a invalid file format error. Is there any secret to getting Money to accept the hleOfx format?

    ReplyDelete
  87. I took some time to address the quote issue.

    1. The ca.finance.yahoo site is correctly reporting ^DJI results (whereas the US finance.yahoo site is not). I decided to add the ability to use an optional Yahoo server within the scripts.

    2. The scripts will no longer skip valid quotes when one or more fails. Successful quotes will load to Money as usual.

    3. A QuoteHistory.csv file is now created in the .\xfr directory, and will append new quote results to old.

    Beta snapshot updated:
    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    To use the alternate Canadian Yahoo quote site, define the following parameter in SITES.DAT:
    YahooURL: http://ca.finance.yahoo.com

    (I put this right above the stocks section in sites.template for new installs)

    ReplyDelete
  88. Hello Robert!

    I actually got on here today looking for a solution to an error I've been getting, "IndexError: list index out of range", having spent several mornings this week trying to solve, and saw Blake's earlier post on the subject. Can't tell you how happy I am that you all have solved this - guess I just did!

    I decided to post anyway just to thank you, as so many others have already, for all the work you've put in on this - not only the design and writing of the code/scripts, but also the time you're spending giving patient and complete answers and feedback to all of us, and you're willingness to do even more, and make this tool even better! I know this is going to sound a little over the top, and fru-fru (is that how you say/write that?!?!?), but your selfless contributions are truly inspiring and faith restoring, in humanity that is! I know, I know, but there I said it! Thanks so much!

    Steve

    ReplyDelete
  89. 3. A QuoteHistory.csv file is now created in the .\xfr directory, and will append new quote results to old.

    Can this be toggled on/off? If everything is working, no need to create the history file and let it grow.

    I have tested it with US and CA sources and they work for me!

    BTW, when one uses the test option for quotes in setup it wants to load the results to money. Anyway to tell the test mode to create the ofx but not try to load it to money -- or have a toggle?

    Thank you for your work on this, great set of scripts!

    ReplyDelete
  90. Beta snapshot updated again:

    1. Added a flag to sites.dat for enabling/disabling QuoteHistory.csv:

    #Save quote history to QuoteHistory.csv?
    #-----------------------------------------
    SaveQuoteHistory: No

    2. Testing an account will now ask whether you want to send the results to Money.

    ReplyDelete
  91. Please check the contents of the beta. I go to

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense

    and get a page not found. The zip file there does not appear to be the beta.

    ReplyDelete
  92. Mike: You somehow omitted the rest of the link:

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  93. Robert,

    Indeed I did. Somehow I ended up on a blog page where the url was truncated. My apologies.

    Thanks

    Mike

    ReplyDelete
  94. Works fantastic for me!

    Thanks again

    Mike

    ReplyDelete
  95. You are on a roll Robert... I would like to echo Steve's sentiments from earlier today!

    The latest beta with the quote fixes is working great. I noticed that the dialog at the end of the script to review and press enter only shows up if there was an error. Nice touch.

    -Blake

    ReplyDelete
  96. I have a small tweak I'm wondering if you could include (time permitting). Could the entry point of the script be automated such that no user intervention is required to do a typical update?

    Suggestion: When executing the script, ask for password input first, if turned on. Then display "Press any key for Interactive Mode". After 3 or so seconds of no input the script would just proceed with downloading transactions. If a key is pressed, the normal "Download Transactions? (Y/N/I)" would be presented.

    Of course, an option to turn this features on/off could be added to the config file.

    Thanks, Blake

    ReplyDelete
  97. Hi Folks,

    Hey guys, great effort that I just stumbled onto a few days ago. I really appreciate what's been done here. I've successfully set up my Schwab, Wachovia Bank, and Prudential Retirement accounts, so they are working great.

    For some reason though I've had had no luck getting my E*Trade brokerage account going. The Setup.py test ALWAYS comes back with "An ERROR occurred when connecting to ofx.etrade.com". No ofx file gets created in the xfr folder.

    I decided try out both the ofxget java utility and the original Steve Dunham ofx.py script with the TFB revisions, and both of them work perfectly with E*Trade. Here are the site values I used successfully in the Dunham script:

    fiorg = etrade.com
    fid = fldProv_mProvBankId
    url = https://ofx.etrade.com/cgi-ofx/etradeofx

    Seeing the differences in the "doQuery" routine in Dunham's ofx.py makes me suspect the Pocketsense routine is less forgiving. I'm a total novice with Python, and I don't have a compiler or else I would try some tweaking around there.

    If anybody has any clues or ideas for what's going on I would sure appreciate it.

    Thanks, Les

    ReplyDelete
  98. Just guessing here...

    Try fiorg = fldProv_mId

    See http://www.ofxhome.com/index.php/institution/view/446

    -Blake

    ReplyDelete
  99. Thanks very much for your suggestion Blake, but I should have mentioned that I had ruled out the necessity for the fiorg value that you see in the ofx directories.

    But wouldn't you know it, after days of beating my head against the wall all it took was for me to post a plea for help this morning to finally figure it out myself. :)

    I was reading back through the messages here (yet again), and Robert's message a couple of days ago about a problem with spaces in the site name jumped out at me. I had downloaded the beta and still had the problem, but I just tried getting rid of the asterisk in the name -- made it ETRADE instead of E*TRADE -- and bingo, now it works! Hurray!!!

    Thanks again,

    -Les

    ReplyDelete
  100. This seems to be working fabulously, although I am waiting for the beta to be finalized so as to obviate the problem with spaces in names.

    Two suggestions:

    1. Some way of sorting the list of accounts
    2. Some way of tagging the individual accounts to make it easier to tell what is what. I have 15-20 accounts at six different sites.

    One question: In Setup.py, option "1" on the main menu is "Add or Modify Account". I've only been able to "Add"; I don't see a method to "Modify". Am I missing something?

    Thanks.
    -- Ron

    ReplyDelete
  101. Ron,

    Option 1 modifies an existing entry only when the account number you enter matches one already in the list for a given site. If you enter a different account number it adds a new account for that site.

    It's odd that I didn't see a problem even though all three of my working accounts had spaces in the site name. I'll have to go back and replace those with underscores to be safe, but the asterisk in E*Trade was my only issue.

    -Les

    ReplyDelete
  102. Ron,

    FYI, I'm not sure of Roberts release schedule.. but the last series of beta releases have been stable IMHO.

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    -Blake

    ReplyDelete
  103. Thanks everyone for the suggestions and feedback.

    Les: Your comment reminded me that I never discussed valid site name characters. Characters that are valid in Windows filenames are valid in sitenames. The following characters are not allowed in file names:

    \ / : * ? " < > |

    Based on your experience, I went ahead and added a little code to delete illegal chars for the OFX file names. It's probably best not to use the illegal file chars, but this addition should support (just about) any sitename.

    ReplyDelete
  104. A-ha! The ol' illegal character thing got me again!

    While I'm geared up for this, let me mention a gotcha I discovered regarding switching to Microsoft Money Sunset edition that I haven't seen mentioned anywhere else.

    I was getting the NAG message in Money Plus Deluxe indicating that I only had a few days left of online services, so being unsure of what the program would do when the time ran out I went ahead and downloaded Sunset Edition and installed it.

    When I opened my Money data file with the Sunset Edition I saw the four accounts that I had always downloaded with Money's online services were still showing as part of a grayed-out download group with no way to remove them from that list.

    When I tried running the PocketSense scripts I encountered that problem described elsewhere of Money not updating if the account was set up to download through Money. I had to re-install Money Plus Deluxe and re-activate it just so that I could turn off the online update for each account. After I did that I ran the scripts and Money updated as it should have.

    The bottom line is to be sure and turn off the online updating for accounts before moving your data file over to Sunset Edition.

    -Les

    ReplyDelete
  105. Thanks for the feedback on the Sunset version Les. The scripts should have still worked with the "online" accounts, but it may not have properly match downloaded transactions w/ existing ones. That's what my initial runs did (before disabling online transactions in Money). I think it's best to disable online transactions before using the scripts for "official" downloads... apparently for more than one reason.

    I think I'll add a blurb about this on the website re: the Sunset Edition.

    ReplyDelete
  106. Now I'm questioning my recollection about the way the scripts behaved. I do remember each Setup.py test opening the import dialog box in Money, but then nothing like that happened when I ran Getdata.py. At any rate, having those entries grayed-out in Sunset Edition was bugging me enough to make it worth it to reinstall the online services enabled version just to shut them off.

    The irony to reinstalling and reactivating Money Plus Deluxe is that it extended my online services cut-off date to January 2011. Forget that, now that I've got E*Trade working with these scripts I've cut the cord.

    -Les

    ReplyDelete
  107. Richard, Using your beta version, I get the following when I run “getdata.py”:

    PocketSense OFX Download Scripts for Money, Ver: 12-SEP-2010 BETA

    Download transactions? (Y/N/I=Interactive) [Y] i
    No accounts have been configured. Run SETUP.PY to add accounts
    Getting security and fund quotes...
    ** $DJIA : invalid quote response from Yahoo! Finance. SKIPPED...
    ^GSPC 1125.07 9/15/2010 4:59pm
    ** $INX : invalid quote response from Yahoo! Finance. SKIPPED...
    ^IXIC 2301.32 9/15/2010 5:30pm
    ** $COMPX : invalid quote response from Yahoo! Finance. SKIPPED...
    DIS 34.21 9/15/2010 4:00pm
    F 11.87 9/15/2010 4:00pm
    YHOO 14.269 9/15/2010 4:00pm
    JNTFX 59.29 9/15/2010 5:54pm

    No files were downloaded. Verify network connection and try again later.
    Press to continue...

    One or more accounts (or quotes) may not have downloaded correctly.
    Review and press to continue...

    When I press “Enter”, the screen disappears. If I use the previous non-beta version and comment out ^DJI, it works as it had before. What am I doing wrong?
    Thanks, Sal

    ReplyDelete
  108. Sal:

    1. Not sure about the $DJIA, $INX, and $COMPX... those don't look like Yahoo symbols.

    2. You also found a bug in the quote upload logic. Fixed (I think). Beta updated.

    Robert

    ReplyDelete
  109. Option 5 is missing from the setup.py? Everything in new beta words when testing.

    ReplyDelete
  110. I think option 5 has always been missing - I looked back as far as I could go. Don't know why I just noticed that.

    ReplyDelete
  111. I guess while I am asking dumb questions...
    I do not find a getdata.pyc, i.e., the compiled version. Should I?

    Is there a command to force compilation of all py files in a directory? I normally just run setup and list accounts.

    ReplyDelete
  112. Menu option 5 enables when you activate password protection.

    When you run getdata.py, you will get a new getdata.pyc. Python uses a "just in time compiler".

    ReplyDelete
  113. I should have remembered password, my apologies.

    I have searched my entire system and am not finding getdata.pyc.

    ReplyDelete
  114. I just want to download fund quotes. GetData downloads the quotes into the xfr file, but when I attempt to import the file to Money, I get an error message:

    "The file you attempted to import appears to be invalid or contains corrupt data. Please download the data again. If this problem continues, contact your financial institution."

    Do I need to create a "ticker" investment account even though all these funds are already in various investment accounts in Money?

    ReplyDelete
  115. Yes, a "dummy" investment account is required. Money will ask for an account to "match" the import to, so it's best to create an investment account with some obvious name (like "tickers"). There shouldn't be any errors though. If you haven't, I recommend using the latest beta (since there are some quote bug fixes there):

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  116. Regarding getdata.py, my apologies. I wasn't thinking this morning. Root programs do not generate .pyc byte code. Compiled modules are only created for modules that are imported by others (e.g., import ofx.py -> creates ofx.pyc)

    ReplyDelete
  117. I created the dummy "ticker" investment account with all of my mutual funds in it. I downloaded the beta version and moved my "sites.dat" file over to that.

    The setup file works (i.e., when I test the 2 accounts I have in it and the stock quotes, it works).

    At the end it asks me if I want to send the results to Money. I select "yes" but nothing happens.

    when I run Getdata, and select "I" for interactive, it gets the records and stock quotes and creates files in the xfs folder, but when I respond "yes" to whether I want the statements uploaded to Money, the screen disappears and nothing happens in Money.

    I'm close, I think. I just don't know what to try next. When I try to import the quotes file through Money, I get the same error report as above.

    ReplyDelete
  118. Have you installed/tried any other finance software? If so (or not), you need to reinstall Money to "re-associate" ofx files with Money. When you install finance software (Money or most others), they will "associate" ofx files with themselves in the Windows registry. Reinstalling Money will fix it (if this is the culprit).

    Regarding the funds, I may have misunderstood before. If you download statements for an investment account, you do not need to list those funds in sites.dat. Only funds that you *don't* update via an ofx account download need to be explicitly updated from the Yahoo! finance site. You can have them defined in both places. If SaveTickersFirst is set to Yes in sites.dat, then the Yahoo! data is saved to Money first. Otherwise, the account statements go first. Basically, the first one to be imported becomes the official "fund quote value" for the date.

    ReplyDelete
  119. I have a similar issue that may or may not trigger an idea. Open Money, File > Import > Downloaded Statement, navigate to the quotes ofx file and it imports cleanly.

    If I run the java script hleOfxQuotes to generate a quotes.ofx file and then try to import it, above, I get the same error message. I have not pursued it since the pocketsense scripts give me all that is needed. I just thought it interesting that one would load and the other would not.

    ReplyDelete
  120. The latest code has been posted to the live/production version on the sites.google.com/site/pocketsense site.

    ReplyDelete
  121. Robert,
    A couple of questions.
    1 Does your software keep the currency exchange rates updated automatically.
    2 Did the problem of getting Canadian Mutual fund price get solved.
    I have tried out Quicken and if you have bank accounts and investments in both Canada & the US it really doesn't work.
    I am planning to try your scripts.

    ReplyDelete
  122. 1. I have not found a way to automatically update exchange rates. The only way I know is to manually enter the values.

    2. I made progress w/ gathering Canadian fund prices. I haven't implemented it into the scripts yet, but plan to soon. I've been slow to implement some quote related updates (very slow for one in particular... Dan), simply due to a lack of time and not wanting to push out something that only half works. After pushing out the "release" version w/ a bunch of little fixes today, I can now focus on the quotes enhancements.

    ReplyDelete
  123. I'm the person whose quotes won't import into Money. I have not downloaded the Sunset version of Money and will do that.

    Here is my question, I have been using Money Plus. What is the difference between the "Deluxe" sunset edition and the "Home and Business" sunset edition?

    Any reason not to just download the Home and Business version?

    ReplyDelete
  124. You will get a lot of Business Categories and Reports created that you may have no use for and could confuse some people, but they are about the same otherwise. You mentioned trying the Sunset edition of Money for quotes import issues, but that program is the same thing as your Money Plus (except for the activation mechanism).

    -Dan

    ReplyDelete
  125. I think the issue with reinstalling Money relates to my suggestion above (i.e., that the ofx file association may be broken). Can you still install "expired" copies of Money Plus? The Sunset Edition may be the way to go at this point (I haven't tried), but I would delete online connections for each account first (the Money online setup links).

    ReplyDelete
  126. I hadn't seen that Robert. The Sunset reinstall would be a good way to fix the association again, but probably best to uninstall Money first. Backup the .mny file first to be sure it doesn't get lost :)

    -Dan

    ReplyDelete
  127. Has anyone had any luck with Compass recently? Here's the info I have - I can connect w/ my credentials via Money and through the website, but when I try w/ PocketSense the OFX in the xfr folder just says "Application error".


    SiteName : Compass - Strength Locker
    AcctType : BASTMT #bank
    fiorg : CompassBank
    fid : 2201
    url : https://www2.compassweb.com/ofxsecurity/ofx_security_server.dll
    bankid : 113010547
    brokerid :
    appid :
    appver :
    mininterval:

    ReplyDelete
  128. I'm very new to this discussion and have not yet tried Robert's program. But I'm following the discussion because I'm hoping his approach will work so that in January, I can start using it. I found a list of OFX code information and looked at Compass. There are three codes--maybe one of these will help you.

    2204
    12474
    13755

    Let me know if one of these works. Good luck.

    ReplyDelete
  129. Where did you find these at? The thing is that I captured the OFX file from the Money transaction and it has the 2201 fid value. I tried these values and now the OFX from PocketSense says "FI information unable", so I'm guessing that the 2201 is the right value.

    I've got about 15 other accounts all setup and working, it's just Compass that's giving me a problem. I even used a packet sniffer w/ Money to verify the URL, and it is going to https://www2.compassweb.com. Unfortunately, since it's HTTPS, I can't see the actual URL.

    ReplyDelete
  130. I found it while looking for OFX code information for another bank.......it's here:

    https://ofx-prod-filist.intuit.com/qb1800/data/fidir.txt

    ReplyDelete
  131. Re Compass Bank, try:

    fid : 2201
    fi_org: Intelidata
    url : https://www2.compassweb.com/ofxsecurity/ofx_security_server.dll

    Also, verify that the correct type of bank account was chosen during setup (i.e., checking, savings, etc.).

    Reference: http://moneydance.com/synch/moneydance/fi2004.dict

    ReplyDelete
  132. Tried the different fi_org value, but still got Application Error, so changing it didn't make a difference. If I go to that URL in a browser, I get a message "Unable to read post data.", so I know it's a valid URL.

    I also tried it as a savings vs checking (it's a business checking account) just to see if that made a difference and I still got the Application Error message.

    I was able to download the updates in MS Money, so I'm pretty sure my login has OFX access.

    Any other ideas?

    ReplyDelete
  133. I don't have a Compass account, but I did test it with dummy credentials. The response was "Application Error", so that's their response for an unrecognized account (although it's probably the same response for other errors too). The next step would be to delete the account in Setup and re-create it. Note that if the account# has leading zeros, they are required.

    ReplyDelete
  134. I looked into the Money OFX and duplicated the settings (account number, fid, etc.). I'll check and make sure I have OFX access on the account, although I think I do if Money was working ok.

    ReplyDelete
  135. Spoke w/ Compass and I have access (which I figured I did since it worked via Money). They were clueless about the OFX format (just use Quicken!), so not much help. I also tried the account number as the username, but no luck.

    I'd be really curious to see if anyone using PocketSense has it working with Compass - maybe there are some other underlying issues? Like I said, I have about 15 other accounts working against 10 or so other institutions without any problems, so this is just Compass giving me problems.

    ReplyDelete
  136. Re: Compass Bank: One more comment--You list bankid : 113010547 in your settings, but I didn't see that listed anywhere else for Compass (i.e., it's blank in other entries I've seen).

    ReplyDelete
  137. That's the routing number - if you try to create a BASTMT site without that, and then setup the account to test, this is the error that PocketSense shows:

    ** Error: Site COMPASS - STRENGTH LOCKER does not have a (REQUIRED) BANKID value defined.

    I'm assuming this is a required field if you are setting up a bank (and all of my other BASTMT sites all have this filled in.)

    ReplyDelete
  138. I'll have to go back and refresh my memory re: the bankid parameter. I remember looking into it and "forcing entry" for the field for bank accounts, but it's been a while. I do not recall the parameter "having to be" the routing number, but it makes sense that it normally would be. As I don't use ofx w/ bank accounts myself, I can only go by user input and what I read.

    ReplyDelete
  139. Is it ok to "stop using online services" in Money or would that interfere with PocketSence?

    ReplyDelete
  140. Yes, you can "stop using online services." In fact, that is what I would recommend for all your accounts. Then you can migrate to the Sunset edition and get PocketSense working.

    I had problems with account balances showing correctly before I removed all of my online services.

    -Blake

    ReplyDelete
  141. As i understand I could just continue using my Money 2006, or is there any advantage to switch to the Sunset edition?

    ReplyDelete
  142. Re: disabling online updates, it is highly recommended that this be done before using the scripts. The way that Money matches "existing transactions" isn't very accurate when you mix the two.

    I never used Money 2006 (went from 2003 to Plus), so you may want to look at the Money Plus features to compare (pretty sure Sunset is the same as Plus, but haven't switched myself... yet).

    ReplyDelete
  143. Richard,
    Have you ever figured out why the symbol “^DJI” will not pickup the Dow Jones Industrial quote from Yahoo? If I do a manual search it works fine. Can you look at it again?
    Thanks, Sal

    ReplyDelete
  144. Sal: I'm assuming you meant me (Robert). I have no way to address Yahoo's quote engine, but I did add the ability to specify a different Yahoo Finance site in your Sites.dat file. Add the following line to Sites.dat:

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

    I'm also working Google Finance support, but there are some glitches to work out.

    ReplyDelete
  145. Sorry about the name Robert, Old age is quickly setting in.

    Thanks for pointing me to the Canadian Yahoo site, “http://ca.finance.yahoo.com“. It picks up the ^DJI quote and everything else just fine.
    Sal

    ReplyDelete
  146. I seem to have the scripts working. Unfortunately, when importing an OFX file, my fingers slipped and the import became associated with the wrong account. Now those entries always go to the wrong account. How do I reset this?

    Thanks.

    ReplyDelete
  147. With regard to removing a wrongly associated OFX import, I found the answer here: http://money.mvps.org/faq/article/53.aspx

    ReplyDelete
  148. Robert,

    I thought you had written recently that the scripts had been modified so as to accept any legal windows file name character in the FI name.

    I see this as the last modification in my copy of Getdata:

    # 05-Sep-2010*rlc
    # - Updated to support spaces in SiteName values in sites.dat
    # - Don't auto-close command window if any error is detected during download operations

    1. When going to import into Money, the routine chokes on an FI of AT&T. The file is downloaded properly, and, if I double-click it will import into Money, but Getdata won't import it.

    2. The command window closed even though there was both this error, and another that indicated the connection could not be made to an FI.

    3. I don't know if the latter error was in the command window or not; but it was there in Money. The first error was expressed as something about .\XFR\AT being unrecognized. But the command window closed anyway.

    Thank you.

    ReplyDelete
  149. Ron: Did you install all of the updated files from the latest release? I just tested w/ a sitename containing a "&" character, and it worked as expected. BTW, when you say "FI", do you mean "SiteName"? Note that the sitename entry isn't used when connecting to an OFX provider, and is only used to help you organize/recognize your site definitions.

    Also, thanks for the tip to fix a mismatched statement/account pair.

    ReplyDelete
  150. I initially had trouble with USAA accounts, until I figured out:
    1) The UserName should be your USAA member number (not your online login username)
    2) The Password should be your USAA PIN (not your online login password)

    ReplyDelete
  151. Regarding ScoTTrade, here's the OFF info: http://www.ofxhome.com/index.php/institution/view/623
    The FI Broker ID is listed as 'scottsave.com', but it must instead be 'www.scottrade.com', per this thread:
    http://www.ofxhome.com//ofxforum/viewtopic.php?id=47484

    I couldn't updates ScoTTrade using either a blank brokerid or a brokerid of 'scottsave.com' in my sites.dat file. When I updated it to 'www.scottrade.com' (note the 'www'), it worked.

    ReplyDelete
  152. Thank you Thrall. Your input/comments will help others.

    ReplyDelete
  153. Back to USAA - my USAA banking (checking, savings, credit card) accounts are working well, but I can't seem to get my USAA investment account to come down. I suspect that's because it's with 'USAA Investment Management Co.' and not 'USAA Federal Savings Bank' - maybe they have a different OFX profile? Anyone have any luck sorting this one out?

    ReplyDelete
  154. Thrall,

    I'm glad you got the USAA Bank sorted out. With regard to the Investment side, I have no experience there. But I would suggest enabling the "ofx capture" mode of MSMoney, and examining the file so as to get the proper values in Site Config and with your account numbers. That's what I did with some of my entries.

    ReplyDelete
  155. Robert,

    I was wondering why you removed my post. It had more information regarding my AT&T problem, and I didn't think there was anything "bad" there, and I had x'd out my account number. Sorry if I posted bad stuff.

    ReplyDelete
  156. Re: USAA Investments... check here:

    http://moneydance.com/synch/moneydance/fi2004.dict

    ReplyDelete
  157. Ron: I didn't remove anything (?)... so I'm not sure what happened?

    ReplyDelete
  158. Robert,

    I had posted a message that had to do with follow up regarding my problem with AT&T; and when I looked for it today, there was a message that it had been removed. And looking just now, even the message that it had been "removed by the moderator" is gone!

    Very strange.

    In any event. In any event, to repeat, I am able to download the ofx file satisfactorily with Getdata; the problem comes when going to import that data into Money using Getdata.

    Getdata gives these error message after I respond "y" to the Upload AT&T (account number) question:

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


    Clearly, Getdata seems to be parsing the command at the ampersand.

    The file will import OK into Money if I double-click on it, so it would not appear to be a Windows naming issue. Maybe something with Python and ampersands?

    -- Ron

    ReplyDelete
  159. Ron: Just to verify before looking at the & issue: you are running the 19-Sep code?

    http://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2

    ReplyDelete
  160. Robert,

    When I run Getdata, the command window starts with:

    I downloaded this version on Sep 25.

    When I run Getdata, the command window starts with:

    PocketSense OFX Download Scripts for Money, Ver: 19-SEP-2010

    But the last revision note in the script itself is dated a few weeks earlier:

    # 05-Sep-2010*rlc
    # - Updated to support spaces in SiteName values in sites.dat
    # - Don't auto-close command window if any error is detected during download operations

    ReplyDelete
  161. You have the latest. Just wanted to verify that you had installed all of the 19-Sep files (not just Getdata.py). I noticed that I forgot to update a few of the headers with all the updates. I did list most of them in the revision history: https://sites.google.com/site/pocketsense/home/msmoneyfixp1/revisionhistory

    The problem is that I work on this in bits and pieces as I have time. I then forget to update what I've done in the notes.

    ReplyDelete
  162. Robert,

    Just to verify that I have the latest versions, I did install everything that was in the downloaded zip file that included the latest Getdata file; and none of the files in the extracted folder have any earlier dates.

    ReplyDelete
  163. Found the & culprit. The os.system() call does not like the ampersand. Fixed, along with another bug related to the QuoteHistory.csv option and the date format. Beta snapshot updated.

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  164. Thanks, Robert. That will be very helpful.

    By the way, if I want to check the beta, what files from the "regular" version do I copy over. I assume sites.dat. But what do I copy over so I don't have to go through the setup process again? Is that ofx_config.cfg?

    Anything else?

    Thanks.

    ReplyDelete
  165. Ron: The "user files" are sites.dat and ofx_config.cfg. You'll notice that these files are never in the distribution, as they are created/maintained locally. FWIW, this beta isn't really a test code... it's a "bug fix" and should be good to go. I'll update the live snapshot with this version when I hear back from you or anyone that it's working.

    ReplyDelete
  166. Robert,

    I copied those two user files into the "beta" folder, and the scripts worked flawlessly even with the AT&T account.

    Unfortunately, since there were no download or connection errors today, I could not tell if a download error would cause the window to stay open. That was the other problem I was seeing where, with a "connection error" (could not connect to the FI problem), the window did not stay open.

    Thanks again.

    ReplyDelete
  167. Ron: The window didn't stay open because it was a bug in the code (rather than a download error). I generally don't trap bugs, only connection and download/statement related issues.

    ReplyDelete
  168. What might I be doing wrong to keep bank balances from being updated? I'm getting transactions as expected and I'm using the sunset deluxe version, but the bank balances are not being updated when I download transactions.

    ReplyDelete
  169. This happened to me as well, given the same parameters. Turns out, if you don't "stop using online services" for each of your accounts, the balances won't update. Got to 'Manage Online Services' to do this.

    -Blake

    ReplyDelete
  170. So, I did that - I have "Use Automated Online Updates" unchecked under the "Online Update Settings" menu option, and have "Never" selected as the update frequency for all of my accounts. However, bank balances are still not updated after an update. Any ideas?

    ReplyDelete
  171. Follow these instructions to disable online services for each account:

    http://support.microsoft.com/kb/2390720

    See "What do to when your financial institution is disabled" section

    ReplyDelete
  172. Also, check the OFX file in the .\xfr directory and confirm that the bank is actually sending the balance information in the statement. They should be, but I would verify.

    ReplyDelete
  173. Hi Robert, when I download the stock prices using the scripts and import them to Money, the dates are off by 2 days, ie the closing prices on 27th are imported as 29th prices.
    I live in Tokyo so my computer clock is +13 hours vs EST which might be part of the problem I suppose.

    Also, I need to download UK stock prices. Yahoo adds .L to UK tickers. However the stock prices are denominated in pennies when Money is expecting pounds - Money interprets a price of 733 as £733, not £7.33. Is there a fix other than dividing the number of shares held by 100?

    Many thanks for all your efforts......

    ReplyDelete
  174. UPDATE: I changed the way TimeZone can be edited, by making it an option in the sites.dat file.

    Beta updated:

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    The following option is now available in Sites.dat:

    #Timezone offset
    #-----------------------------------------------
    TimeZone: -5EST

    If not defined, the default is -5EST.

    Regarding "pennies" vs "pounds", from my limited research in the past few minutes, it appears that the London Stock Exchange *always* reports in "pennies". Will Money allow this currency? (I only use dollars). If so, that's the preferred "fix". Otherwise, I would consider adding a "conversion" to "pounds"... but only if Money doesn't support "pennies".

    ReplyDelete
  175. Should have also mentioned: the timezone offset option only applies to stock/fund quotes. For online statements, TZ offset is provided by the OFX provider (bank, etc.).

    ReplyDelete
  176. Any tips from others that have gotten USAA accounts working. My text ofx keeps saying USAA can't find the account. I am using my USAA member number as my Username and my PIN, as noted in previous posts. Add the zeros to match my statement, making it a 10 digit code, it doesn't help. Using the dashes in my acct number as on my statement doesn't help either.

    ReplyDelete
  177. I don't have my scripts handy, but what I did was enable MS Money to capture the OFX downloads (instructions for this are in the MSKB); and then copy the account numbers from there (along with the user ID and PW).

    ReplyDelete
  178. Ron,

    Thanks for the tip. I did that, but in the process also restarted... looks like the restart actually fixed the problem.

    Alon

    ReplyDelete
  179. I had a little time since yesterday to look closer at the TimeZone issue. The user-defined TimeZone was not going to be a good "fix". I did that in a hurry, but didn't think it through. I rolled the beta back to the 13-Oct version. When looking at it, I also had a new error coming up with the quote download. This is new, so I'll wait to see if it's a Yahoo issue or something that requires a script update.

    ReplyDelete
  180. I found the error. Yahoo Finance suddenly isn't sending the "name" of index tickers (e.g., "S&P 500" is missing, and Money doesn't like it). I'm using the Canadian Yahoo site, but haven't tested the US version. Will wait to see what happens on their end...

    ReplyDelete
  181. My apologies if this has been covered elsewhere, but is there a way to run getdata and not get the prompt to input y/n/i? I would like it to just default to y

    ReplyDelete
  182. I had this suggestion a while back, but Robert has had bigger fish to fry. It would definitely be nice to streamline the update process as much as possible.

    ---------------

    Could the entry point of the script be automated such that no user intervention is required to do a typical update?

    Suggestion: When executing the script, ask for password input *first* if enabled. Then display "Press any key for Interactive Mode". After 3 or so seconds of no input the script would just proceed with downloading transactions. If a key is pressed, the normal "Download Transactions? (Y/N/I)" would be presented.

    ReplyDelete
  183. Actually, the script started out that way until I requested an Interactive mode option :) I find that I don't use that option much though.

    Did you know you can just hit Enter, rather than hitting the "Y" key? I like Blake's suggestion.

    -Dan

    ReplyDelete
  184. An auto-timeout will work well enough if a password is required, but if there is no password... then there is no "stop point" for an accidental execution of the script. I guess I could make the "auto timeout" a feature that only comes alive when encryption is enabled? My own opinion is that pressing Enter twice isn't too bad an option. Redundant... but simple.

    ReplyDelete
  185. Assuming encryption is turned off..

    I was thinking the user executes the script, which immediately displays "Press any key for Interactive Mode" or something similar. Here you have built-in a stop point. If the user presses a key then you drop down into the "Download Transactions? (Y/N/I)" as already implemented. Otherwise after a bit, the script proceeds to download.

    Assuming encryption is turned on...

    Same as above, but you precede everything by first asking for a password. In this way, no interaction with the script is needed once the password is entered.

    ReplyDelete
  186. And I guess maybe if there is a concern about having a hard stop (without encryption), maybe the delay (or something) could be customizable in the config file.

    ReplyDelete
  187. Thank Robert for this awesome package.
    you saved day/week/years.
    I thought my option was only to go to quicken 2010 . I did go but did not like it. and wished since then if i can find some other way . Glad I found this blog.

    ReplyDelete
  188. As another guy posted (but his post is no longer here - maybe he deleted it?), I'm also experiencing an 'invalid/corrupt' error when downloading from ING. The PocketSense routine seems to log in and grab the account info correctly, but then Money reports:

    "The file you attempted to import appears to be invalid or contains corrupt data. Please download the file again."

    I'm suspecting the root cause is a problem on the ING side, because PocketSense has otherwise worked flawlessly and I was never able to download statements from ING using MS Money in the past. Posting this for posterity in case someone else figures out a solution.

    SiteName : ING
    AcctType : BASTMT #bank
    fiorg : ING DIRECT
    fid : 031176110
    url : https://ofx.ingdirect.com/OFX/ofx.html
    bankid : 031176110
    brokerid :
    appid :
    appver :
    mininterval:7

    ReplyDelete
  189. Thrall,

    Just to be sure, your ING account is a savings account, right? The settings would be different on the Investment account side of the business...

    -Dan

    ReplyDelete
  190. Hi Dan - yes, my ING account is a savings account. I double-checked the institution info at ofxhome.com, and even went through the steps in this comment:
    http://www.ofxhome.com//ofxforum/viewtopic.php?id=47508
    ... to create a Personal Finance Access Code to use instead of my PIN. However, still no luck. Curious if anyone's gotten ING to work correctly?

    ReplyDelete
  191. I started using PocketSense a couple weeks ago, when my online quotes update stopped working. The OFX import for my Schwab accounts worked fine. Tried to do an update tonight, and while the OFX was imported successfully, the Portfolio Manager and Account Summary pages don't have updated quotes. The Account Register has the correct account value, so the quotes have been imported (verified by looking at the "Update prices/manually" dialog.

    The comment by Anonymous on Oct 18 2010 mentions a KB article describing disabling online services for an account. But by that, I don't seem to have online services enabled (I manually updated the Schwab info before, just used automatic online quotes, which are now disabled).

    I'm using Money Plus Premium. Would installing the Sunset edition get Portfolio Manager showing the latest imported prices again? I'm hesitant to install Sunset until I understand why the Portfolio Manager and Account Summary pages updated last week with PocketSense, but don't this week.

    Thanks for any advice from anyone.

    ReplyDelete
  192. I just verified that my Schwab updates are working just fine. You are importing statements, so the values are for COB the day before. It sounds like you were using MSN symbols for quoting your investments, rather than Schwab's symbols. Try clearing one symbol out and see if after the next Schwab update, you aren't prompted regarding the symbol.

    -Dan

    ReplyDelete
  193. No, not a symbol problem, or the Account Register value wouldn't have been correct. Turns out it's a known problem. Looked this morning, and the Portfolio Manager and Account Summary pages have correct values.

    I checked one of the Schwab OFX downloads, which were pulled down at 2010/11/12 9:47pm PST. I see a global DTSERVER and DTASOF of 20101113120000, which sounds like noon on the 13th, but the individual quotes have DTPRICEASOF 20101112210000, or 9 pm on the 12th.

    The PocketSense script's author commented back on August 15, 2010 9:34 AM that Money uses the DTASOF time for all quotes, not the DTPRICEASOF. So last night, the new quotes were in the future. Now they're not, and all screens are up-to-date.

    Sounds like something I could try to fix playing around in the scrubber. If DTASOF is after the current date, just force it back. I could use learning a little Python, anyway.

    ReplyDelete
  194. Phil,

    You've stumbled upon a known issue with the quote script. I alluded to the issue a few months back in one of my comments, but basically it is that the current quote script uses midnight "tomorrow" for the DTASOF time. I didn't implement that particular logic myself, and didn't really care for it.

    My "to do list" includes "use the actual date/time for each quote", but I'm not sure it's possible to do "all in one file" with the Money OFX import logic. At a minimum, I want to set the DTASOF for the statement to the first actual quote date/time in the sequence.

    ReplyDelete
  195. This isn't the problem with the quote script, since it's for a downloaded OFX. It's probably a time zone thing, with Schwab using an EST DTASOF with me in PST. I looked at the pre-scrubbed OFX, and Schwab is just using YYYYMMDD, with the local date on the server side, for DTSERVER, DTASOF, DTSTART, and some DTPRICEASOF (for some unpriced securities in bankruptcy). Setting DTASOF to the first DTPRICEASOF wouldn't work if an unpriced security was that first one. Maybe best to just back DTASOF by 1 day if it's YYYYMMDD for tomorrow.

    ReplyDelete
  196. BTW, I just tried changing ofx.py to include the timezone offset in the uploaded query's DTCLIENT and DTASOF (e.g. 20101113232831[-8]) to see if the Schwab OFX server would key off that in any way. But no, no change in the response - I still see references to 20101114 (tomorrow, at least still here in PST). So scrubbing times to replace YYYYMMDD references to tomorrow to be references to tonight is likely the way to go instead.

    ReplyDelete
  197. Phil: This may have been caused by the time scrubber itself, depending on whether Schwab is sending a time offset. When implementing that code, I intended to *not* adjust time values if an offset was provided by the provider. Looking at it just now, I had omitted that check. I updated, and posted to the beta snapshot. Give it a try...

    http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
  198. Last comment for this post... otherwise, it will go to another page (200 posts per page). Comments continued at:

    http://pocketsense.blogspot.com/2010/11/replacing-microsoft-money-continued-nov.html

    ReplyDelete