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
Subscribe to:
Post Comments (Atom)
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.
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
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!
ReplyDeleteMS 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
Robert, sorry to bug you but could you please confirm if this is how the utility works:
ReplyDeleteOn 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
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.
ReplyDeleteThere'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.
My tests on the new scrubber.py look good. I want to watch it after monthly close.
ReplyDeleteThank you for the update.
Mike
why can't i post? "Title"
ReplyDeleteI have problems with TD America
ReplyDeleteWhen I run setup.py I find the following error in the ofx file
Gun
TITLE>Error 400--Bad Request/TITLE
gun: Do you mean TD Ameritrade, and when you run "Getdata.py"?
ReplyDeleteFirst, verify your sites.dat entry against the entry on ofxhome.com, and verify the account data in Setup.
This comment has been removed by the author.
ReplyDeleteStupid me!!!!
ReplyDeletein 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
Robert said...
ReplyDeletegun: I'm assuming you verified your Setup entry for Ameritrade also? As a first step, post your sites.dat entry for Ameritrade here.
Thanks again for your patience with me.
ReplyDeleteFound 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
Glad you figured it out gun.
ReplyDeleteYou 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
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
ReplyDeleteI 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?
ReplyDeleteCopy the ofx_config.cfg and sites.dat files.
ReplyDeleteYep, that did it, thanks
ReplyDeleteAnd 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?
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.
ReplyDeleteThanks 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?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteIf 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.
ReplyDeleteI 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.
ReplyDeleteI believe that USDCAD=X, would be correct for USD to CAD (if that's what you want)
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.
ReplyDeleteYes 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
ReplyDeleteI 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.
ReplyDeleteHi Robert;
ReplyDeleteMany, 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
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!
ReplyDeleteI'm going to rename "Exit" to "Save and Exit".
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.
ReplyDeleteThanks, Dudley
For Robert or Dan,
ReplyDeleteI 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
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.
ReplyDeleteSorry I can't offer help regarding CapitalOne.
-Dan
I don't use CapitalOne. A quick search produced the following on the GNUCash site:
ReplyDeletehttp://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..."
Robert,
ReplyDeleteI 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
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.
ReplyDeleteIs 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.
Hi Robert,
ReplyDeleteMSMoney 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
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.
ReplyDeleteTo 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).
Robert, OK, nothing else I can contribute on Vanguard.
ReplyDeleteCan you clarify how to get the %Change column of Portfolio Manager working?
Dudley
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.
ReplyDeleteThe 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
Dudley: %change is not updated within Money via the OFX scripts.
ReplyDeleteSal: 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.
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.
ReplyDeleteRobert: Sounds good.
ReplyDeleteThanks, Sal
Robert, any break through on the Canadian Mutual Funds quote download?
ReplyDelete-Sam
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.
ReplyDeleteYAY!...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.
ReplyDelete- Sam
(My previous comment didn't appear to work, so I'm shortening it and trying to post as anon):
ReplyDeleteWhat 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
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.
ReplyDeleteThat 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?
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.
ReplyDeleteAfter running 'getdata' the following note appears. The data seems to incorporate into Money correctly. See below:
ReplyDeleteSending 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.
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.
ReplyDeleteI 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
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.
ReplyDeleteGun: 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.
This comment has been removed by the author.
ReplyDeleteThanks for the prompt reply
ReplyDeleteThe .\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
Gun: Download the latest beta version at:
ReplyDeletehttp://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.
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!
ReplyDeleteNow I just need to figure out how to import from Janus.
Thanks again, Matt
I unzipped the new script, but I only copied the new 'Getdata' file.
ReplyDeleteA 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.
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.
ReplyDeleteThe getdata window only stays open if there is an error during the uploads.
Thanks, Robert, I d/l the files.
ReplyDeletePlease excuse my ignorance, how do i update without losing my data?
Copy all new files to the script directory, and overwrite the existing versions. Your personal data will not be overwritten.
ReplyDeleteThanks again, everything works flawlessly.
ReplyDeleteYou 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
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.
ReplyDeleteOnce 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?
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).
ReplyDeleteRobert I just started following the blog. Can you re-post instructions on how to install pocket sense for those that recently starting following. Thanks
ReplyDeleteSee info and instructions on the web site: http://sites.google.com/site/pocketsense/home/msmoneyfixp1
ReplyDeleteRobert,
ReplyDeleteI'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.
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).
ReplyDeleteThank you very much, Robert. The file is on its way.
ReplyDeleteLet me first say Robert, you are awesome! Thanks so much for putting this together.
ReplyDeleteIs 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.
Robert please consider,
ReplyDeletePutting 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
Robert,
ReplyDeleteWell, 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.
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).
ReplyDeleteBlake: 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@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.
ReplyDeleteAfter 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.
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.
ReplyDeleteDan
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.
ReplyDeleteRobert,
ReplyDeleteOFX 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.
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?
ReplyDeleteBTW. This is a great program. Thanks
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?
ReplyDeleteGetting 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
>>>
TO ALL HAVING PROBLEMS DOWNLOADING QUOTES:
ReplyDeleteDrop 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
Robert,
ReplyDeleteWhereas 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
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.
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
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.
ReplyDeleteNice detective work on your part too Blake!
ReplyDeleteI 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
Thanks all.
ReplyDeleteWould it be possible to just discard the error record rather than discarding the entire quotes ofx?
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.
ReplyDeleteRobert,
ReplyDeleteAs 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
The problem with Yahoo quotes via csv conversion that we are using here and hleOfxQuotes is two fold.
ReplyDeleteIf 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
And the following from Yahoo Finance:
ReplyDeleteQuote....
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
I have just commented out ^DJI until something else can be done.
ReplyDeleteBTW, 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?
I took some time to address the quote issue.
ReplyDelete1. 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)
Hello Robert!
ReplyDeleteI 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
3. A QuoteHistory.csv file is now created in the .\xfr directory, and will append new quote results to old.
ReplyDeleteCan 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!
Beta snapshot updated again:
ReplyDelete1. 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.
Please check the contents of the beta. I go to
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense
and get a page not found. The zip file there does not appear to be the beta.
Mike: You somehow omitted the rest of the link:
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
Robert,
ReplyDeleteIndeed I did. Somehow I ended up on a blog page where the url was truncated. My apologies.
Thanks
Mike
Works fantastic for me!
ReplyDeleteThanks again
Mike
You are on a roll Robert... I would like to echo Steve's sentiments from earlier today!
ReplyDeleteThe 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
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?
ReplyDeleteSuggestion: 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
Hi Folks,
ReplyDeleteHey 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
Just guessing here...
ReplyDeleteTry fiorg = fldProv_mId
See http://www.ofxhome.com/index.php/institution/view/446
-Blake
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.
ReplyDeleteBut 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
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.
ReplyDeleteTwo 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
Ron,
ReplyDeleteOption 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
Ron,
ReplyDeleteFYI, 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
Thanks everyone for the suggestions and feedback.
ReplyDeleteLes: 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.
Thanks, Les
ReplyDelete-- Ron
A-ha! The ol' illegal character thing got me again!
ReplyDeleteWhile 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
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.
ReplyDeleteI think I'll add a blurb about this on the website re: the Sunset Edition.
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.
ReplyDeleteThe 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
Richard, Using your beta version, I get the following when I run “getdata.py”:
ReplyDeletePocketSense 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
Sal:
ReplyDelete1. 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
Option 5 is missing from the setup.py? Everything in new beta words when testing.
ReplyDeleteI think option 5 has always been missing - I looked back as far as I could go. Don't know why I just noticed that.
ReplyDeleteI guess while I am asking dumb questions...
ReplyDeleteI 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.
Menu option 5 enables when you activate password protection.
ReplyDeleteWhen you run getdata.py, you will get a new getdata.pyc. Python uses a "just in time compiler".
I should have remembered password, my apologies.
ReplyDeleteI have searched my entire system and am not finding getdata.pyc.
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:
ReplyDelete"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?
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):
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
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)
ReplyDeleteI 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.
ReplyDeleteThe 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.
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).
ReplyDeleteRegarding 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.
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.
ReplyDeleteIf 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.
The latest code has been posted to the live/production version on the sites.google.com/site/pocketsense site.
ReplyDeleteRobert,
ReplyDeleteA 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.
1. I have not found a way to automatically update exchange rates. The only way I know is to manually enter the values.
ReplyDelete2. 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.
I'm the person whose quotes won't import into Money. I have not downloaded the Sunset version of Money and will do that.
ReplyDeleteHere 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?
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).
ReplyDelete-Dan
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).
ReplyDeleteI 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 :)
ReplyDelete-Dan
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".
ReplyDeleteSiteName : 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:
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.
ReplyDelete2204
12474
13755
Let me know if one of these works. Good luck.
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.
ReplyDeleteI'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.
I found it while looking for OFX code information for another bank.......it's here:
ReplyDeletehttps://ofx-prod-filist.intuit.com/qb1800/data/fidir.txt
Re Compass Bank, try:
ReplyDeletefid : 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
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.
ReplyDeleteI 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?
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.
ReplyDeleteI 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.
ReplyDeleteSpoke 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.
ReplyDeleteI'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.
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).
ReplyDeleteThat'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:
ReplyDelete** 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.)
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.
ReplyDeleteIs it ok to "stop using online services" in Money or would that interfere with PocketSence?
ReplyDeleteYes, 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.
ReplyDeleteI had problems with account balances showing correctly before I removed all of my online services.
-Blake
As i understand I could just continue using my Money 2006, or is there any advantage to switch to the Sunset edition?
ReplyDeleteRe: 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.
ReplyDeleteI 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).
Richard,
ReplyDeleteHave 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
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:
ReplyDeleteYahooURL: http://ca.finance.yahoo.com
I'm also working Google Finance support, but there are some glitches to work out.
Sorry about the name Robert, Old age is quickly setting in.
ReplyDeleteThanks 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
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?
ReplyDeleteThanks.
With regard to removing a wrongly associated OFX import, I found the answer here: http://money.mvps.org/faq/article/53.aspx
ReplyDeleteRobert,
ReplyDeleteI 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.
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.
ReplyDeleteAlso, thanks for the tip to fix a mismatched statement/account pair.
I initially had trouble with USAA accounts, until I figured out:
ReplyDelete1) 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)
Regarding ScoTTrade, here's the OFF info: http://www.ofxhome.com/index.php/institution/view/623
ReplyDeleteThe 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.
Thank you Thrall. Your input/comments will help others.
ReplyDeleteBack 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?
ReplyDeleteThrall,
ReplyDeleteI'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.
Robert,
ReplyDeleteI 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.
Re: USAA Investments... check here:
ReplyDeletehttp://moneydance.com/synch/moneydance/fi2004.dict
Ron: I didn't remove anything (?)... so I'm not sure what happened?
ReplyDeleteRobert,
ReplyDeleteI 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
Ron: Just to verify before looking at the & issue: you are running the 19-Sep code?
ReplyDeletehttp://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2
Robert,
ReplyDeleteWhen 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
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
ReplyDeleteThe 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.
Robert,
ReplyDeleteJust 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.
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.
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
Thanks, Robert. That will be very helpful.
ReplyDeleteBy 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.
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.
ReplyDeleteRobert,
ReplyDeleteI 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.
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.
ReplyDeleteWhat 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.
ReplyDeleteThis 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.
ReplyDelete-Blake
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?
ReplyDeleteFollow these instructions to disable online services for each account:
ReplyDeletehttp://support.microsoft.com/kb/2390720
See "What do to when your financial institution is disabled" section
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.
ReplyDeleteHi 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.
ReplyDeleteI 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......
UPDATE: I changed the way TimeZone can be edited, by making it an option in the sites.dat file.
ReplyDeleteBeta 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".
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.).
ReplyDeleteAny 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.
ReplyDeleteI 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).
ReplyDeleteRon,
ReplyDeleteThanks for the tip. I did that, but in the process also restarted... looks like the restart actually fixed the problem.
Alon
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.
ReplyDeleteI 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...
ReplyDeleteMy 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
ReplyDeleteI 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.
ReplyDelete---------------
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.
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.
ReplyDeleteDid you know you can just hit Enter, rather than hitting the "Y" key? I like Blake's suggestion.
-Dan
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.
ReplyDeleteAssuming encryption is turned off..
ReplyDeleteI 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.
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.
ReplyDeleteThank Robert for this awesome package.
ReplyDeleteyou 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.
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:
ReplyDelete"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
Thrall,
ReplyDeleteJust 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
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:
ReplyDeletehttp://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?
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.
ReplyDeleteThe 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.
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.
ReplyDelete-Dan
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.
ReplyDeleteI 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.
Phil,
ReplyDeleteYou'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.
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.
ReplyDeleteBTW, 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.
ReplyDeletePhil: 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...
ReplyDeletehttp://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip
Last comment for this post... otherwise, it will go to another page (200 posts per page). Comments continued at:
ReplyDeletehttp://pocketsense.blogspot.com/2010/11/replacing-microsoft-money-continued-nov.html