Thursday, March 4, 2010

Replacing Microsoft Money (continued)

This is a continuation for discussing the PocketSense python scripts, since comments were up to 100 on the first entry and I just posted the latest version of code to the live snapshot.  Please let me know if you have any issues or bugs.  I think we're at a stable point with the code, and it's certainly easier to use.  Big thanks to Dan and Ray for helping with beta testing.

200 comments:

  1. The following features are planned, but not yet implemented:

    * Download all account statements prior to sending the data to Money. The scripts currently send the results as they are received.
    * Allow the user to selectively send statement results to Money.
    * Of course, keep the simple option of just downloading everything, sending it to Money, and never asking the user anything extra.

    ReplyDelete
  2. Robert,

    Your strategies outshine mine, but I thought I'd put a couple of thoughts down on paper anyways :) for your consideration.

    If the only time that Feature #1 is favorable is when the user is using the webpage add-in to keep the python screen on-top, perhaps a command line argument would be a nice way to invoke this option. What I had in mind is appending the target for the shortcut:

    "C:\Documents and Settings\Dan's\My Documents\Microsoft Money\Python Scripts\Getdata.py" -money

    and then the user wouldn't have to even select an option if you were able to invoke that option based on the -money argument. (I don't think I could add the argument from the add-in webpage, but if I could, I'll modify that instead)

    Sounds like Feature #2 is like the Testing option menu in setup.py

    ReplyDelete
  3. Actually the more I think about it, I think Feature #1 would be preferable ALL OF THE TIME, so that you wouldn't be interupted by the Import messages while the script is running either.

    ReplyDelete
  4. The other question I had:

    Right now I have 8 accounts set up, so when I'm running "Test Accounts", Option 9 is Stock Quotes and Option 0 is Exit. What happens if I add one more account?

    ReplyDelete
  5. Robert,
    I only download stock/mutual fund quotes. I don't use the account transactions download. Will your program allow me to do that? If not, can you provide that option?

    by-the-way: excellent instructions and explanations.

    ReplyDelete
  6. Re: stock/fund updates without statement downloads. It will now:) I just modified Getdata.py to download stock/fund transactions even if no accounts are defined for statement downloads. Live code snapshot has been updated (08-Mar-2010). Grab the latest version, run Setup.py and enable stock/fund downloads and then edit the stock/fund entries in sites.dat.

    ReplyDelete
  7. Dan: Option "0. Exit " is hard-coded and "stocks/funds" will always be the last one; account selection is incremented, with no limit. So, 10 accounts should list 1 through 10 accounts, plus 11. Stocks/Funds, and then "0. Exit". I haven't tested that many...

    A command line option would work, but I see this as something folks may want to use sporadically, without having multiple entry paths to Getdata. I'm thinking a simpler option is to use the "Download transactions (Y/N/I)" dialog, with I = Interactive. During the "interactive" mode, we can put any extra steps we want without impacting the non-interactive mode. For now, I-mode would just pause before closing the window and ask if the user wants to send the data to Money.

    ReplyDelete
  8. Thanx Robert,

    This is what I'm proposing adding to the Money webpage:

    Download & Import your OFX data

    When prompted "Download transactions? (Y/N/I)":

    (I)nteractive is the preferred response, as the script window will
    remain visible and you can decide on what you want submitted to Money.
    (Y)es will process everything, but the script window will "hide"
    after the first OFX file is submitted to Money.

    ReplyDelete
  9. I wasn't planning on "folks may want to use sporadically" the command line option, I was going to add this option to the link, but you are right - if you are going to reply to a prompt, (I) is just as easy as (Y) :)

    -Dan

    ReplyDelete
  10. In my mind (not in code yet), the overall operation will change such that all statements and quotes are grabbed prior to sending to Money. The difference w/ the interactive option will simply be that the user is asked to confirm actions.

    I'm not sure that I would label one or the other as "preferred", since we each have our preferences. I'll almost always use the automated option, but others may "prefer" to run interactively.

    ReplyDelete
  11. OK, I'll think about it yet. Maybe I'll not add anything, as the prompt itself will present the options.

    ReplyDelete
  12. Thanks Robert for such a quick response.

    ReplyDelete
  13. Thanks for this super utility! Works well with my Money 2006. -Tim

    ReplyDelete
  14. Super utility indeed!

    Robert,
    Thank you for your feedback. Here is what the new webpage will look like:
    http://microsoftmoneyoffline.files.wordpress.com/2010/02/moneyoffline.jpg

    I figured I'd publish it now in anticipation of your Interactive option so that fewer people would have to redownload the add-in later :)

    ReplyDelete
  15. Knowing nothing about programing, I would be very interested in a java program with a GUI interface available from within Money that will merely update the prices in my quotes account. It would really simplify it for us non-tech users. I tried the HLE process mentioned in MicrosoftMoneyOffline but it is not working for me. I get an error message about the data may be corrupt. I tried using it with Money Plus 2008.

    ReplyDelete
  16. Re: a GUI interface. The nice thing about the script package is that it requires zero programming on your part. Once Python is installed and you have placed the scripts into their own directory on your computer... simply run Setup.py by double-clicking it and "enable" stock/fund quotes. Next, use Windows Notepad to edit the sites.dat file and enter your stock and fund symbols in those sections. Double-clicking Getdata.py will download the stock/fund data to Money. No programming required!

    The reason I'm sticking with Python (at least for now) is that I can quickly modify the inner workings, without all the pretty stuff. Spending time on a GUI detracts from that, so I'd rather not go there... yet. If I find some time and take the interest, who knows?

    ReplyDelete
  17. Dan: Putting pressure on me to get the interactive mode in there? :)

    ReplyDelete
  18. I just posted a BETA version with a simple "interactive" mode for Getdata.

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

    Let me know how it works.

    ReplyDelete
  19. BETA version updated just now. I fixed a small bug w/ the interactive mode.

    I've also added an (optional) entry for Sites.dat.

    SaveTickersFirst: No

    The default is to save account statements to Money first, and then send stock/fund ticker quotes. Placing this value into sites.dat w/ a value of Yes (or Y) reverses the order. It's optional, but is now included in the template for a fresh install.

    ReplyDelete
  20. Robert,

    I just downloaded and tried it out. I chose download to Money, but not knowing what Verify was, I said NO the first time. So I tried it again and see that you can verify the upload of each account individually - You've got every angle covered now!!!

    Did you consider doing a similar prompt "Upload online data to Money? (Y/N/V=Verify each) [Y] " to save answering the verify prompt?

    I had to see how you did it. ofxList is just a simple array, isn't it? You make it look so easy!

    Fantastico!!!

    ReplyDelete
  21. Glad you like it! The reason for two prompts is that you can choose Interactive, get a pause after downloads, choose to upload to Money (y/n), and decide whether to do them all or one at a time (skipping some if you want). ofxList is a Python "list" that is used to store three parameters per entry (lists and dicts are great features in Python that most languages do not have). OFX.py and Quotes.py were modified for the method, generating status codes and returning file names. Previously, files were submitted by those two routines... not by getdata, so it's more than just an edit to the getdata wrapper.

    ReplyDelete
  22. Robert,
    after you say YES to upload, you're immediately asked Verify? so my suggestion just saves answering that second prompt.
    One other item:
    Scrubbing .\xfr\DISCOVER20100311131136.ofx as https://ofx.discovercard.com

    Did you really want the "as https://ofx.discovercard.com"?

    -Dan

    ReplyDelete
  23. upload Y / verify N = upload ALL
    upload N = cancel
    upload Y / verify Y = upload w/ verify (which is what the V would do)

    Just a suggestion though...

    -Dan

    ReplyDelete
  24. I think I gotcha now Robert,

    The verify option is for Download YES as well as Interactive, isn't it? I hadn't tried that. Sorry.

    -Dan

    ReplyDelete
  25. Dan: I updated to use a single Y/N/V=Verify on the 2nd interactive prompt. Thank you.

    ReplyDelete
  26. Thank you!! (I hope you like it better too). I just realized that Download YES is the simple non-interactive response, so I guess I was thinking correctly.

    The Beta sure works great with my add-in from within Money. I expect that you'll get a lot of praise as more people discover PocketSense once their program goes offline. When running from within Money, it seems more like a new official module now.

    -ameridan (Dan)

    ReplyDelete
  27. See any bugs? Did you try:

    SaveTickersFirst: Yes

    in sites.dat to see how that works? I tested it, but sometimes bugs show up in crazy ways. It seems stable to me at this point. If so, I'll update the live version.

    ReplyDelete
  28. Caught a bug that was added when modifying the prompts. Beta updated just now.

    ReplyDelete
  29. Sorry I haven't tried all combinations yet. The Interactive Mode captured all my attention :)

    I'll play with it more tommorow.

    -Dan

    ReplyDelete
  30. Robert,

    I see you changed the Download YES so that the uploads take place after all of the downloads are done. Very nice! If you intend to upload everything, the Interactive mode isn't the only way to go now. I guess I'll take that extra info back out of my webpage since the script window stays on top now (and the Yes description is no longer correct)

    I did try "SaveTickersFirst: Yes" and it still processes them last. I even tried running setup again thinking the the python files might then recompile. I used your 7:31pm version of getdata.py

    ReplyDelete
  31. Dan: when you say "processes them last", do you mean "downloads them last" or "uploads to Money last"? I ask, because quotes always download last, but the "SaveTickersFirst: Yes" flag should send the quotes to Money first. If you run in Interactive mode, and then verify each upload... quotes should go to Money first or last, depending on this flag. Let me know...

    ReplyDelete
  32. You're right. I was expecting them to show up first on the script screen for some reason. Thank you for clarifying (make that distinction in your user guide).

    I haven't found anything else - how about you Ray?

    -Dan

    ReplyDelete
  33. Robert,

    Just tested the setup.py with Discover and the Test Account scrubbed the OFX file but didn't upload to Money.

    Although I could use Interactive & Verify, I still like to use setup script for just grabbing a singular account and this always uploaded to Money before.

    -Dan

    ReplyDelete
  34. Now THAT is a bug... glad you tested it! I forgot to modify Setup for the updated ofx and quotes script call/return methods. I'll fix that and upload a new version this evening or tomorrow morning. Thanks Dan.

    ReplyDelete
  35. This isn't a bug but as I asked before...

    Scrubbing .\xfr\DISCOVER20100311131136.ofx as https://ofx.discovercard.com

    What does "as https://ofx.discovercard.com" mean?
    -Dan

    ReplyDelete
  36. When initially writing the scrub routine, I just wanted some "debug" info to show what it was doing. Really, there's no reason to flash the "scrubbing" message at all. I changed it in the most recent version to (using your example):

    Scrubbing .\xfr\DISCOVER20100311131136.ofx

    Maybe I should make that a Debug option and have it silent otherwise.

    ReplyDelete
  37. I'll like seeing it as you changed it. Someone that is downloading their first batch of a few months worth of transactions will feel good knowing that they won't have to deal with the replication angst you felt before taking this on. :)

    The informative script screen is another key feature of PocketSense in my opinion.

    By the way, do have Windows 7? I've just rcd a comment that my webpage add-in doesn't work with Win7.

    -Dan

    ReplyDelete
  38. Beta snapshot updated. I think we're close!

    ReplyDelete
  39. Robert,
    There seems to be a limit on the number of stocks/funds allowed to be listed within the sites.dat file. When there is a large number, the getdata.py does not work. When I reduce it, then it works fine. Can you review this and remove any limit? I'm only using the quote update feature, no brokerage or bank account downloads.

    Also, it would simplify things if I didn't need to define whether a symbol is a stock, a fund, an index, or whatever - just allow them to be listed in any order, if possible. I'm working with copied files of my original MoneyPlus2008 file and it has not expired yet.
    Thanks, Sal

    ReplyDelete
  40. Sal,

    I don't think you really need the labels within the stocks section (that just helps you organize), nor does the order matter. Just make sure that open-end mutual funds are listed in the mutual fund section.

    -Dan

    ReplyDelete
  41. Sal,

    There are no imposed limits to the number of ticker symbols. I just now randomly added a bunch of stock/fund tickers and it worked fine here. I'm testing w/ the BETA version, so you may want to try it (just in case there's a difference?).

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

    If it still fails, provide more info:
    - Site/fund section of your sites.dat file
    - Error messages or a description of what happens

    Thank you.

    ReplyDelete
  42. Sal,

    One reason you may be having problems: you are now getting your quotes from Yahoo! Finance, not MSN, so there is a good chance you're using some incompatible symbols. You should go to Yahoo! Finance and check each symbol manually before plugging them into sites.dat

    -Dan

    ReplyDelete
  43. Bobby,

    Did you get my email? I'm trying to resolve the webpage add-in issue(s).

    -Dan

    ReplyDelete
  44. I've been using your code to load data for a few weeks and it has been working good. But I noticed today (it has probably been happening for a while since I don't charge to this CC) that my AMEX credit card load is failing. I'm getting and error stating
    "Client application or version is not supported by this server."

    It used to work.

    So two things... maybe keep the download window open if an error occurs.
    And do you know why I'm all of the sudden getting this error.

    ReplyDelete
  45. The code snapshot will soon be updated, and will include an "interactive" mode, so that will keep the window open. For now, use the "test account" function in Setup.py to do the same for single account.

    As for the error, one possible culprit is the AppID and/or AppVer. Are you using the most recent version? If so, you can define these parameters for a specific site to match what the institution supports.

    Look at http://www.ofxhome.com/index.php/institution/view/424 . I just now looked at the user comments/errors and noticed that they changed their server recently.

    You may also want to look at: http://moneydance.com/synch/moneydance/fi2004.dict , but it may not be current.

    ReplyDelete
  46. I should have clarified: The AppID and AppVer are optional site parameters that you can define for a site entry in Sites.dat.

    ReplyDelete
  47. Robert,
    Dan hit the nail on the head. I was using both MSN and yahoo symbols in my data.py file thinking your scrip would just ignore the bad ones. However, whenever your script found an MSN symbol, it just stopped with no error message and the screen disappeared.

    Is there is any way for your scrip to continue but then identify at the end which symbols were incorrect? This will ensure I pick up prices for all symbols.

    Thanks for the link to the beta version. Using the "I" option made it a lot easier to debug.
    Sal

    ReplyDelete
  48. 2nd Posting - I don't think my first attempt made.

    Robert,
    Dan hit the nail on the head. Thanks Dan.

    I had both MSN and yahoo symbols listed in my data.py file, thinking the scrip would just ignore the bad ones. I didn't bother to screen out the MSN only symbols. It turns out whenever your script finds a non-yahoo symbol, it just stops and the screen disappears.

    Is there any way your script can ignore the bad ones, continue to process the rest and then provide a list of the incorrect symbols at the end? This will allow the user to update his data.py file so all symbols are priced.

    Thanks for the beta file. It made debugging a lot easier.
    Sal

    ReplyDelete
  49. I just updated the BETA version to handle symbols not recognized by Yahoo! Finance. If anyone can test on their system, that would be useful before updating the live version.

    ReplyDelete
  50. Robert,

    That is great! Doesn't bomb-out anymore; just skips. That's a keeper. Also like how you have default prompt responses in [] indicating that you can hit Enter for that value.

    -Dan

    ReplyDelete
  51. Robert,
    It worked perfectly in my MoneyPlus2008 program. Using the "I" choice allows me to see those that were rejected. Very nice.

    To update quotes only (no account downloads), the "I" choise is really the only option for me. I like the idea of being asked whether to update my Money program before actually doing it.
    Thanks, Sal

    ReplyDelete
  52. Robert,
    It worked perfectly in my MoneyPlus2008 program. Using the "I" choice allows me to see those that were rejected. Very nice.

    To update quotes only (no account downloads), the "I" choise is really the only option for me. I like the idea of being asked whether to update my Money program before actually doing it.
    Thanks, Sal

    ReplyDelete
  53. Robert,
    I think my computer is bugged. This is the 2nd time I've had to send my comment twice, as you can see it from my previous comment. I'm sure it's user error. This time I had the sense to copy my comment before sending.
    Sal

    ReplyDelete
  54. Robert,
    I just found a weird problem. When I had the Dow Jones Ind identified as an Investment Type "Index" within Money, your script would not pick up the quote cost. I changed the type to "Stock" , then I got the quote.
    Sal

    ReplyDelete
  55. Robert,
    I just found a weird problem. When I had the Dow Jones Ind identified as an Investment Type "Index" within Money, your script when not pick up the quote cost. I changed the type to "Stock" , then I got the quote.
    Sal

    This is my second attempt to transmit to comment. Something is strange. I am using the preview first.

    ReplyDelete
  56. Everything but mutual funds go in the stocks section. Those subcategories are just comments to "help" you organize. Maybe it would be best not to have those in the template as the user could just add those (if they want to) and perhaps they're just adding confusion.

    If you see a note saying your comment has been added, just ignore the fact that it doesn't show up right away.

    -Dan

    ReplyDelete
  57. Sal: You lost me there on the DJIA. Yahoo! uses the ^DJI symbol for the DJIA, whereas Money (and MSN) would use $INDU. I wouldn't expect Money to match the two, but your saying that it did? I don't import those myself, since they're not actually funds... just reference indexes.

    ReplyDelete
  58. Robert,
    I guess I didn’t explain it correctly. Forget about MSN vs Yahoo! I'm only talking about using Yahoo! symbols.

    For history within Money, I import the prices for ^DJI, ^GSPC & ^IXIC. Within my Money program, these investments were identified as indexes. "Getdata" obtains the prices for these symbols from Yahoo! – I can see them in the script. The investment type choices within Money are: bond, index, stock, mutual fund. Because I had each of these investments defined as an "index" rather than a "stock", Money for some reason would not update the prices. When I changed the investment type for each within Money from "index" to "stock", the prices were imported/updated correctly as expected.
    Sal

    ReplyDelete
  59. OK, I understand. The scripts are writing the stock/fund prices to an account that you have setup. I'm pretty sure that you can't have indexes in an account, since you cannot invest money in them (thus no reason to have them in an investment account). Calling it a "stock" or "fund" allows it to go into the account, since Money doesn't know one ticker from the next. You can invest $ in index *funds*, but those have different tickers than the indexes themselves. I haven't tested this, but it makes sense that it would be that way.

    ReplyDelete
  60. Robert,
    After thinking about it some, I don't think you should bother trying to do anything with this "index" vs "stock" issue. It would be a waste of your time. The "getdata" works just fine for me, the way it is. It's no big deal for me to change the investment type to stock.
    Thanks, Sal

    ReplyDelete
  61. Robert,
    Because of your work, I can now continue to use my MoneyPlus program for as long as I want. Thanks,
    Sal

    ReplyDelete
  62. . sorry for these .'s but it's the only way I can post my comment quickly. The 2nd post seems to push out the 1st post.

    ReplyDelete
  63. Sal,

    I guess I misinterpreted your question too. Money imports my market indexes (defined as Index) just fine, but they are not in an investment account, they are in a Watch account.

    -Dan

    ReplyDelete
  64. Robert,

    Even though I have Online services till August, I've switched them off because PocketSense actually works better than Online!

    Money ignored some transactions before like Discover Cashback awards and the DirectPay notes that had $0.00 associated with them, that now process into Money as I would expect them to.

    Have you noticed this too?

    ReplyDelete
  65. Yes... I did notice that. I actually considered filtering out the 0.00 entries to mimic what Money does, but decided I liked it better with them. As you said, those entries often contain notes.

    ReplyDelete
  66. Yes, like when the bill is due. I upgraded to MoneyPlus and not knowing the new version blocked the messages out cost me a $35 late payment fee that Discover wouldn't reverse. That is when I initiated DirectPay.

    So it looks like the only negatives to OFX file updates vs. Online are:
    1. only one quote value per day (first-in)
    2. no MSN news and chart links for investments

    ReplyDelete
  67. Robert,
    Is there any way to import the quotes so they show up as “Online” rather than “Update”. When I ran “Online” quotes, it wrote over my “Updates” prices. I guess I’m hoping this would allow more than one price download per day.
    Sal

    ReplyDelete
  68. Sal: I don't think it's possible to import stock quotes as "online". To my knowledge (at this point), that is a built-in feature for Money (i.e., imported data is processed differently).

    ReplyDelete
  69. re: I just updated the BETA version to handle symbols not recognized by Yahoo! Finance.

    I like this feature. I've got an options symbol (using the new symbology that officially starts in May) in the list that SKIPS. I'll now know when Yahoo! switches over.

    -Dan

    ReplyDelete
  70. Dan: The live version is probably slightly more recent than the beta at this point... although I don't recall changing much between the two. Let me know how those options pan out.

    ReplyDelete
  71. Robert,

    I've not experienced any problems with the latest version and I really like all of the enhancements.

    -Dan

    ReplyDelete
  72. Robert,
    I just ran my normal internal Money “Online Updates” and noticed that the price history within Money registered the Friday date (3-19-10) which was the last day the Stock Market was open, not today’s date (3-20-10) when I ran the Money program. When I ran the “getdata” program, it registered as 3-20-10, the day I ran the program, not 3-19-10 which was the last date the Stock Market was open.

    When I run “getdata” in the “I” mode, I notice the dates are all showing the 3-19-10 date, but when I look within Money, it shows the 3-20-10 update. If possible to do, I think the 3-19-10 date would be the more desirable date.

    Also, what would really be NICE within the “getdata” program is to allow the user the ability to obtain prices for a PAST date. The default of course should be current date. This would probably be impossible for account downloads, but may be possible for just prices/quotes. I think price history is available in Yahoo, but I am not sure if it’s possible to retrieve them.

    Whether you can do the above or not, I am very happy with your program as it is.
    Thanks, Sal

    ReplyDelete
  73. Dan or Robert,
    Adding my comment worked correctly on the first try. Whoever fixed it - Thanks, Sal

    ReplyDelete
  74. Robert,

    As Money gets closer to going OFFLINE, I can foresee some financial institutions dropping OFX (sometimes referred to as Microsoft Money format) downloads and only offering QFX downloads instead (I hope not though!!!).

    If this does occur, it should be very easy to adapt the scripts to also have the capability of downloading QFX files, and then “scrubbing” them by merely changing the extension to OFX, since everything in a QFX file is OFX compliant. The unique “INTU.BID” tag’s value that Quicken uses to makes a connection back to Intuit (“call home”), and verify that the financial institution has licensed (PAID for annually) the right to import that file into Intuit’s software, isn’t of any consequence to Money. By scrubbing the QFX file, it wouldn’t be necessary to register the QFX extension with Money’s Import Handler (which is an alternative fix though).

    ReplyDelete
  75. Robert & Sal,

    I've just verified that all of my quotes downloaded from my broker today (statement is dated yesterday) only updated the quote for yesterday's closing price, and nothing for today.

    On the other hand, the quotes from quotes.py all have an entry for today (Saturday) even though, as Sal stated, the ticker date is yesterday at closing.

    This is NOT!! a complaint, but I agree with Sal's suggestion that quotes.py should be reworked to act like the broker's downloads in this regard. It was probably written by TFB to just use the current date?

    If a user would like the script screen to also list the current quote price, that might be an option in Interactive Mode - but not to be dwonloaded into Money. That would also end the complaints that the quote price can't be updated with newer OFX downloads that day.

    -Dan

    ReplyDelete
  76. This is from a brokerage statement on Saturday, 3/20/2010:

    {INVPOSLIST}
    {POSMF}
    {INVPOS}
    {SECID}
    {UNIQUEID}808509830
    {UNIQUEIDTYPE}CUSIP
    {/SECID}
    {HELDINACCT}OTHER
    {POSTYPE}LONG
    {UNITS}1
    {UNITPRICE}16.5500
    {MKTVAL}16.5500
    {DTPRICEASOF}20100319210000
    {MEMO}SCHWAB INTL INDEX FUND
    {/INVPOS}
    {/POSMF}

    and this from quotes.py statement on Saturday, 3/20/2010:


    {INVPOSLIST}
    {POSSTOCK}
    {INVPOS}
    {SECID}
    {UNIQUEID}F
    {UNIQUEIDTYPE}TICKER
    {/SECID}
    {HELDINACCT}CASH
    {POSTYPE}LONG
    {UNITS}0
    {UNITPRICE}13.29
    {MKTVAL}13.29
    {DTPRICEASOF}20100319160100.000[-5:EST]
    {/INVPOS}
    {/POSSTOCK}

    notice that DTPRICEASOF is the same for both 3/19/2010 @ 4:01 P.M. EST
    = 160100 which is the same as 210100 GMT.

    So Robert, I am at a loss as to why they are processed differently???

    -Dan

    ReplyDelete
  77. ----------------------------------------

    unless it's because it uses the TICKER section with just today's date instead:

    {STOCKINFO}
    {SECINFO}
    {SECID}
    {UNIQUEID}F
    {UNIQUEIDTYPE}TICKER
    {/SECID}
    {SECNAME}Ford Motor Compan
    {TICKER}F
    {UNITPRICE}13.29
    {/SECINFO}
    {/STOCKINFO}

    here is the respective secondary section of my broker statement:

    {MFINFO}
    {SECINFO}
    {SECID}
    {UNIQUEID}808509830
    {UNIQUEIDTYPE}CUSIP
    {/SECID}
    {SECNAME}SCHWAB INTL INDEX FUND
    {TICKER}SWISX
    {/SECINFO}
    {MFTYPE}OPENEND
    {YIELD}2.8000
    {/MFINFO}

    -Dan

    ReplyDelete
  78. The quotes are being sent to money as an account statement, with embedded security pricing. Money uses the account closing date to update prices, *not* security closing dates. I can modify the script to pick the first "security closing date" and to use that date for all quotes. That should work for everything, since they're all downloaded at once.

    Before doing that, I'm going to play around and see if Money can handle multiple accounts (and thus multiple closing dates) in a single ofx file (as we don't want a separate file import for each security).

    ReplyDelete
  79. Regarding the Money OFX and Quicken QIF files, I don't think any changes will be necessary. The scripts currently default to "looking like" Quicken to the server (not Money), and the file extension of .ofx is created by the script... not the institution. You can define different values for AppID and AppVer in sites.dat for a specific site, but the defaults are ID=QWIN and VER=1800.

    ReplyDelete
  80. Robert,

    Thanx for the replies. Regarding Quicken (QFX, not QIF) - right now we are accessing OFX servers at the financial institutions, and I guess I was thinking that eventually they might only offer QFX. Probably the same servers though...

    Regarding the quotes, I hope you are just enjoying the challenge and not thinking that we are all complaining! Normally, I wouldn’t download quotes for investments already quoted on brokerage statements, but Ray (he requested the option of downloading quotes first before brokerage statements) is doing that to get a more recent value (probably not a closing price though) into Money for ALL of his investments.

    I guess I’m addressing the wishes that others are expressing (like Sal) that the quote values be more representative of the Online quotes Money gathers from MSN, which are closing prices for days the market is open – so no values for holidays and weekends. Since Online updates aren’t “first-in” value only, you always got the current price and the closing price just replaced those values eventually.

    -Dan

    ReplyDelete
  81. Absolutely Dan... I've had a bit of fun doing this. I don't rely on quotes for much, so it's good to get feedback regarding that function. I didn't write the original quotes.py (of course, that would be TFB), but I've modified it enough that it's no longer the same. I am happy to implement additional modifications to make the pkg useful to others, as I have time.

    I personally think that quotes should reflect the actual closing date/time... so that's what I'm going to look into implementing. At a minimum, I will pick an actual quote date/time from a connection and use that value for all quotes.

    ReplyDelete
  82. Robert,
    If possible, having the ability to request a quote for a past date would be a NICE feature (not mandatory).

    Would having two different scripts (getquotes and getACCTdata) make it easier to accomplish what you are trying to do?
    Thanks, Sal

    ReplyDelete
  83. Hi Bobby,

    I just sent you an email regarding some other enhancements I had in mind...

    Because of all the variabilities in users OS and file property settings, I‘d like to come up with a python script that could mate with your PocketSense that would actually create the webpage addin for Money.

    All,

    Another thought I had that I'd like to hear from others as to whether it would be worth developing - All of the stocks and funds that are listed in sites.dat could show up on this webpage as links to Yahoo! Finance, so that users could click for news, charts, etc on each investment like they can with MSN Online thru Money currently.

    Regards,
    Dan

    ReplyDelete
  84. Robert,
    Would it be possible to tell us when your program does not find an internet connection? Without an internet connection, the getadata program attempts to get prices for about 10-15 seconds and then shuts down and the screen disappears without an explanation.
    Thanks, Sal

    ps- I think Dan's idea about links for all stocks and funds in the sites.dat file is worthwhile. It's a quick way of researching your securities.

    ReplyDelete
  85. Sal: I see two options for the net connection idea:

    1. Force a user prompt if any of the attempted net connections fail (easy to include, and I probably will regardless of #2), and/or
    2. Specifically test the network by attempting to connect to a known host or two first (e.g., Yahoo/Google). This would be a tad more involved, but not too bad.

    ReplyDelete
  86. Hi Robert -

    I just came across your scripts. While trying to set up the sites.dat file, I cannot find my bank in the OFX Home directory.

    How/where can I find the information I need to update the dat file? My bank is Compass Bank.

    Thanks -
    Mimi

    ReplyDelete
  87. see if this helps:

    http://ofxblog.wordpress.com/?s=compass

    -Dan

    ReplyDelete
  88. Robert think you are filling a big need here with these scripts. Many users do not want to give up Money. Quicken is a very poor alternative. You might want to consider a full GUI version of this and post it on a website for paid downloads. I think you would be surprised as to the need for this product.


    NS

    ReplyDelete
  89. Robert,
    To avoid confusion, can you prevent add'l comments to be entered into the Feb 6 comments and force everyone to comment only in the latest Mar 4 string?

    However, please allow us to review both strings (since they both contain good information), by listing links to both comments under Comments in your home page. To tell you the truth, I didn't realize there were 2 sets of comments so I missed half of the discussions.

    Also, I’m not sure where Dan obtains the bank information, but having a link in your home page to all of the various banks known to date would be quite helpful to new users. If there is a way of finding that info on our own, an explanation on how to do that would help as well.
    Thanks, Sal

    ReplyDelete
  90. Sal,

    You must not have been to my blog (that strongly promotes PocketSense!), as I have links to 4 different sources of bank data & anything else I find that might be helpful to Money OFFLINE users.

    http://microsoftmoneyoffline.wordpress.com/look-up-your-ofx-settings/

    -Dan (ameridan)

    ReplyDelete
  91. NS,

    As a matter of fact, I'm hard at work on a GUI interface for these scripts (Robert is helping me). See my blog, as I've posted the initial version there. We're working on simplifying the install as well as improving the webpage that will also list all of your investments with links to the Yahoo! pages for each of those investments (rather than MSN).

    My new version will also have a link to update the ticker list so that when you revise or add investments in sites.dat, the webpage will reflect those changes.

    -Dan

    ReplyDelete
  92. Sal: I appreciate the feedback. I'm going to wait for the current thread there (i.e., Ray's tests) to complete before attempting to close-off comments there. Sorry for the confusion.

    The to-do list for the next revision to the code is beginning to gel, so it shouldn't be much longer before I set aside a bit of time to incorporate the changes... probably soon after Ray finishes his tests.

    ReplyDelete
  93. Robert,
    1- When I add a new stock into Money, it will retrieve the end-of-week stock price history for about 6-7 years in the past. This is good info for various performance and value reports. This would be a valuable feature to maintain, especially if I forget to run the update quotes every Friday.

    2- Money does not provide daily prices, nor does it provide the ability to retrieve prices as of a particular date. As a result, if I want the net worth as of a particular day (such as end-of-month, quarter, year), I would need to add them manually if those dates don’t happen to coincide with the end-of-week dates. This would be a valuable new feature currently not available in Money.

    Please consider adding these to your to-do list?
    Thanks, Sal

    ReplyDelete
  94. Robert,
    Here is a consolidated list of requested updates for quotes:

    - Allow more than one quote update per day.
    - Use the actual market day quote date when requesting an update over the weekend.
    - Identify when no internet connection is available.
    - Be able to retrieve end-of-week price history for a specified interval if available at Yahoo!.
    - Be able to retrieve the end-of-day stock price for a particular date.

    Thanks, Sal

    ReplyDelete
  95. All involved in this effort have restored my faith in humanity :-)

    I am having an issue with the quotes download, however.

    I have a pretty large watch account and I have all the quotes downloading correctly. When it imports, I am presented with a dialog to select which account to link to. No watch accounts are listed - only investment accounts.

    I created a test watch account, changed it to an investment account, removed the associated cash account, and imported. Everything works but then I go into the portfolio manager and nothing is listed under that account. If I pretend to buy a single share, then the quote displays. I tried the instructions listed under "OPTIONAL: Create an Account in Money to Receive Stock Quotes" as well just in case I was missing something. Any ideas? Thanks - SM

    ReplyDelete
  96. Sean,

    I know I've talked about Watch accounts before, so I hope I didn't confuse you. Anyways...

    The quotes script download is indeed linked to a dummy investment account, but you won't see any investments listed there because you haven't purchased any of those investments there. Instead the prices are reflected for those same investments in the accounts that actually hold them. In the case of the Dow Jones Average (as an example), you'll need to purchase 0 shares @ $0.00 in your watch account, if you want to see the current price of the Dow reflected there.

    I hope this helps :)

    -Dan

    ReplyDelete
  97. Dan,

    Thanks for the quick response. I think the challenge I am having is that I can't seem to link to the download to the watch account. Once it downloads the quotes, Money then prompts me to select an account for an imported file. The list of selected accounts does not include watch accounts - only investment ones.

    So, I think you suggesting that I need to link it to (any) one of the investment accounts lited and then the quote prices will be reflected in the watch account for those securities listed there (with zero amounts purchased).

    Let me know if I am off track there or if you are able to get watch accounts to directly link to your quotes download.

    Thanks again,
    -SM

    ReplyDelete
  98. Sean,

    You need a "dummy" investment account to receive the stock quotes. I named mine "Tickers"... but anything will do. The quotes are being imported in the form of a "statement", which must go to an investment account.

    ReplyDelete
  99. Robert,
    I think it's important to hi-lite that one has to create an "EMPTY" dummy investment account (not watch account). I made the mistake of adding zero quantities of all of my different stocks in the dummy account thinking it was necessary to capture the price quotes. As I found out later, that is not necessary.
    Sal

    ReplyDelete
  100. Sal/Robert,

    Thanks for the insights.

    Sal, I am interested in how you are getting the quotes to display in the Portfolio Manager without using a watch account (with zero amounts) or entries with zero amounts in the dummy investment account.

    I am basically finding two ways:
    - create an actual watch account with zero value entries that picks up the values from the quotes statement imported into a dummy investment account (that has no entries)
    - or having entries with zero amounts in the dummy investment account with no watch account.

    Thanks again all for you work on this. Great stuff.

    ReplyDelete
  101. 04/28/10
    SMM,
    The quotes for the stocks that I own show up in the real investment account. If I'm interested in tracking stocks that I don't currently own, then I would add them to an investment account (not a Watch account) which I call "Investments to watch". An investment account shows up in the Portfolio Manager; a watch account does not. However, the quotes for stocks/mutual funds in a watch account can be seen in the Account Register – Account Summary view.

    As long as all of the owned and non-owned stocks are listed within the "getdata.py" file, the quotes will be updated no matter what account they're in.

    If you want one account that lists all of the various stocks you own or want to own, and be able to see them in the Portfolio Manager, then an investment account (not watch account) with zero amounts should work.

    Robert/Dan,
    If I've said something that is not true, please correct me.

    Sal

    ReplyDelete
  102. I think you have it Sal. Personally, I no longer use the built-in online features of Money as my license has expired. As such, I cannot add a Watch account to test how that works.

    Stocks and funds that I actually own get their values from the real investment statement downloads. The extra stocks/funds that I may want to watch or that are in accounts for which I *do not download statements* are the only entries in the sites.dat file. There is no need to enter stock/fund tickers for those that you both: a) own in an account, and b) download a real statement for.

    ReplyDelete
  103. Sal,

    One correction - a watch account does indeed show up in the Portfolio Manager; I have one in there today. However, to create a new one, your online services still need to be active. Once offline, Money won't let you create a new account of that type.

    Looks like in the offline world then:

    Stuff I own: Quotes will download with statements.

    Stuff I don't own: I will need to make sure they are in the sites.dat file. I will need a dummy investment account to import the quotes statement into. I can then either make entries into that account that have zero values or, if I really wanted to, I could enter zero values in a watch account. Either way, the quotes will display in the Portfolio Manager but I need those zero value entries in order to make that possible.

    At least this the way my Money Plus Deluxe is working today.

    -Sean

    ReplyDelete
  104. Sean is correct, Watch accounts do show up in Portfolio Manager (unless you choose not to). I own stocks in DRIP accounts that have no online statements available, so this is an example of STUFF I OWN that aren't quoted via statements, but are included in sites.dat for OFX quotes (confirming what Robert stated).

    -Dan

    ReplyDelete
  105. Robert,

    Well, two solid weeks of running three files in parallel and all looks fine.

    File1=MSMoney using MSN & on-line downloads
    File2=Moneysense run from within MONEY
    File3=Moneysense run before starting MONEY

    As long as the quotes are created and loaded before broker statements, all three files match.
    The only thing not matching perfectly in the last two weeks is that Yahoo quotes for indexes may differ from MSN quotes by a penny, and it happened twice. It may be how Yahoo/MSN calculate the index last price. I would not be surprised if I saw the same thing with Google quotes.

    I sure wish I knew a lot more about how MSMoney uses invisapi.dll to get quotes into Money.

    Ray

    ReplyDelete
  106. Sorry, I meant Pocket Sense above not moneysense!
    These are all sounding the same.

    By the way, If I use FireFox, I cannot post here, but I can with IE7, go figure!

    Ray

    ReplyDelete
  107. Robert,

    Thanks for the great work with the workaround. I've been putting off dumping Microsoft Money for some time since it has been my favorite money management program for years. I installed your program and it works like a charm.

    Thanks again for the hard work.

    Mark

    ReplyDelete
  108. I finally found a slice of time to implement some of my "to do" items. The BETA is available if anyone has time to test.

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

    This version is mainly a bug-fix release. Specifically:

    1. "Save tickers first" now downloads quotes first so that the file timestamp is also the first. Money does not use a FIFO method for importing files in a queue, but instead uses the file timestamp. Thanks to Dan for finding and verifying this issue!
    2. Fixed a bug that only allowed testing/deleting accounts numbered 1 to 9. It should now support an unlimited number of accounts.
    3. If a connection fails, it is now reported and the exit occurs more gracefully. Getdata will now move on to the next account or quote server and try again.

    These were the key "bugs" that I've noted. If I don't hear anything negative, I'll post this as an official update. With these out of the way, I can begin working on enhancements to the Quotes engine.

    ReplyDelete
  109. Although the quotes download first, they were the last to import into money for me and my flag is set to Yes. Just an observation, but my quotes.ofx file had yesterday's date and was modified with the new download. Is it possible you inserted the quotes download prior to deleting the ofx files? :)

    -Dan

    ReplyDelete
  110. Thanks Dan. I'm not sure what's happening differently on your setup? I just tested it on my system at home and it worked correctly (i.e., SaveTickersFirst=Yes gets stock quotes into Money first). I glanced through the code and didn't notice anything obvious. If you run in interactive mode, do quotes go into Money first?

    ReplyDelete
  111. Correction: On re-reading what I wrote yesterday, I noticed a typo: The issue w/ quotes not loading to Money in the right order was discovered/investigated by Ray. Even I can't edit these comments!

    ReplyDelete
  112. I ran getdata in Interactive mode this morning, and you're right - it does behave correctly. But click on properties for your quotes.ofx file and see if it isn't a modified old file. Is the quotes data file being created too quickly after the delete command? Perhaps it doesn't really matter though.

    I ran getdata a 2nd time (in non-Interactive mode) afterwards and witnessed it behaving properly again, so never mind my report I guess, but yesterday, I know I saw the dummy account process last in the statement review screen.

    -Dan ~ (I know I've helped Robert, but Ray gets the credit for this one!)

    ReplyDelete
  113. You're right Dan. I just verified that the quotes.ofx file "date created" timestamp does not change. It is being deleted, but the Windows NTFS system is "remembering" that the file already existed and keeping the old "created" timestamp. I assume that this timestamp expires after a bit, but apparently not within the first few seconds (I just tried it). I'm not going to worry with it unless it turns out Money is actually using the "created" timestamp rather than the "modified" stamp. The work-around would be to create a unique file name every time.

    What's frustrating is that my initial 5-min fix to this was to just change the modified timestamp to the right order, as changing the created stamp isn't straight-forward. I wanted to make an attempt at updating the created stamp too, thus the reason for actually changing the download order. Live and learn... :)

    ReplyDelete
  114. To be on the safe side, I modified the quotes script to name the file quotes+time$.ofx... to ensure that we always get a new created timestamp. BETA updated.

    ReplyDelete
  115. A definite improvement! By merely commenting out the delete *.ofx, someone may be inclined to save the quote files now for restoring .mny files or sharing online for backfilling quote data :)

    -Dan

    ReplyDelete
  116. Robert,
    I tested the "getdata.py" beta version without an internet connection in both the default "yes" mode and the "I" mode. In both cases, I got the "Getting security and fund quotes..." for about 10 to 15 seconds, then the script screen totally disappeared, similar to what it did before.
    Sal

    ReplyDelete
  117. Thanks Sal. I forgot to add a prompt to keep the window open, but it shouldn't be hanging for 15 secs. I tested the same (disconnected), and it passes through each attempt very quickly without bombing.

    I just added a prompt for those cases where there are no successful connections. Beta updated. Let me know what the screen dumps out.

    ReplyDelete
  118. MS has released the version of Money that does not require online activation:
    http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=60302e1e-207e-4710-ac80-d19c22e47488

    Has anyone tested pocket sense with this version?

    ReplyDelete
  119. It is my understanding that the Sunset edition of Money does provide the OFX statement import function. I haven't tested it (yet) to confirm, but assuming this is true... then yes, the PocketSense scripts will work correctly.

    It is great that Microsoft is putting the Sunset version out there to support previous customers, and it probably isn't a bad option for new users for the time being either.

    ReplyDelete
  120. Robert,
    Please provide the link for the updated beta version. I tried your last link (http://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip) but I'm getting the same incorrect result. I don't think I'm picking up your latest beta.
    Sal

    ReplyDelete
  121. That's the correct link Sal.

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

    I just updated it again, and the "modified timestamp" for most of the files is 8-May. If it isn't pausing at the end when the network is disabled, then something isn't right. Run Getdata.py directly from the command prompt (python getdata.py) so you can see what's happening... since the window won't disappear.

    ReplyDelete
  122. I just added a Title/Version line to the start of the Getdata and Setup scripts, so that it tells what version is running. Note that every file from the zip needs to be updated when a new version is being installed or tested.

    ReplyDelete
  123. The Beta has been working perfectly for me Robert.

    It would be nice for someone to confirm that the "Favorite Web Sites" option will still be available in the Sunset edition (my guess is that it will be).

    -Dan

    ReplyDelete
  124. Robert,
    I retrieved an updated beta and it now works correctly. One point! I get the message:
    "An error occurred trying to connect to the Yahoo! service
    Finished downloading data
    No files were downloaded. Press Enter to continue... "

    May I suggest:
    "An error occurred trying to connect to the Yahoo! service. Check your internet connection."
    No files were downloaded. Press Enter to exist and try again."
    When I press “Enter”, the script screen disappears.
    Sal

    fyi – I still get the Sat or Sun date, not the last Market date - but I don’t think you have attempted to correct this yet.

    ReplyDelete
  125. Dan,
    Out of curiosity, can you explain what you mean by your 5-7-10 comment:
    A definite improvement! By merely commenting out the delete *.ofx, someone may be inclined to save the quote files now for restoring .mny files or sharing online for backfilling quote data :)
    Specifically “be inclined to save the quote files now for restoring .mny files”.
    Thanks Sal

    ReplyDelete
  126. Sal: I haven't yet worked on the time/date stamps for quotes, but it's an issue I plan to address soon. Glad the new beta worked, and I'll modify the message to be more descriptive. Thanks for the feedback!

    ReplyDelete
  127. Sal,

    Now that the quote files all have time stamps as part of the file name, they could theoretically be saved and imported as a group to fill in quote data on a older restored money file.

    (This is assuming an easier method isn't found to create an ofx file with the historical data for a given time period)

    -Dan

    ReplyDelete
  128. Sal,

    I forgot to add that the files can't be saved if the getdata script is erasing them - that is what I meant by commenting out the delete *.ofx

    -Dan

    ReplyDelete
  129. Robert,
    The first line in the 11 May 2010 version of "getdata.py" still shows "ver 10-May-2010 beta". It looks like you just forgot to change this to "ver 11-May-2010".
    Sal

    ReplyDelete
  130. Interesting Sal. Mine shows correctly, and I just verified the live code. Verify that you installed all of the new files. Version info is in the control2.py file. Timestamp for latest control2.py is "5/11/2010 10:34pm".

    ReplyDelete
  131. Robert,
    I originally downloaded the "ofxpy_moneysense_2010-05-11.zip" file today (5-12-2010) at 9:08am from your Install and Setup site and the control2.py file showed version 10 May 2010 beta. I downloaded again at 3:13pm and the control2.py now shows version 10 May 2010, without the beta. I guess I now have the correct control2.py file. I'm really not sure what happened.
    Thanks, Sal.

    ReplyDelete
  132. It would appear that your computer has to everything twice :) I'm really not sure, as I updated the file yesterday evening and haven't touched it since... but it's good you have the right one now.

    ReplyDelete
  133. Upgrade Proceedure
    Dear Forum:
    Pocketsense 18-03-2010
    Win XP SP3, IE8
    What is the porceedure for upgrading to the new build?
    The instructions on the 'Setup & Install' site only reference a 'new' installation.
    Do I need to re-edit the new sites.dat file or can I copy the one from the 18-03-2010 build?
    Same question for setup.py. Do I need to re-enter my account #'s and passwords into the new file or can I just copy the old one?
    Thanks in adavance and thanks for a great application.
    -Kevin N.

    ReplyDelete
  134. Answering for Robert,

    He has developed the package such that your data files are seperate from the script files (hence the template, etc.) You simply extract ALL files into the same folder, which overwrites the older files.

    He'll probably add a statement regarding upgrading in his instructions though...

    -Dan

    ReplyDelete
  135. Thanks Dan.
    I just wanted to make sure.
    -Kevin N.

    ReplyDelete
  136. Kevin: Thanks for pointing that out. I'll edit the instructions for an update. As Dan wrote, extract *all* the new files and overwrite the old. Everything else is either static (the sites.dat and ofx_config.cfg files) or dynamically updated (the compiled *.pyc files).

    The .dat and .cfg files are user-specific, and I never provide those in the package. They are created the first time Setup.py is run.

    ReplyDelete
  137. New to this so bare with me.

    I tried to test my alaska airlines cc account and this fails. The OFX contains the following "The product which you are using is not enabled. Please call customer service at 1.800.792.0808 for assistance."

    What am I missing?

    ReplyDelete
  138. We need more details... Did you have this working with Money previously? Did it require Yodlee? The message indicates that you need to call that # and enable Quicken downloads (make sure they won't charge you a fee for that privelege).

    -Dan

    ReplyDelete
  139. I used those online sevices for years with money.
    Is there a parameter I need to change to tell Bank of America that I am a MS Money customer/user? BTW, the "Alaska Airlines CC" (i.e. BoA) does not require Yodlee.

    Regarding Yodlee, I encountered another problem with Sterling Savings Bank that uses Yodlee. With this bank, I get the following error: "Site STERLING does not have a (REQUIRED) BANKID value defined. An error occurred while trying to download the statement. An error occurred while testing the new account." I have not idea where to get the bankid from.

    ReplyDelete
  140. I'm not familiar with the card, but was it working with Money before? If so, you could also try using "MONEY" for the AppID and 1600 as the APPVER in the Sites.dat entry for this site. This tells the server that Microsoft Money is connecting, rather than Quicken. The default for the scripts is to mimic Quicken, since it's assumed that sites will eventually stop supporting Money.

    ReplyDelete
  141. Not familiar w/ Sterling Bank. Did you connect to it from Money before? It's possible that Money was pulling the info from Yodlee (I'm no expert on Yodlee, having only read about a Microsoft deal to use their data). The scripts require that the bank/brokerage/credit company have an OFX server running that you can connect to (thus the BANKID field).

    ReplyDelete
  142. I tried with Money and 1600 > same result with BoA (Alaska Airlines).

    Regarding Sterling, I know that it does work with Money as I have been using it for a while but I do not know if Quicken supports it though.

    Now, I wonder if there is something else going on with my setup because even AMEX brings me and error > "Client application or version is not supported by this server."

    ReplyDelete
  143. I have BoA and it requires Yodlee unless you're willing to pay for the downloads. You may not realize you used Yodlee if you log on with the microsoft live ID. For BoA, simply log onto the site and download your statement rather than using the OFX script.

    Look up the BankID for Sterling at one of the sources here: http://microsoftmoneyoffline.wordpress.com/look-up-your-ofx-settings/

    Amex should work with the default QWIN and 1700, so unless you've overwritten these values, I can't help you out there.

    -Dan

    ReplyDelete
  144. Amex works now. Somehow I was using a URL that was invalid.

    BoA and Sterling are both using Yodlee. Are you saying that I won't be able to automate my downloads and I have to do this manually every time I want to synchronize Money and my accounts. That would suck.

    I never used too many features in Money. The only ones I care were:
    - Be able to enter transaction ahead of time
    - Be able to have bills setup (Bill Summary)
    - Be able to reconcile my accounts
    - Download the transactions automatically and synchronize them with what I entered to make sure that there are nothing unusual.
    I cannot believe that there are nothing out there that can do that.

    Christophe

    ReplyDelete
  145. Yep, my credit union accounts and BoA are manual OFX imports (and always have been).

    When BoA asks me why I don't use their card more often, I tell them that it's because they don't offer free OFX transaction downloads, like Discover does.

    -Dan

    ReplyDelete
  146. +1 on Discover. I originally switched to them solely for the OFX support. Our CU doesn't offer anything online, and our other CC finally began offering OFX connections.

    Regarding Yodlee, it's my limited understanding that they don't actually do direct downloads, but instead use your user/password info to login and skim through your bank accounts to collect transaction data. Microsoft formed an agreement with Yodlee to use their service to allow Money users to get their data from sites not offering an OFX service. Generally, that's not a very good way to do it though. OFX is a direct, well-formed vehicle for data retrieval, whereas (IIRC) Yodlee is "reading" your web site account and parsing it out. I've never used Yodlee, so I may not have this right.

    ReplyDelete
  147. I am glad that I found Pocket Sense to use with my Money files. Got the credit cards and 401K working - can't seem to find the correct bank OFX info for Harris Bank. Can anyone steer me where else I can look?

    ReplyDelete
  148. Have you tried...?


    SiteName : HARRIS
    AcctType : BASTMT
    fiorg : NETX
    fid : 7CF
    url : https://ofx.harrisdirect.com/cgi/ofxmoney
    bankid :
    brokerid :
    appid :
    appver :
    mininterval :


    -Dan

    ReplyDelete
  149. also...
    bank id will be your routing number

    -Dan

    ReplyDelete
  150. Looks like Harris Direct is a brokerage house, not your bank...

    Unless you know that Harris Bank has an OFX server, you may have to download those statements manually from the website. We all seem to have a few banks that don't fully cooperate with out wishes :)

    -Dan

    ReplyDelete
  151. It is a great idea to use script to download quotes into MS-Money.

    Yahoo Finance don't give quote for some mutual funds. To have quote for NBC860 (MUTF_CA:NBC460), I use Google Finance instead of Yahoo Finance.

    Is it possible to modify the script to download quotes from Google Finance instead of Yahoo Finance?

    ReplyDelete
  152. I am currently still getting online downloads thru my Money Plus Deluxe, however my accounts are now with PNC bank and things have not been as smooth as it was with National City. I am considering setting up the Pocketsense scripts now and I am wondering should I install everything and use with my current version of the software or load the sunset version and then set up pocketsense? Thanks for all your time and efforts for all our sakes.

    ReplyDelete
  153. I have not seen a generic programmers API for the Google Finance service. They do have a "portfolio interface", but it isn't useful for quotes. Yahoo! has an open API for requesting structured quote data.

    ReplyDelete
  154. Regarding "Money Plus Deluxe" vs the "sunset version", I'd continue using what you have (I am). I'd grab a copy of the Sunset Version and keep it somewhere for your next computer, as it will never require activation.

    ReplyDelete
  155. I have gotten the test to work correctly for every account howeverI am not sure what to do next. After I hit YES to test the account, my open Money program appears on the screen and then nothing happens. I am not prompted to do anything like associate an account with the downloaded information as you described in the instructions. Any ideas what I am doing wrong?

    ReplyDelete
  156. Open Money first, and then run the "test" feature. It shouldn't matter, but it is a place to start. I suggest you make a backup copy of your Money file before testing.

    ReplyDelete
  157. Robert, I do have my Money program open. I run the test, then Money home page appears on the screen and nothing else happens. It must recognize that I have downloaded the file for Money and so it switches to the money program but then I don't know what to do from there. The program does not show that I have a statement to read and nothing appears to have changed???

    ReplyDelete
  158. It's possible that you need to reinstall Money. OFX file uploads are actually handled by another program that ships with Money. It gets associated with the OFX file during installation, but can get changed by another program. If you've installed any other money management software since Money, you definitely need to reinstall Money.

    If you go to the .\xfr subdirectory and double-click on one of the .ofx files, it should import to Money. If Money activates when you do this, but the file doesn't load, then try it manually.

    Manually: Go to File->Import->Downloaded Statement. Find one of the .ofx files and open it. If nothing happens, then I suspect something may be wrong with your site or account settings. If so, look into the .ofx file with a text editor and see if it has transaction information. Transactions will have dates, amounts and descriptions.

    ReplyDelete
  159. Thanks, Robert. I'm sure that is it. I loaded Moneydance and checked it out and the first time I ran the Getdata, it loaded it to Moneydance, so I uninstalled that program thinking that would solve the problem. I will reinstall Money and give it another try. BTW, Moneydance is not bad. If this doesn't work out, I probably will give Moneydance a shot at replacing Money.

    ReplyDelete
  160. Report back on the result. You're the second person to report this issue when running and then uninstalling Moneydance, so I may add a blurb about this on the setup page if it's the culprit.

    ReplyDelete
  161. I reinstalled Money but that did not solve the problem. I believe that is because I changed the default install location to drive (E)on my hard drive. I uninstalled again and tried installing the Money program in it's default location (C), and that solved the problem. Can't seem to get my Janus Accounts to download right. Anyone have any luck with Janus?

    ReplyDelete
  162. Everytime I download a transactions from Janus, Microsoft Money throws up an error saying "The file you attempted to import appears to be invalid or contains corrupt data." Any thoughts?

    ReplyDelete
  163. Open up the Janus ofx file in a text editor and see what's there...

    Moneydance does indeed mess up the ofx file associations, so reinstalling Money is the easy solution rather than messing with the registry editor.

    -Dan

    ReplyDelete
  164. As Dan suggested, the first step is to look into the OFX file for clues. I noticed that the URL for Janus funds at http://www.ofxhome.com/index.php/institution/view/615 is long and complicated. If this is the one being used, verify the copy/paste into the Sites.dat file, with *no* line breaks. It must all be on one line, and it *is* CaSe sensitive.

    ReplyDelete
  165. I'm a newbie to your scripts but am having a problem. I add an American Express credit card account, test it and everything works. But if I then list the accounts I've added it's not there. I've added it 3 times and it disappears each time. Any idea what I'm doing wrong? AMEX still appears in my sites.dat file, just the specific account disappears. Thanks.

    ReplyDelete
  166. Ken,

    How many accounts do you have? I had concerns if the count exceeded 9 or 10, but I thought Robert had addressed that in his latest version.

    I think you are the same Ken that stated you had 35 accounts on my Blog, and the answer might help Robert figure this out...

    Ameridan (Dan)

    ReplyDelete
  167. Where did you get your Janus URL Information? Based on the OFX Home Directory, the Janus URL is:
    https://ofx3.financialtrans.com/tf/OFXServer?tx=OFXController&cz=702110804131918&cl=50900132018
    It also does not give an ID or ORG. Do you have this information to go with your URL information?

    ReplyDelete
  168. Vic: I'm assuming you are referring to the link in the comment above? If so, that's the URL pointing to the OFX Home Directory entry for Janus. I do not have any other info for Janus (as I don't use it), but the ID and ORG fields aren't required by the scripts themselves. If the Janus server requires more info than the OFX directory shows, then you may have to ask Janus to learn what they are.

    ReplyDelete
  169. Ken: Any success on adding the extra account? If not, how many accounts do you have? The Setup routine should support a limitless number of accounts, but there may be a bug that you've tripped upon?

    ReplyDelete
  170. I have the scripts working with Fidelity, except for one minor issue. The investment transactions are being posted 1 day before they actually occur. The cause of the problem, as I understand it, is that

    Fidelity uses

    20100730000000.000[-4:EDT]

    for the trade date. Notice the time is set to zero.

    Reading a post at

    http://www.cdnbusinessdirectory.com/microsoft-money/4177-my-stock-transaction-date-off-1-day

    One way this happens is if the broker sets the time field in the OFX download dates to zeros, or omits the code-- which strangely defaults to zero=midnight. That corresponds to midnight. Now if the broker makes the timezone be set to eastern time, that transaction dated november 1 (implicitly midnight) EST, your computer set for PST will see that as 9 PST on October 23. I wish Money would not work that way, but it does. And the potential for this is defined in the OFX spec.

    The time field needs to be set to 7 AM eastern which will get Money to record the correct date.

    Does a "scrubber" exist for this date problem?

    ReplyDelete
  171. Ken here. I've been out of touch for a while due to moving and having to concentrate on more pressing issues. Just wanted to let Dan know I've been able to get just about everything working, including all my Fidelity accounts. Just to have that up and running means I'll be able to continue using Money once Microsoft's servers go dark. I've now added all the obvious accounts to setup.py but I'm stumped on a few. Without having to sign up and pay Chase I've been able to auto download my savings account transactions and Home Equity Line transactions into Money. In the OFX Home Forum I only see an entry for Chase credit cards. Anyone have a clue what the parameters should be for Chase Bank and Chase home equity line accounts? It's not a big deal but it'd be great if I could add them in. I've tried using the Chase credit card entry with no luck.

    Even though I'm the quintessential end user and have no experience with Python, the scripts have worked perfectly and the Pocket Sense writeup was easy to follow. If more people were aware this all existed you'd have a ton of users.

    Thanks!

    Ken

    ReplyDelete
  172. I have written a Fidelity zero transaction time script and added a call to it in ofx.py. I ran it and the time went from 000000 to 100000. I figure that is a large enough time change to put the time past midnight. I will need to wait until some transaction post to be sure it is working. If interested, please tell where to send the changes.

    ReplyDelete
  173. MikeV: If I understand what you are saying correctly, changing the time to anything other than midnight "fixes" the timestamp issue in Money. I'll add this to the to-do list for the scripts (no need to send anything). Odd that Microsoft would tick it "backwards" rather than use the actual date. If you don't mind, test it with the time-stamp set to 000001 (rather than 100000). This should be 1-sec past midnight.

    ReplyDelete
  174. Robert,

    I believe the OFX standard default is 120000, which is noon GMT. I don't think 000001 would cut it because you're also subracting another 5 - 8 hours from the GMT.

    Dan

    ReplyDelete
  175. Robert, I have a very clean script that is based on scubber.py. Be most happy to send it to you or post it here.

    Robert, Dan -- from Microsoft

    If a date does not have any hours or GMT adjustment values, SBA interprets the time of the statement as midnight GMT. Since midnight GMT is 5 hours before midnight EST, SBA would interpret a 20050505 as 7 PM on 5/4/05 if the system time zone were EST. In this case, SBA would display the date as 5/4/05, rather than 5/5/05.

    Additionally, if the date includes a datetime designation such as 20050505050000 for a server in EST, SBA converts the date to GMT, and then converts it back to the LOCAL time zone (not the server's). If a customer were connecting from the Pacific coast, SBA considers the time of the transaction to be 9PM 5/4/05, which is one day earlier than expected.

    Microsoft recommends that servers either send server time in full datetime format or send dates with a datetime format that equates to Noon GMT, such as CCYYMMDD120000. With this format, SBA displays the expected date for almost any time in the world. In the example above, a 20050505120000 would always display as 5/5/05 anywhere the world except for the center of the Pacific Ocean.
    Microsoft recommends this for the following tag values:
    • DTASOF
    • DTDUE
    • DTEND
    • DTPMTPRC
    • DTPOSTED
    • DTSTART
    • DTXFERPRJ

    That means 120000 GMT will be 0600 or 0700 central time. The script changes midnight GMT 000000 to noon GMT 120000 in the DTTRADE field. I will need some transactions to import the converted ofx files, but it makes sense that it should work. I shall post results later this week as month end transaction are processed.

    Mike

    Mike

    ReplyDelete
  176. Thanks for sharing that info Mike, and offering your code. Thinking this through, I don't see a situation where adjusting the following time conditions for downloaded statements would harm anything (if I'm thinking about it right):

    1. Time not provided (CCYYMMDD)
    2. Time = 000000 (CCYYMMDD000000) and no time adjustment value given. Example: [-5:EST]

    Is this the logic you employed?

    ReplyDelete
  177. I did a fix specifically for DTTRADE in Fidelity:

    # fid_datefix.py
    #
    # Fix zero time field in Fidelity transactions
    #
    # cleanup the file time if needed
    # called in ofx.py
    # fid_datefix.fixdate(ofxFileName)
    # else:
    # status = 0 #no ofx file?


    import os, sys

    def fixdate(filename):
    print 'Fixing Fidelity Trans Date ', filename
    f = open(filename,'r')
    ofx = f.read() #as-found ofx message

    ofx_final = '' #new ofx message

    #loop through and replace every DTTRADE TIME value with a modified version
    p0=0 #text window offset
    p1=0 #text window end

    # 20100730000000.000[-4:EDT]

    while p1 > -1:
    p1 = ofx.find('', p0) #find the next fitid field
    if p1 < 0:
    break #no more fields... get out

    p1 = p1+17 #include the DTTRADE tag + date in the text window

    ofx_final = ofx_final + ofx[p0:p1] #write the window to the new message

    ofx_final = ofx_final + str(12) #write a 12 at beginning of TIME field

    p1 = p1 + 2 #move the window offset to the start of the TIME + 2 field

    p0 = p1 #move window to start of next field

    #end while (input file)

    #write the remainder of the file
    ofx_final = ofx_final + ofx[p0:]

    #close the input file
    f.close()

    #write the new version to the same file name
    f = open(filename, 'w')
    f.write(ofx_final)
    f.close
    return

    In ofx.py - these added/changed

    # 01-Aug-2010 MTV
    # - Fix Fidelity zero transaction time

    import getpass, scrubber, site_cfg, fid_datefix

    #cleanup the file if needed
    scrubber.scrub(ofxFileName, FieldVal(site, 'url'))

    #set Fidelity zero time to non-zero
    fid_datefix.fixdate(ofxFileName)

    else:
    status = 0 #no ofx file?

    ReplyDelete
  178. FYI, I imported some new transaction today from Fidelity. They came in with the correct date! So far, both databases are tracking smoothly. The only difference I have seen is the non-online database tries to match investment transactions with existing transactions (typically last month's). I have looked at the settings of the online and non-online database and do not see any differences. Anyone know of a setting that would control this feature?

    ReplyDelete
  179. Mike,

    I haven't tested the code, but just skimming through, it looks like you've applied this to all statements (i.e., not just Fidelity). You overlay a "12" in the hour column, leaving the rest untouched. It makes the assumption that there is a time value (which could be null I think). It seems to me that this change should only occur if the the existing hhmmss portion is 000000, since most servers seem to provide usable date/time values (rather than midnight).

    To me, if the closing date/time is midnight in California, then it really is 3 am on the next day on the East coast, and should be recorded that way. Since closing will never be midnight for any real market exchange, making the presumption that 000000 equates to sometime "earlier in the day" makes sense.

    ReplyDelete
  180. I wrote this for my use with Fidelity rather than as a general routine since the only statements I receive are those from Fidelity. All my other accounts have a web option for downloading (such as Discover, Chase, and so on). However, since it only looks at the DTTRADE field it probably does not matter. Fidelity always includes the DTTRADE time as 000000. If a server included the time as null, then that will also be interpreted by Money as midnight GMT and the routine would not work. Setting the time to noon GMT will work for all time zones except Hawaii and other Pacific areas. It definitely needs a little more tweaking to make it more robust. Yet it is working great for my transactions.

    ReplyDelete
  181. Thanks Mike. Just wanted to verify my understanding of what you were doing. I will add a global routine to scrubber.py that does this for specific situations (e.g., 000000 and NULL time stamps).

    ReplyDelete
  182. I not a techie and need help. I downloaded the pocketsense file and am just following your instructions. I've done step one under configuration, but in step 2, I don't see a sites.dat file in the directory. I have template file called "sites," but it won't let me edit. HELP!

    ReplyDelete
  183. Just to verify: You have installed ActivePython first?

    - Run Setup.py by double clicking on it. This will create your local sites.dat file.
    - Exit Setup.
    - Right-click the new "sites.dat" and select "edit". If the "edit" option is missing, select "open with" from the right-click menu and choose "Notepad".

    ReplyDelete
  184. I did all that but it didn't work, but now I did it again, and it did. Don't know why, but thanks.

    You will be hearing from me again (either with additional questions or a big THANKS!).

    ReplyDelete
  185. Went thru the ActivePhyton install and the Pocketsense setup. Pocketsense is a thing of beauty, and for me it works like a charm.

    Many thanks

    ReplyDelete
  186. Appreciate the positive feedback (really).

    ReplyDelete
  187. I just installed ActivePhyton and the Pocketsense. I added all the ticker symbols for the U.S> and Canadian Stocks. However, I am facing a challenge with adding symbols of Canadian Mutual Funds. Can someone help? For example I tried searching the symbol for TD Canadian Bond Fund on Yahoo Finance (Canada) but the search returns with no selection.

    ReplyDelete
  188. Interesting question re: mutual funds outside the US. If you download statements from your Investment Institution (e.g., Vanguard), the ticker symbols for mutual funds that *you own in an account* will update from the statement. Are you wanting to watch Mutual Fund prices that you do not own in an account, or for an account that you cannot retrieve statements? If so, could you give an example CA fund symbol?

    ReplyDelete
  189. Thanks Robert for getting back to me.

    Lets take the Canadian Mutual Fund offered by TD Bank in Canada - TD Canadian Bond Index-e. Symbol TDB909. I am unable to get the Yahoo Finance's corresponding symbol even on the Canadian site of Yahoo Finance. I understand the program updates the quotes from Yahoo Finance site - right?

    Unlike the U.S., not many Canadian Mutual Fund companies offer download functionality.

    Looking forward to your suggestion.

    ReplyDelete
  190. I verified that Yahoo! does not support Canadian mutual funds. They do support Toronto Exchange stock quotes, but not funds. I'm going to look again to see if another open provider has an "open" quote API that we could use in addition to Yahoo. Stay tuned...

    ReplyDelete
  191. That was my finding too. The most recent quote was from 2004 for tdi*cbf.to which is the symbol for TD Canadian Bond Index Fund.

    -Dan

    ReplyDelete
  192. Time for a new comments thread. Go to the following link to continue discussions.

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

    This thread is being closed.

    ReplyDelete