Sunday, November 14, 2010

Replacing Microsoft Money (continued... Nov-2010)

This is a continuation for discussing the PocketSense python scripts.  Please post any issues, bugs or comments.  Positive feedback is appreciated just the same as bug reports!  For information about the scripts themselves, and for installation/configuration instructions, go to: http://sites.google.com/site/pocketsense/home/msmoneyfixp1

280 comments:

  1. Robert, what is the status on the Canadian Mutual Funds automatic quote updates via getdata? We are nearing the MS Money sunset and would love to see this resolved too. Thanks,

    -Sam

    ReplyDelete
  2. Hi Sam. My foray into it was a bad experience. When I first looked at Google finance, I found an XML interface to their quote engine. As they appeared to support CA funds, I wrote a parser for it. Then... I found out the XML interface didn't actually support CA funds, only STOCKs and ETFs. An unfortunate oversight on my part up front, or something changed while I was writing the parser. Worse, their XML output was inconsistent, providing timestamps in varying formats depending on whether exchanges were open or closed (maybe more flags than that). I then moved to scoping out a screen parser for the Google GUI interfaces, as they do appear to support CA FUND quotes. There were inconsistencies there too, so I paused. It was going to require more time than I had.

    I haven't given up, but it hasn't been a priority either. If anyone has time to hack out a reliable html parse logic for the Google Finance site (e.g., http://www.google.com/finance?q=TDB909), then I could implement into the scripts pretty quick. Note, however, that the html output may change by "time of day"...

    ReplyDelete
  3. Robert, thanks for the change, but it doesn't fix the Schwab issue. Schwab doesn't send any TZ offsets in their datetimes. The problem is Schwab sends a YYYYMMDD datetime for the current date, without a time, in the DTSERVER and DTASOF fields, and in DTPRICEASOF fields for unpriced securities (e.g. bankrupt companies). This current date is for the server, which appears to be in Eastern time. When that is tomorrow from the client timezone, you'll get a price update from the future and hit the problem in the Portfolio Manager and Account Summaries.

    Anyway, I've made a fix for this in scrubber.py. If I see a date field with tomorrow's date, as YYYYMMDD, I replace it with the current date and time. I also added debug code to copy the original OFX file to a .backup before any scrubbing happens. Here are the diffs, which hopefully aren't too trashed by the commenting system:

    10c10,11
    < import os, sys
    ---
    > import os, sys, shutil, time
    > from control2 import Debug
    12a14,17
    > if Debug:
    > # save original OFX in a backup file
    > shutil.copy2(filename, filename + '.backup')
    >
    104c109,113
    <
    ---
    >
    > now = time.time()
    > tomorrow = time.strftime("%Y%m%d", time.localtime(now + 86400))
    > today = time.strftime("%Y%m%d%H%M%S", time.localtime(now))
    >
    132c141,147
    < datetime = datetime[:8] + '120000'
    ---
    > #If nulltime is given with tomorrow's date, replace with current date
    > #and time (assuming this happens thanks to timezone mismatch between
    > #server and client)
    > if datetime == tomorrow:
    > datetime = today
    > else:
    > datetime = datetime[:8] + '120000'

    ReplyDelete
  4. Hmm, indentation got removed. You can recreate it easily enough, but if you want, shoot an email to "pml dash pocketsense at lucido dot net" and I'll reply with the file.

    And thanks for creating and releasing these scripts. I was dreading the idea of moving 18 years of data to Quicken.

    ReplyDelete
  5. Phil: I thought on this some more, and reviewed the reasons/logic used when implementing the date/time "scrubber" routine. Looking back, it appears some providers implement their time logic differently.

    The "starter" for this issue was that Fidelity was sending quotes for day x, but it was recording for day (x-1). They recorded the time as 000000[-4:EDT] for day x, but then this would interpret as day x-1 on the west coast. You have the opposite issue, in that the server is sending the a date/time combo that *would* translate correctly in the west... but not for an east-coaster. In all, it sounds like your logic of simply forcing any date later than "today" be equal to "today" isn't a bad approach. I'm hesitant, only because I didn't like globally changing timestamps to begin with. Every "fix" for one location has the potential to "disturb" someone in a different location. You're the first to report it, but here we are.

    I'm tempted to handle this in a different way altogether. Rather than a time scrubber, maybe implement a "site-specific time offset". The variable would allow users to correct time discrepancies for a specific provider, relative to their location. This would be a user-defined parameter, so it's guaranteed to be accurate *for them*, but it requires effort *by the user*.

    Can anyone think of a situation where the *automatic* logic would fail? i.e.,

    If time omitted or is equal to 000000: Set time with GMT 120000
    If resulting date is greater than today, then: Set date equal to today.

    The case I see where it could fail would be if the provider really did want the date to be "yesterday", and had adjusted the time to be so on *your* computer. A site-specific offset would be more foolproof, as each user/site combo becomes a variable.

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

    ReplyDelete
  7. Re: ticker error message. What are the ticker symbols you tested?

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

    ReplyDelete
  9. No "site" required, just an empty *investment* account to receive the quotes. The .L indicates the London Stock Exchange, correct? If so, I believe the Yahoo quotes are in "pennies". Do you currently have these same symbols defined elsewhere in Money, but possibly in pounds? It shouldn't matter, but if the value changes by a factor of 100 it could raise a flag in Money. Comment out the .L symbols and try just the US exchange and see what happens. Add one at a time and see what happens... maybe even try a .L stock that you *don't* already have listed inside Money.

    ReplyDelete
  10. I have been looking at what it would take to allow PocketSense scripts
    to request more than one account in a single request for a multi-account
    OFX file. Separate requests work very well, but multiple requests would
    be handier for many users.

    I looked at the code, which required learning a little Python. I think
    I could modify ofx.py to make the request. The main problem would be
    to get the account number for each account to the client.invstQuery()
    function. If acct_num could be an array (or a string that might have
    numbers in a string separated by a special characters), then it should
    be fairly easy to create the single request.

    Alternatively, there could be a FieldVal of INVSTMT_MULTI. (I am thinking
    in terms of investment accounts, but the process could apply to banks.)

    My style would probably be less crisp than what is there already.

    Here is a truncated description of a request for information for
    a request for data for a single account from a broker:

    OFXHEADER:100
    DATA:OFXSGML

    pre-header stuff

    &LT;OFX&GT;
    &LT;SIGNONMSGSRQV1&GT;

    broker stuff including password

    &LT;/SIGNONMSGSRQV1&GT;
    &LT;INVSTMTMSGSRQV1&GT;
    &LT;INVSTMTTRNRQ&GT;

    account specific stuff including account number

    &LT;/INVSTMTTRNRQ&GT;
    &LT;/INVSTMTMSGSRQV1&GT;
    &LT;/OFX&GT;

    --------------------------------------
    Here is a similar request except it is for three accounts:

    OFXHEADER:100
    DATA:OFXSGML

    pre-header stuff

    &LT;OFX&GT;
    &LT;SIGNONMSGSRQV1&GT;

    broker stuff including password

    &LT;/SIGNONMSGSRQV1&GT;
    &LT;INVSTMTMSGSRQV1&GT;
    &LT;INVSTMTTRNRQ&GT;

    account 1 specific stuff including account number #1

    &LT;/INVSTMTTRNRQ&GT;
    &LT;INVSTMTTRNRQ&GT;

    account 2 specific stuff including account number #2

    &LT;/INVSTMTTRNRQ&GT;
    &LT;INVSTMTTRNRQ&GT;

    account 3 specific stuff including account number #3

    &LT;/INVSTMTTRNRQ&GT;
    &LT;/INVSTMTMSGSRQV1&GT;
    &LT;/OFX&GT;


    So I could work on prototype code for ofx.py, but I would like
    guidance as to how I would expect the account numbers and account
    count to be incoming. I could just hard code in the numbers for my
    testing. Or I could use setup.py to enter a long string with the
    account numbers separated by '~' or some such. Maybe somebody who
    would find this less challenging than it will be for me will run
    with this.

    ReplyDelete
  11. My first try had the SGML tags totally missing. I deleted that. So I tried using the 4-character escape characters for greater-than and less than characters. That did not work so well. I hope it is readable enough for those who might have waded into the post which was already pretty hard to follow.

    ReplyDelete
  12. Cal: If we can get multiple accounts into one statement, then that would be beneficial to folks who have 2+ accounts at one provider. What I had really wanted to do when I first looked at the same issue was to put *all* brokers/banks/brokers & statements in one file. That was too aggressive, and apparently not possible.

    We could implement what you've posted pretty easily though. Shoot an email to pocketsense1{dot}gmail{dot}com with the full OFX message request format and your thoughts. It's about impossible to do it on this board.

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

    ReplyDelete
  14. It seems that the UK method of quoting in pence, rather then pounds / major currency units is also going to cause problems in my multi currency setup.

    Do you think I should hard code a GBP version of your download scripts to divide by 100 as there doesn't seem to be a way around this in Money that I can easily find.

    ReplyDelete
  15. Robert: re the TZ Schwab issue. I thought some about problems just replacing the date globally. That's why I'm only replacing dates with a null time and no time offset, and a date of tomorrow (i.e. must be simply YYYYMMDD for tomorrow). That at least limits the scope. Not sure where else you'd see a time in your future like that, but I really only have access to Schwab and Fidelity OFX samples. I notice Fidelity at least uses full datetimes with milliseconds and TZ offsets, so my change would leave them alone. I am curious whether Schwab ever sends a real update from the future. I've noticed, logged into their normal website, I occasionally see activity in the future, for instance, cash rolled into the sweep money market fund on Monday for a dividend received on Friday, when it's still just the weekend. I'd be curious how that shows up in the OFX, and will keep an eye out for it. Hopefully, that would use a full datetime, with non-null time. Note that Schwab never seems to use TZ offsets.

    I'd be OK with a site-specific time offset, but it would be somewhat painful to explain to users, especially with the way you scrub null times to 12 noon. Not sure how you'd have the time offset interact with that.

    You mentioned automatic logic of (1) scrub null or 000000 times to 120000 GMT then (2) if time is later than today, force back to today. That's different than what I'm doing a bit. My logic is more restrictive: if time is a null time for tomorrow, use the current time, otherwise if it's a null or 000000 time, scrub to 120000 GMT. And actually, I'm not sure you're really scrubbing to 120000 GMT. I suspect Money is treating datetimes without a TZ offset as localtime, not GMT.

    Another possibility is to do my version of scrubbing only when scrubbing a Schwab OFX.

    ReplyDelete
  16. Ouroboros: I only use US Dollars, so I have no experience on currencies. You're saying that Money doesn't support pence? If not, then we can add a routine to adjust .L quotes to pounds (i.e., divide by 100).

    If you feel comfortable doing so, then feel free to do it. I'd appreciate it if you shot a copy to me to include in the distribution. If you don't feel comfortable doing it, I can... but it may "be on the list" for a bit.

    ReplyDelete
  17. Hi Robert,

    I'll give it a shot if it finds 'GBP' in the currency label. I know it seems strange to use both USD and GBP, but that's one of the reasons I cant migrate to Quicken and the like - only MS Money provided consolidation of different broker currencies.

    ReplyDelete
  18. Ouroboros: Before heading down that road, shoot me an email to discuss offline: pocketsense1{at}gmail{dot}com

    ReplyDelete
  19. I'm not sure if this will affect US stocks, but for anyone downloading UK stocks, Yahoo sometimes doesn't update the (SECNAME) field for all stocks. If this happens you will get a corrupt file error in Money as it will not import this as blank.

    The solution is to manually edit the name in the OFX or add an exception in the quotes script.

    Money still works great with these scripts!

    ReplyDelete
  20. I've noticed that with US exchanges a couple of times too. I have a "to do" to add a "check for missing parameters" before sending quotes to Money.

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

    ReplyDelete
  22. If a date/time string is missing the time, then Money interprets it to mean "midnight GMT", and then adjusts to the local timezone. For someone on the east coast, this equates to 7pm on the previous day. I didn't bother with it, since I don't like the method to begin with. My plan is to replace the date/time with an actual quote date/time instead. Maybe in the interim, it would be better to use a timespec of today@12:00:00. That should work anywhere in the world.

    ReplyDelete
  23. For my ticker download uasge, I've removed the tomorrow calculation on (DTASOF) and Money seems to use this as the datestamp when you look at the manual investment price update form.

    (DTPRICEASOF) seems to be ignored (for ticker updates at least) as the 'last Updated' column, that no longer works in Money, seems to be the place this detail of info would be displayed

    ReplyDelete
  24. So...changing the user defined url to: http://uk.finance.yahoo.com appears to set the scripts to run against the UK site - TERRIFIC!
    HOWEVER...Funds and stocks with a .L suffix do not appear to load. .L indicates the London exchange - where nearly all the UK prices are quoted.
    SO...nearly there, but would appreciate any help available to take a look at this.
    THANKS SO MUCH FOR A TERRIFIC SOLUTION.

    ReplyDelete
  25. UK stocks should work (also from the Yahoo US site). I made the following changes/quick hacks and it works great:

    Quotes.py:
    Line 41
    currency = "GBP" #Ouroboros: changed this to GBP to USD

    Line 118
    self.price = str(float (quote[1])/100) # Ouroboros : divide by 100 change for UK

    Line 117
    _field("HELDINACCT", "OTHER"), #Ouroboros : changed from Cash

    Line 175:
    _field("MKTVAL", "0"), #Ouroboros : this has to be 0 for ticker update

    Line 181:
    tomorrow = datetime.datetime.today() # + datetime.timedelta(days=1) Ouroboros: ignore tomorrow to get todays timestamp

    Line 223:
    if len(name) == 0 : #Ouroboros: quick hack to assign it to something if yahoo sends this through empty
    Line 224:
    name=ticker
    Line numbers may be slightly out and note python indentations have to be spaces rather than tabs and it affects the logic (keep them as they were)

    UK funds will not work. This would have to be downloaded from the Yahoo web pages directly. See discussion on Microsoft Social Money forum

    ReplyDelete
  26. That's very helpful, thank you.
    I do wonder if there is a way to source UK Funds though, that's what I'm really after.

    ReplyDelete
  27. John: One of the updates that will be in the next beta will be better support for currencies and conversions. It will be simple, and will require the user to enter appropriate parameters... but it should be flexible.

    Re: the use of the uk.finance.yahoo site, I'm not sure if/why it uses different symbols. No experience on my part.

    ReplyDelete
  28. To all: I have not worked much on the scripts over the past few months due to "real life". It is difficult to find spare time (especially during a holiday season), but I'm going to start digging into the scripts this week. I have a few meaningful updates to implement. Most of the work involves "deciding how to handle it" (which is mostly done... I hope), while coding itself goes pretty quick.

    ReplyDelete
  29. Thanks Robert.
    UK Funds appear in the uk.finance.yahoo site, but somehow the current quotes.py does not import them.
    There must be so many UK users who'd die for this to work, so it's a plea to anyone with the inclination to help us stranded souls.
    You work is terrific, many, many thanks!!

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

    ReplyDelete
  31. That's good to know. But a bit frustrating.
    I will post something with Yahoo too, but I suppose we have slim hope.
    Thanks so much for taking the trouble to reply.

    ReplyDelete
  32. will these scripts work on a linux box?

    ReplyDelete
  33. Thanks for Pocket Sense. It's much more convenient than updating quotes in MS Money manually. As suggested I created a dummy account "Tickers". I'm unclear on how to associate this account with Pocket sense. I just use setup.py and test the quotes download, which works OK and sends to Money. I need to link the Tickers account with getdata.py.

    ReplyDelete
  34. Money should ask which account the OFX files will go into the first time for each account. If the quotes are coming in, you are probably just fine. Hopefully Money asked the first time, and has remembered.

    ReplyDelete
  35. Thanks a lot for this!!!
    If a web site provides OFX downloads (but not via MS online services), is there a way to find OFX data for sites.dat? I have a couple of credit union accounts that do this.
    Thanks again.

    ReplyDelete
  36. I have a page on my Blog with several links to help in this regard.

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

    ReplyDelete
  37. I guess I can't see the forest for the trees... Edited Sites.dat, no problem... Ran Setup.py, and tested the accounts.... Ran Getdata.py... It runs successfully, says it sent data to Money, but I can't find it or see any changes.... Nowhere did setup.py or getdata.py ask me to associate an account in sites.dat with an account in Money... That's apparently the issue... What do I do to fix that ????

    ReplyDelete
  38. Any chance that you have installed another money management software package (e.g., Moneydance)? If so, it may have redirected the OFX uploads to another place... or somehow caused the OFX file to *not* be associated with Money. Reinstalling Money fixes the issue, if it's the culprit.

    ReplyDelete
  39. Your OFX files should show a MS money icon if correctly associated with Money. If they are, try deleting your Tickers Account and recreating it before the next upload 0 it should all re-associate.

    ReplyDelete
  40. I tried installing MS Money Sunset PLus...and Activepython 3.1.2.4, and the scripts. I couldn't figure out how to run the scripts...and just went back to my 2007 business edition.

    The instructions at pocket sense seem to be for guys who already have a certain amount of experience, but I have no clue reading between the lines. Could someone be a little more explicit in providing more instrucitons than listed at http://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2

    Can I get by just using 2007. It's actually working fine now.

    Thanks.

    ReplyDelete
  41. How far did you get in http://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2 and which was the first step that you needed more explanation for?

    ReplyDelete
  42. 1. If this is a new installation
    ...a) Run (double click) Setup.py first.
    ...b) Select 0. Exit to exit.
    2. Edit the sites.dat file* to include your site information (required)
    3. Edit the sites.dat file to include your stock, and mutual fund ticker symbols (optional).
    4. Run Setup.py to setup your accounts.
    5. Run Getdata.py to download your statements and (optionally) stock/fund quotes.

    ReplyDelete
  43. Not that it was a part of the product I ever used much, but I see the sunset edition still has all the web links to the MSN finance pages.

    Microsoft still appear to be putting alot of effort into this. Any idea if this will also be shut down or is this one of their consumer media sections that provides revenues?

    ReplyDelete
  44. John,

    Re having to get some data from html (specifically uk fund prices)

    it appears from a quick play with the examples at http://www.boddie.org.uk/python/HTML.html that Python has quite powerful html handling capabilities.

    As I'm sunsetted'' as of today I'm going to hack something together to get hard coded values at first and then perhaps this could be integrated into a version of Robert's scripts where sites.dat also fetched stuff in this alternative way?

    ReplyDelete
  45. So glad I found your posting. I was dreading switching to another personal finance software. Great job!

    ReplyDelete
  46. CA finance.yahoo is now rejecting ^DJI. Any idea how to get around it?

    Mike

    ReplyDelete
  47. I noticed that too. Try ticker = INDU instead. Not sure what's happening, but it's looking like Yahoo! may be discontinuing support for the DJ Index via their API.

    ReplyDelete
  48. It's actually a turf battle. Murdock does own the symbol DJI as Dow Jones Index, but he does not own DJIA the Dow Jones Industrial Average which is owned by the AP. Not the first time a Murdock organization has put the screws to another group to get its way.

    Just use the symbol INDU as Robert has said. The interesting part is that if you use INDU, Yahoo sends it back with the symbol ^DJI, unless they have caught it lately.

    Wish I could post using FireFox. Going over to IE to post each time is a pain.

    Ray

    ReplyDelete
  49. Same problem over at MS Money Forum. Post with Safari and it ignores it!
    -Ouroboros

    ReplyDelete
  50. Odd... I'm posting with Firefox (v3.6). Good info re: the turf battle. Power mongers are that way...

    ReplyDelete
  51. Any help with UK fund prices very gratefully received.
    John

    ReplyDelete
  52. My quotes have not been updated since 7/18/2010 (which is the date that the quote items were added). I ran ofxanalyzer against the quotes.ofx file and got

    *** All line number references do not include header lines ***
    ***OFX 1.02 Add ~10
    ***OFX 2.0 Add ~2
    Running Data Through Parser
    HRESULT = 0
    Error Code = 0

    HRESULT = -2147450874
    Error Code = 237
    Severity = X
    Type = C
    Error Message = XCInvalid Date Data - %s (Data in pszBuffer)
    Other Info = MNYSGMPB: SGML error at (null), line 45 at ">":
    XCInvalid Date Data - \025XINVPOS.DTPRICEASOF (Data in pszBuffer)

    Current Buffer = 2

    I do not get an error message when importing into Money, but nothing changes in the watch account.

    I get errors from hleofxquotes.jar as well, but am not really worried about it.

    Where do I need to look?

    ReplyDelete
  53. Another senior moment. The values are updating (except for the ^DJI error) --- the date, which does not change, is the last transaction date. Arggg...

    Evidently the ofxanalyzer error does not impact the import?

    ReplyDelete
  54. Oh shoot ... just saw the INDU statement.

    It was the ofxanalyzer error that triggered this entire senior moment thread...

    ReplyDelete
  55. Robert (and other PocketSense users),

    From Quicken’s site, I found the following: On April 30, of each year, Quicken discontinues its support for any of its versions more than 2 years old. As of April 30, 2011, Quicken will no longer support Quicken 2008, which implies that we may have to upgrade the default in PocketSense at that time since Quicken forces financial institutions to promote Quicken upgrades by not allowing “obsolete” versions to communicate with their servers.

    I’ve observed that Moneydance is already using version 1900 (Quicken 2011), rather than version 1700 (Quicken 2008) that we’ve been using, so I’m hoping some users will try out the new setting prior to Bobby incorporating this into his next version of PocketSense.

    ReplyDelete
  56. Just realized that the default in PocketSense is 1800 currently (not 1700). I've tried out 1900 and all seems well, but the need to change is not imminent...

    -Dan

    ReplyDelete
  57. Thanks Dan. I'll add a couple of user parameters for DefaultAppID and DefaultAppVer to the next release. There's already a site-specific option to define these values for each site entry, but a global user-defined default will be a nice addition.

    ReplyDelete
  58. MS Money Plus Deluxe (ver 17) alerts you to dividend payouts for your holdings without downloading the information from your broker. Do you know of a script that has this feature? If not, is there a web site that provides and tracks dividend payout dates & amounts for investments. Thank you.

    ReplyDelete
  59. Any help with UK fund prices very gratefully received.

    ReplyDelete
  60. Just a question on the quotes - should it only be done once a day after the market closes or can it handle multiple updates in a day?

    Also does it collect (say 7 days worth of quotes) when it updates or do you have to do the update everyday if you want each days worth of quotes?

    Much appreciated!!!

    ReplyDelete
  61. Quotes are for the "last price", and the scripts can grab those all day. However, Money only accepts a single quote per day via the OFX import. Additional quotes (later in the day) that it receives for a symbol are discarded. There is no provision to back-fill the quote history.

    ReplyDelete
  62. I have used the program for about 8 months. What a great help. I updated to lastest version when it came out. I seem to be getting some errors randomly when it connects to yahoo services to get stock updates. It says it can not connect..connecting to the banks works fine. Any Ideas?

    ReplyDelete
  63. Are you using the default quote provider (yahoo.com), or are you using an alternate (e.g., ca.finance.yahoo.com). If you're not sure what I'm talking about, look at the 19-Sep revision history entry (sites.google.com/site/pocketsense/home/msmoneyfixp1/revisionhistory) for the YahooURL: option that was added for the sites.dat file.

    ReplyDelete
  64. My "program" Base currency is Canadian dollars, I run several separate accounts for my Canadian and US Dollar portfolios. Tickers for the Canadian securities are the bare TSX symbol as in Petrominerales' PMG while tickers for the American securities are preceeded by "US:" as in US:AAPL. If I switch the program base currency this becomes the Yahoo friendly AAPL and Petrominerales becomes CA:PMG.

    In either case US:AAPL and CA:PMG are not generated by Yahoo and the Yahoo PMG.TO does not import into my current setting. As the US: and CA: are established by Money I can not over ride these to change to the .TO format if the currency of the security is Cdn Dollars.

    Baring setting up a US Dollar file and a Cdn Dollar file any suggestions, maybe some lines in the script to make the format conversion when the ofx is generated? The option of two separate files for each currency doesn't really work and defeats the purpose of trying to retain MS Money as my two currency bank transactions after more than ten loyal years are so interconnected.

    ReplyDelete
  65. That brings to mind another "scrubber" routine that would take foreign exchange funds (ie Canadian) and convert them using the day's exhange rate into the base currency (USD). Since there are so many variabilities - might be best to have an example and let the user modify.

    -Dan

    ReplyDelete
  66. I currently have the following item on my "to do" list. "Add currency + multiplier option for stock quotes." That basically means, allow users to define an optional import currency and multiplier for each ticker. If I understand correctly, Grahm is asking for the ability to also define an alternate symbol (e.g., replace PMG.TO with CA:PMG?) This can be added to the same "to do", as I haven't worked on that one yet. My version of Money allows you to use a new symbol as a surrogate for an existing one. For example, when I first changed from ^DJI to INDU, Money asked me if this was a new symbol, or if it was the same as an existing one. It was the same, so I "mated them" on first import.

    ReplyDelete
  67. I am using the default setup with yahoo getting my quotes..no changes were made to the sites.dat file. Still get the random connect to yahoo errors.

    ReplyDelete
  68. any chance of getting the stock value change to work in the porfolio section when doing the downloads? Mine just says "unch"

    ReplyDelete
  69. Add the following line to sites.dat (assuming you're running the latest version) and see if it helps.

    YahooURL: http://ca.finance.yahoo.com/d/quotes.csv

    Can't update the "last price change" inside Money via OFX (to my knowledge).

    ReplyDelete
  70. I have run the script for UK stock prices today and the imported statement appears within Money BUT the existing shares are not updated with the new prices.

    I have an "import" account with all my shares recorded as a purchase, also added all the shares to the watch account, but nothing.

    The prices are definitely making it into money as they appear when you first import the statement. On the very first go, the existing tickers were all changed to include the .l on the end.

    Any suggestions ?

    ReplyDelete
  71. Well it is the current version sept 19th. Did put line in the .dat file. Bank downloads work fine. When doing stock/funds downloads is when it hesistates and says "An error occurred trying to connect to The Yahoo! service" at different times during download.

    ReplyDelete
  72. qwakuk: Not sure what the "import account" does? The way it should work is that you have your actual accounts, with symbols defined for each investment. You would also have a "tickers" account (made up name), that is an investment account with zero dollars in it. The symbols used in the investment account must be the same as the symbols sent by Yahoo... or you have to "point them to their equivalence" the first time you do an import with the unknown symbol (at least, that's how my copy of Money works).

    If you look through the comments above, you'll see one asking for an "equivalence" symbol option. I'm considering adding that. It would allow a user to select the Yahoo symbol for download, but send an alternate symbol (maybe an existing Money symbol) to Money during the import. Would that help you?

    ReplyDelete
  73. HMan: Not sure about the Yahoo! error. That specific error is only thrown when the script can't connect to the Yahoo site (timeout occurs). Issues with internet connection? Anyone else seeing this?

    ReplyDelete
  74. Robert thanks for the reply but i think i am dealing with things correctly but the prices are not matching up.

    I have numerous "real" accounts with actual transactions. Then on the first time of using the script i created a new account called "import" and put a buy transaction for 1 share into the "import" account for all of my existing shareholdings ie AV.L,HIW.L etc etc.

    Money then altered my existing tickers from AV, HIW to match them as AV.L and HIW.L etc etc.

    When i import, as part of the process i can see these new prices BUT theraafter they have disappeared and do not appear if i look in the manual price history.

    ReplyDelete
  75. Robert et al

    Thanks for the ongoing support but i have another question. I believe the software works by reading a csv file ?

    Could it read a csv file stored on my hard drive and turn that into a ofx file ?

    Perhaps i could turn that csv file into a locally stored web page, would that be helpful ?

    ReplyDelete
  76. qwakuk: Not sure? There shouldn't be any transactions in the import account, so maybe that matters? Mine is an empty account with no transactions and a balance of zero. I'd delete that account, create a new empty one (type=investment), and try again.

    Re: the csv... the import routine is specific to the results provided by Yahoo (one line csv with a specific format). As for the web page, there will be one in the next release (finally). Also, if you enable the option in sites.dat, a quote history csv file is created in the xfr directory, which stores every download. I don't use it, but someone wanted the feature.

    ReplyDelete
  77. Robert - i will remove the transactions tonight and give it another go

    RE the csv idea, I am in the UK and we have the unit trust issue. I have an online portfolio at moneyextra and can download a csv file with the daily prices for my portfolio. What i wondered was if it was possible to take these prices and convert them into an ofx file thereby resolving our unit trust / mutual funds issue for uk users.
    Can email you the csv file if you are interested.

    ReplyDelete
  78. The answer to this question may be here somewhere but I couldn't find it. What is the difference between how Money currently updates prices (and allows multiple daily updates with price changes) and using the Yahoo script? Is there some way to tap into the current method to add the additional functionality of regular price updates/changes throughout the day? Now that I've got all my account transactions updating I want it all :-)

    ReplyDelete
  79. Firstly, thank you so much for all your work spent developing this excellent solution. As someone who relies heavily on MS Money and has done so for many years this will allow me to avoid switching to Quicken or other alternatives until something capable of truly emulating MM’s power becomes available (always an optimist…)

    Although it is only price quotes that I am using, I now have Pocketsense working perfectly on MM Sunset Edition in parallel to live MM feeding all tickers in a range of brokerage accounts on multiple markets/currencies. It even works to update prices for some non-MM supported markets such as Swiss equities.

    Before I cut over my live accounts I am trying to kludge US options quotes out of the system, thus far without success. My approach was to use Pocketsense to pull the option quote from Yahoo and then use the equivalence mechanism in MM to try to link the Yahoo ticker to the MM option security name (which will not of course accept the new format US option ticker.)
    Unfortunately I fail at the first hurdle as Pocketsense seems not to find the option tickers and generates the ‘Invalid Quote response from Yahoo! Finance – SKIPPED’ error.

    Have tried with a variety of active options without success using what seem valid tickers according to Yahoo, eg:
    CSCO110319C00020000
    OPR:CSCO110319C00020000
    Using version 19Sep2010*rlc. Have tried searching all the forum blog pages but I cannot find whether this area has been covered before. Any guidance on what I am doing wrong or otherwise in getting this work-round to function for US traded options would be much appreciated.

    Again, my thanks for an outstanding piece of work.

    Bob

    ReplyDelete
  80. Bob,

    I discovered the "option" to get Options quotes and posted the procedure on my Blog, however, since the symbology changes, I've never been able to get that working again.

    ReplyDelete
  81. Ken: Money handled price updates differently. The OFX method works via the Money "import" handler, which (afaik) only supports a single quote per day.

    Bob: No idea about options. I don't trade myself, and don't really understand how Money handles them. The two ticker symbols you list above are not supported by the Yahoo Finance API... but are supported by the interactive web version. This is not unique to options, as Yahoo provides some quotes via their web interface that are not supported by their back-end API (e.g., Canadian Mutual Funds are not supported either). That said, if someone can describe the details of how they would be handled via OFX, and can figure out a reliable method to retrieve them (e.g., Yahoo)... I'm sure it could be coded into the scripts. I actually spent a bit of time looking at the Google Finance API, but had to drop it (not enough time to devote). It may offer some possibilities not available with Yahoo, but it is more complex to learn/implement.

    ReplyDelete
  82. Yes, I have the same problem since the 'Schwab' method you helpfully blogged did not work for me in MM17 either as the Symbol field will not accept 21 characters. That's true using an Investment Type of either Equity Option or Other (as the Schwab example actually shows.) Perhaps it is just v17 that does not work or something.

    That's why I was thinking it might be possible to kludge the link for option prices by having MM use the equivalence to a security type from the correct Yahoo ticker quote if I can get the OFX file populated from the Python script in the first instance. So far I've failed at that...

    Any ideas gratefully received.

    Thanks

    Bob

    ReplyDelete
  83. Robert

    Deleted the old "import" account and created a new one. It is totally empty, same procedure, try to import, prices appear as part of the import routine and then seem to disappear. Not updating the existing investments.

    ReplyDelete
  84. qwak: did the import routine ask for an account to associate the quote statement with, and you selected the new (empty) account? It just dawned on me that you were using London Exchng quotes (.L). There have been issues there: Yahoo sends quotes in pennies, the scripts assume dollars, but Money may be configured differently. I don't use any of that, but (based on feedback) have a wish/to-do list of adding "currency", "multiplier", and maybe even a "surrogate ticker symbol" for each symbol. Could this be related to your issue?

    ReplyDelete
  85. Well today did downloads for stocks without a hitch. Oh I forgot to mention that when you download and it wants to start Money, it asks twice (two popup windows) Happened after recent upgrade..

    ReplyDelete
  86. I recommend starting Money before running the scripts. If Money isn't running, it will ask each time a statement is dumped to the Money OFX handler. If you're quick to start Money on the first message, you may only get that one (before the next one gets sent over). I'm not that quick...

    ReplyDelete
  87. Robert: thanks for the explanation about the Yahoo API. Sadly I don't have the smarts to offer anything useful on this, but I'm sure that IF somehow option pricing via the OFX could be made to function using that feed as for stocks/funds etc you would make many users even happier than we are already that you took the time and trouble to develop PocketSense.

    Bob

    ReplyDelete
  88. Robert

    Yes i am in London, but same happens whether i use uk or ca yahoo servers. Have amended the script slightly (see 28th Nov post above) to sort out the pounds and pence issue.

    Created a new empty account as part of the import routine on the first go after deleting the old one. Did not have to match the securities tickers as was done previously.

    ReplyDelete
  89. qwakuk
    Have you tried my date change as suggested on Nov 28 post. I seem to remember for UK stocks it was always importing the day before.

    You can test by going to maunally update prices and delete say 3 days history. Them import the OFX again and see which date is getting updated.
    For me I had to make the line 181 change for UK stocks and my timezone.
    -Ouroboros

    ReplyDelete
  90. Ouroboros

    Thanks for the post, I have already made the changes you suggested but will double check them.

    I note that the date in the ofx file is in US format even though i am importing for the yahoo UK server ie 1/5/2011 rather than 5/1/2011. Changed Windows settings to US ie month before day and importing then, but it made no difference.

    ReplyDelete
  91. Here's some code to grab UK fund prices from Yahoo if anyone fancies integrating it with the scripts. You need to install the beautiful soup package first.

    --

    import urllib2
    import re
    from BeautifulSoup import BeautifulSoup

    page = urllib2.urlopen("http://uk.finance.yahoo.com/q?
    s=GB0000467810.L&ql=0")
    soup = BeautifulSoup(page)

    #code just to print the heading of the page for now - remove this
    titleTag = soup.html.head.title
    print titleTag.string

    #Get the parser to scan to known text in the page
    label = soup.find(text="Net Asset Value:")

    #Grab the text in td part of the HTML
    first_td = soup.find('td')
    text = first_td.find(text=True)

    print text

    ReplyDelete
  92. re: Options price updates. I do a lot of options trading and when the new symbols took effect I noticed that Money no longer updated prices throughout the day. However when I updated my accounts each morning the prices would reflect the closing data from the previous day. I came to the conclusion that when my Fidelity accounts updated they were somehow updating the position prices in the accounts as well as downloading transaction data.

    Although I'm now updating all my transaction data using PocketSense I do still get price updates throughout the day the old way. I'm going to wait until after 1/31 to see if Fidelity automatically updates prices for all positions in the accounts when I run PocketSense each morning. If so I don't plan to implement the Yahoo updates.

    I guess what I'm saying is you might want to check and see if your brokerage updates option prices for held positions. It certainly appears that Fidelity does.

    ReplyDelete
  93. re: Ken: Money handled price updates differently. The OFX method works via the Money "import" handler, which (afaik) only supports a single quote per day.

    I know Money handles price updates differently from an OFX import, I just wonder how. If we better understood from someone (Cal Learner?) the mechanism it might be possible to create an alternate means to provide updates throughout the day. For instance if we understood the data structure and methodology maybe we could point Money at a file on each of our computers that is regularly being updated from Yahoo or Google and get more than 1 update per day.

    ReplyDelete
  94. As I was looking into QFX/OFX files fetched by PocketSense, I noted a funny substitution. Here is a line:
    BATH &amp; BODY WORKS
    Clearly the code made a substitution to avoid a problem, but it also did the substitution when it was not a problem.

    The payee showed as "BATH & BODY WORKS" in Money

    ReplyDelete
  95. Correction: The payee showed as "BATH & BODY WORKS" in Money.

    ReplyDelete
  96. OK... the blog posting process is taking out my 5-character strings that are the escapes for the ampersand. I guess I should have used Preview... basically PocketSense is adding the escape sequence modifying the escape sequence that was already OK in the file as downloaded. The Payee in Money then showed the escape sequence where it should have only shown the ampersand.

    ReplyDelete
  97. >>> Ken said...

    re: Options price updates. I do a lot of options trading and when the new symbols took effect I noticed that Money no longer updated prices throughout the day. However when I updated my accounts each morning the prices would reflect the closing data from the previous day. I came to the conclusion that when my Fidelity accounts updated they were somehow updating the position prices in the accounts as well as downloading transaction data.

    Ken, that's interesting that you can somehow get the Fidelity OFX to update options prices in MM. How are you entering the option ticker in MM and which version is it? I can't get MM v17 to accept the 21-digit standard option ticker and that's why I was trying to kludge it by associating a price from an OFX using a real Yahoo ticker to a cut-down but still meaningful ticker in MM that does fit in the restricted ticker field. Did you associate the MM items to the inputs from your Fidelity OFX in some way?

    But the first problem is to find a way of getting the options pricing through the Yahoo API into the OFX as Robert explained above. I think that may be possible as Personal Stock Monitor Gold runs off the Yahoo API I believe and handles options prices very well. I use PSM just for keeping an eye on options positions but use MM as solution of record so have to duplicate transactions which is obviously not ideal.

    Bob

    ReplyDelete
  98. The quotes in an OFX file are associated with the UNIQUEID (usually the CUSIP). I don't know if you need a symbol as such for reading the UNITPRICE for a security from the OFX file if there is a UNIQUEID/CUSIP in the OFX. I may be able to make a test if it matters.

    ReplyDelete
  99. A lot of comments to consider, but I wanted to respond to the observation re: the ampersand. There are a few forbidden characters in the OFX standard, and the ampersand (&) is one. It must be replaced with the escape sequence, or Money will throw an error. Even so, the scripts do not currently replace the & symbol with an escape sequence... the OFX provider is sending it properly encoded, and it's being passed to Money. The quote script tosses illegal characters completely, rather than replacing with an escape sequence.

    ReplyDelete
  100. I think Cal is on to something with how Money matches securities. Since the change in option symbols I do enter the new symbol in Money but often it's truncated (especially if the strike price is xx.5; like 22.5). And the price of none of my option positions is updated like stocks are. But every morning when I download OFX data (either using Money update or Pocketsense) the option prices are updated to the previous day close. My conclusion is the Fidelity OFX file is comprehensive and includes price data for each position.

    I'm by no means an OFX expert and unfortunately since I can't initiate the OFX download from the Fidelity website I can't capture the OFX file and look to see what's in it. All I can do is hope that when the update function goes away after 1/31 I'll get my daily price update via the Pocketsense Fidelity OFX downloads.

    ReplyDelete
  101. Ken: My experience thus far with every other type of investment is that the account provider (e.g., Fidelity, Vanguard, etc.) sends all information necessary to record accurate account transactions. The Yahoo interface is a "work around" intended solely for tracking things that aren't held in an OFX-enabled account. If your options are held in an account for which you have an OFX interface... you should be good. More so, you shouldn't try to update those positions via Yahoo.

    There's an option in sites.dat (SaveTickersFirst) that directs the script to upload Yahoo quotes first. The reason is that someone wanted to update their accounts from Yahoo rather than the account provider (first quote received by Money is recorded for the day). I recommend that this option be set to "no", i.e., that Yahoo quotes be recorded last, unless there's a specific reason to reverse it.

    ReplyDelete
  102. Regarding the quotes in the broker's OFX , I see a problem with more than one broker. The prices are showing in Money Plus Sunset a day later than the actual. I don't see a flaw in the OFX files. Instead I suspect that the Money may be ignoring the DTPRICEASOF tag value, and instead using the DTASOF from early in the OFX file.

    In Money, to see the prices recorded for a security, you can right-click the security in the Portfolio and choose SeePriceHistory. Then UpdatePrices->UpdatePricesManually. That will give a history. Compare those prices with prices from http://finance.yahoo.com/q/hp?a=&b=&c=&d=0&e=8&f=2011&g=d&s=vgslx (enter a symbol into the "Get Historical Prices for:" box to choose a different symbol. How does the data compare for you?

    ReplyDelete
  103. PS: (I wish I could edit my posts rather than just making subsequent posts) I just realized that I am seeing this same bug on the securities that I get quotes from Yahoo, at least for the most part. This may be related to when I getdata. I am not sure about that.

    So I suggest we compare the Money history with the Yahoo history.

    I am thinking this is a flaw in Money rather than PocketSense.

    ReplyDelete
  104. Cal, when I update my Fidelity accounts every morning (whether using Money update in the past or Pocket Sense now) it updates option prices with the previous day's close. That's perfect for my needs as it gives me a portfolio value and a trackable price history. Fidelity has a very sophisticated online app I use for trading that gives me real time pricing throughout the trading day. Assuming that running Pocket Sense every morning will use the Fidelity OFX data to update security prices with the previous day's closing price then I'll be able to retain all the functionality in Money I require. I think the key is running GetData in the morning after Fidelity updates it's info during the night. If I were to run GetData after market close it would probably load yesterday's closing prices, not today's.

    ReplyDelete
  105. Robert, thanks for the info about the Yahoo interface. Fortunately I was so intent on getting my account transaction data correct that I put off worrying about quotes and was only now beginning to focus on it. With my option prices being updated daily I planned to wait until after 1/31 to see what happens when the Money quote server goes away. From what I've read in the last several posts it looks like I'll be OK and won't have a need to use the Yahoo workaround as I don't keep a watchlist in Money, only owned securities in real Fidelity accounts.

    I can't thank everyone enough for the work they've done on Pocket Sense to make it possible to continue to use Money after 1/31. I have so much data for such a long time from so many accounts I couldn't imaging losing the ability to use Money. A friend tried to migrate to Quicken and has done nothing but complain about its inadequacies. I will now set him up to be able to continue with Money.

    ReplyDelete
  106. Cal: I thought I had responded earlier, but just realized when reading the latest comments that I hadn't. The date/time stamp being used by Money for quotes isn't accurate, and I've known it since first working on the code. The problem is that quotes are "packaged" within a single account, the statement for which has a single date/time. That date/time is not currently derived from the quotes, but the system clock. I don't like that. As we don't want a separate account for each symbol, the next version will use a date/time from one of the quotes to make it "better", but not perfect.

    ReplyDelete
  107. Many thanks for the useful info and for developing these routines.

    I am trying to use pocketsense to update share prices in a UK version of MS Money 2005. When I load the OFX file into Money the load appears to complete OK and I can see the new share price in the ‘statement reconciliation’ view for the dummy account. However, the new price is not updated into the other occurrences of that share-code in other portfolio accounts.

    The only way I could force it to update was to set the Units to 1, in which case Money would then offer to update the share details by adding the 1 unit into the dummy account. This then also updated the share price for any other accounts with that share code. If I look at the history for the share prices they are listed each day as “Online” except for the latest one with the new price from OFX which is listed as “Buy”.
    The problem with this is that I have to change the unit count each time an OFX is loaded to force the update which then leaves me with a load of ‘false’ shares which confuse the portfolio totals.

    Has anyone else managed to use these routines with a UK version of Money 2005 ?
    Thanks,
    Veronica

    ReplyDelete
  108. Veronica

    I am also using the UK version of Money 2005 and have the same problems as you.

    I have far more unit trusts than shares so it will only be a part help if i can get it to work correctly

    ReplyDelete
  109. Veronica: I'm finally in the process of coding some "to dos" (catching up). The foreign funds issue has raised it's head numerous times, but I'm not focused on it yet. I thought I understood the problem, but your post confused me. You mention using "units=1", but no mention of currency or multiplier. I don't think that fooling Money into adding shares is the right way to go.

    As of now, I'm intending to implement a simple user configuration for stocks/funds. The change would allow a user to define the currency, a "multiplier", and an alternate ticker symbol. For a UK symbol, these may be units="pounds", multiplier=0.01 (pennies to dollars or pounds), and AlternateSym=ExistingMoneySymbol. Won't be long...

    ReplyDelete
  110. PocketSense Sites Problem: I am trying to set up PocketSense and am having a problem getting Setup.py to display the Sites information. I have successfully added some Sites to sites.dat, and then used Setup.py to add and test some accounts. However, when I exit Setup.py and go back into Setup.py it sometimes no longer displays the Sites even though they are still in sites.dat. I am using Windows Vista, and have reinstalled the PocketSense scripts a couple of times to get around this, but I cannot figure out why I am having this problem.

    ReplyDelete
  111. PocketSense OFX File: Is there a way to see the OFX file that PocketSense creates and sends to the financial institution? I can view the OFX file stored in the xfr folder when there is a problem, but it would be helpful to see what is being sent to verify that it is working the way I expect and compare it to what Microsoft Money is sending.

    ReplyDelete
  112. OFX APPID and APPVER: Microsoft Money 2007 creates OFX files with Money and 1600 whereas the PocketSense defaults are the values used by Quicken values QWIN and 1800. Should it make any difference what I use for OFX downloads to Microsoft Money 2007?

    ReplyDelete
  113. phwhite: Not sure about sites showing inconsistently in Setup? Setup lists your accounts (not sites). Sites are only listed when *adding a new account*.

    If you enable debug in control2.py, you'll get extra info dumped to the screen (including the ofx request message).

    You can define alternate values for appid and appver for each site entry in sites.dat. The next update will allow a user-defined global default for appid and appver.

    ReplyDelete
  114. Should have added: if you enable Debug, use the "test account" option in Setup. If you run Getdata with multiple accounts, you'll be swamped with screen dumps.

    ReplyDelete
  115. Debug mode & Setup problem: Setup started working properly when I turned on debug in control2.py. The problem I was having was that Setup was not displaying accounts or sites regardless of which Setup option I selected. I first encountered this when I was trying to add an account, so what I saw was that Setup was not displaying sites. Later I saw that it also was not displaying acocunts.

    Turning on debug mode helped me see what my problem was. This is a very useful feature that would be handy to have in the PocketSense installation instructions.

    ReplyDelete
  116. Account not found: I am trying to use PocketSense for a situation invloving multiple accounts at a credit union. The way this works with Microsoft Money is that I define my credit union userid and password, and Microsoft Money downloads all of the accounts. The OFX file sent by Microsoft Money does not include ACCTID or ACCTTYPE parameters, but the PocketSense OFX file does. The response to the PocketSense OFX is "Account not found". I would rather be able to select individual accounts, so I am checking with the credit union to see if that is possible, but so far the credit union has been less than helpful so I am not optimistic that I can get any useful information from them.

    ReplyDelete
  117. phwhite, make sure that your PocketSense scripts are not within your "Program Files" folder in Windows Vista. Instead create a folder somewhere else. That folder could be in Documents, or you could create a new folder that is not within either the Windows or the "Program Files" folders. I am suspecting you might have your PocketSense files within "Program Files" folder because of your comment that changes you did were not taking effect. There could be other reasons, but that would be one.

    You will need to create separate accounts for each account when Using PocketSense. The username and password would typically be the same for each of those accounts, but the account number would differ. If you want to see what account numbers that Microsoft Money is using, you could collect logs from MM. Look at the *rq.txt files; one of them should have the account numbers that Money is using in its request.

    ReplyDelete
  118. PocketSense folder location: This is not in my "Program Files" folder but is in a subfolder a few layers below my C:/Users/(User) folder so that should not be a problem unless the path is too long. However, Setup has worked properly some of the time, which is weird. I saw by looking at the PocketSense OFX file that I had misinterpretted the purpose of the User Name parameter, so now that I have that fixed maybe the problem will go away. So far I have not had the Setup problem while using debug mode, which is good to have turned on in any case while I am setting this up.

    Microsoft Money OFX: I did collect the logs from Microsoft Money, which is where I saw that MS Money was only passing the USERID and USERPASS fields but not the ACCTID and ACCTTYPE fields. That is why I posted my question about the credit union situation.

    ReplyDelete
  119. Robert - Great job on these scripts.

    Anyone successful in downloading BoA checking account data in Washington state? I keep getting a username/password error (code 15500). I've checked my username & password and verified my account number many times. Thanks in advance.

    ReplyDelete
  120. PHWhite: good deal on the folder.

    Regarding the *rq.txt files in the log, expect two *rq.txt files for each FI. Look at the other one.

    ReplyDelete
  121. Hi Robert,
    Thanks for the quick reply. I’ve only been looking at share prices so far, not funds. I should have said that I am using the uk-yahoo website to get a UK share price, and have currency set to GBP. For eg “BT.A” shares the correct price is retrieved by pocketsense, though with the decimal point in the wrong place, which I edited manually.
    I don’t think the problem is with retrieving the data, as the OFX file is created ok with the correct digits in the price. I wonder if it’s a problem with UK-Money-2005 version (ver 14.0.100.1105) – perhaps it does not update the price unless the unit count has changed as well ? When I try to load the file with units=0 the new price is shown on the reconciliation screen, but not updated into the other entries for that code.
    If I edit the units to 1 and then load, the price is then updated ok, but I have to allow Money to add the new one share, which means that the share count is wrong, and also requires a manual confirmation.
    If qwakuk is having the same problem then it may be that our UK version is not as clever as the US versions . It would be interesting to know if anyone has found a way to update the prices using Pocketsense on Money 2005(UK).

    Thanks,
    Veronica

    ReplyDelete
  122. Re UK Money 2005 : I am currently trying to get Pocketsense to update just share prices, not funds. I am using yahoo UK with currency code GBP, and edit the ofx file to correct the decimal point.
    The OFX is created ok, but when loading into MSMoney 2005 UK (version 14.0.100.1105) the new price is shown on the reconciliation screen, but not updated into other entries for that share code.
    I wonder if it is a problem with this version of ms-money in that it will not update the price if the unit count is 0? If I set the unit count to 1 the price is updated, but I have to manually confirm it, and I am left with an extra share in the portfolio (guess I'd need to add 1 each time I loaded the ofx).
    If qwakuk also has the same problem perhaps it is with Money 2005-UK ? Has anyone else managed to load Pocketsense share prices into Money-2005-UK ?
    thanks,
    veronica

    ReplyDelete
  123. Robert: Thanks for the advice on the debug option. That was essential to getting this working.

    Cal: Thanks for the tip on looking at the second *RQ.txt file. I see now that the first *RQ.txt file is a request of the account info from the financial institution, and the second *RQ.txt file is requesting transaction downloads for each account. I only want to download specific accounts, so this gave me what I needed to figure out how to do that without any assistance from the credit union.

    I now have successfully downloaded transactions from TD Ameritrade (institutional), American Express (AMEX), and the Oregon Community Credit Union (OCCU).

    With Ameritrade, my failed attempts caused some of the accounts to have downloads locked out, so I had to call their technical support to figure that out and get the accounts unlocked.

    With American Express, my primary issue was not realizing that one of our credit card numbers was secondary and one was primary, and I naturally tried to download transactions using the secondary card number. I had to call American Express technical support to figure that out.

    With the Oregon Community Credit Union it took looking at and understanding the Microsoft Money logs to understand how to do those downloads.

    I have not fully tested this, but things are looking good at this point.

    ReplyDelete
  124. Robert: In quotes.py, here is a section:
    ==================================
    def _invStmt(self, currency):
    tomorrow = datetime.datetime.today() + datetime.timedelta(days=1)
    res = _tag("INVSTMTRS",
    _field("DTASOF", tomorrow.strftime("%Y%m%d")),
    ===================================

    Ultimately, I think DTASOF should be taken from the first quoteTime value. In the mean time, I think that the DTASOF should be taken from today rather than tomorrow.

    ReplyDelete
  125. Interest comment Cal, as it describes what I'm doing. The current dev version has already been changed to use today@noon, but the plan is to use the first live quote.

    ReplyDelete
  126. I have had a setback on my Fidelity scrubber function. I think I had my perceived success in a particular time window, but I am having problems with my later experiments. I have not characterized the problem yet.

    Again the problem is having quotes timestamped a day off. The Vanguard scrubber seems to work so far.

    ReplyDelete
  127. I am using a scrubber dated 8/6/10 and Fidelity works correctly. We did have an issue some time ago associated with zero time causing the date to be off by 1 day.

    ReplyDelete
  128. While you have scrubber.py dated 8/6/10, it probably does not make changes for Fidelity. The need for a scrubber may change depending on what timezone you are in, whether daylight saving time is in effect, and when you do your downloading.

    Check the quote history for one of your holdings for the last month or so. I would expect there to be no quote entries dated for Saturday or Sunday if there was already a scrubber working for Fidelity for you.

    Regarding Fidelity, things may be different if the user is using Fidelity NetBenefits rather than Fidelity Investments. I suspect the same scrubber will work for both, but it is not necessarily so.

    ReplyDelete
  129. Anybody else having trouble with Scottrade lately? Everything has been working perfectly for months, then suddenly about 10 days ago my Scottrade downloads stopped working. I have tried both www.scottrade.com and scottsave.com as the broker ID, but neither one works.

    ReplyDelete
  130. I am using Fidelity Investments. Since Robert made sone changes around November my quote dates from Fidlity have been correct. Before that they were 1 day off.

    ReplyDelete
  131. Jim,

    According to this site http://www.ofxhome.com/index.php/institution/view/623 , the Scottrade OFX server is fine. See if the settings match yours.

    Dan (ameridan)

    ReplyDelete
  132. Ken (and anybody else interested): I have placed a copy of my scrubber.py available at http://www.speedyshare.com/files/26276348/scrubber_cl00.zip

    There is a text file. http://social.microsoft.com/Forums/en-US/money/thread/674a497f-0ac9-420e-9ab8-9924cae6a2ef lists the SHA-1 for my scrubber.py mod.

    ReplyDelete
  133. Dan, I saw the same thing re: the Scottrade server. I don't understand why I'm having problems now. It was working fine for at least three months prior to the last ten days. Is anyone else using Scottrade, either successfully or unsuccessfully?

    ReplyDelete
  134. Quick Question, Downloading discovercard statements I from time to time will get numerical entries at 0.00 debits. I usually just delete these in money but using the financialbuff's scripts they come back each time I run them. Does the pocket sence scrubber remove these? I don't want to leave all the 0.00 charges out there.

    Thanks!

    ReplyDelete
  135. Personally I like getting the 0.00 debits again. Recent versions of Money filtered those out and hence I didn't see the notes that sometime accompany those transactions. If your download interval is 7 days for example, just leave those charges there for a week before deleting. They also let you know that real charges to follow are pending, so if you know you haven't used your card, someone else might be...
    -Dan

    ReplyDelete
  136. I forgot to answer the other part of your question - no, PocketSense does not remove them, although it could. The word for those charges (if they are not NOTES from Discover) are preauthorizations.

    ReplyDelete
  137. Thank you so much for this software!! I was worried that I might have to switch to Quicken at the end of the month. Now that I know that is not the case, I've been attempting to setup my accounts. I've successfully setup and imported my account info from First Tech Credit Union, but am having issues with ING Direct. I receive the error message:

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

    I looked into the downloaded file and it appeared to be missing a DTEND field. I added the one from my First Tech Credit Union download and then the file imported correctly.

    Is there some way that the ING Direct ofx file can be correctly formatted without having to go into the file each time to add a DTEND field?

    Also, is there a wiki somewhere that people post their successful bank configurations to, because I'm also having trouble even connecting to some of my other banks (US Bank, Sharebuilder)?

    Note: I'm using Money Plus Home and Biz Sunset Edition

    ReplyDelete
  138. Stephen,

    Sounds like an ideal application for a scrubber routine if the issue repeats itself. Regarding OFX configurations, you should find what you're looking for in one of the links here: http://microsoftmoneyoffline.wordpress.com/look-up-your-ofx-settings/

    -Dan

    ReplyDelete
  139. Can anyone advise me how to to get Chase Banking/Checking to work?

    ReplyDelete
  140. Thanks MMO,

    Those links were very helpful. I followed the format for AMEX and CITICARD and those worked right away. I figured out the issue I was having with ShareBuilder (for account number only use the 10 digit number between the hyphens in the account number string shown online). I read some more about US Bank and apparently one has to pay to access it, so that's not worth it for me (only one VERY lightly used account).

    So now I've got everything working except ING. I wouldn't know where to begin on a scrubber file.

    ReplyDelete
  141. Transaction Download Interval: I would like to figure out a simple way to make the transaction download interval more dynamic. First, I would like to describe how I am currently using the PocketSense scripts.

    I maintain 3 Microsoft Money files, and I use a batch command script to specify which MNY file to open. I therefore created separate ofx_config.cfg files associated with the individual Microsoft Money files so that I would only download transactions for the accounts used by the specific Microsoft Money file that I am opening. My batch command script copies the specific ofx_config.cfg file into the PocketSense folder before starting Microsoft Money, and then I use a Microsoft Money web favorite to launch Getdata.py from within Microsoft Money. The issue I have is that I have lots of transactions in one of my MNY files, so I usually download transactions every few days for that MNY file. The other two MNY files have far fewer transactions, so I may only download transactions a few times per month for them. Occasionally when I am away from home I may not download transactions for a month or longer for any of the MNY files. The other consideration is that one of the low activity MNY files has accounts at the same financial institution as the high activity MNY file. I just started using the PocketSense scripts a few days ago, so at this point I am using defaultInterval: 7, which is working fine for now, but in the not too distant future I will need to lengthen this interval, at least for the low activity accounts.

    I looked at the ability to set the minimum interval by site, which would let me address the issue of having two MNY files downloading transactions from the same financial institution but with both low and high activity accounts. However, that would still leave me with a choice of what to do about the transaction download interval for the high activity MNY file. The choices I see are:
    1) Have about a 45 day transaction download interval even though most of the time I download transactions every couple of days, or
    2) Have a short transaction download interval of about 7-14 days that would miss transactions when I occasionally am away for home longer than that.

    I thought about trying to use my batch command script to feed the transaction download interval into the PocketSense script, but I don’t have an easy way to reliably keep track of when the last successful download occurred, and I suspect that the PocketSense download interval gets saved in a way that would not be easy for me to alter from my batch command script.

    If possible, I would like to keep the download interval short on the high activity MNY file since the transaction download and matching performance is better that way with less likelihood of problems arising from repeatedly downloading older transactions.

    Any advice on what to do about this?

    ReplyDelete
  142. One possibility is to have separate folders for your PocketSense files. The batch file would CD to the appropriate folder. Each would have its own settings.

    But I am wondering what would make things terribly slow if you just used a 60-day interval for all. Here are my ideas:
    1. You are in an exceptional hurry.
    2. You have a really large number of transactions.
    3. Your computer is really slow.
    4. Your online access is really slow.

    #3 and #4 might be something you could speed up. :-)

    ReplyDelete
  143. Transaction Download Interval: I generally have 80-100 transactions per month so in a 60 day interval there would be twice that. Not sure whether this is a lot of transactions, but it does seem like Microsoft Money takes noticeably longer to process lots of transactions, but it also seems like performance is better with the PocketSense script than it was using MS Money. I don’t know at this point whether things will slow down as I increase the transaction download interval.

    My PC is fast and I have fast Internet access, so I suppose what I could do is just follow the recommendation to progressively bump up the transaction download interval over the next few months and see how things go. I expect 60 days would cover just about any situation that might reasonably happen, so that would probably be where I would head.

    ReplyDelete
  144. PHW: The download interval shouldn't have much effect on import time. If you require separate processing, I'd do as Cal suggests and have a separate directory for each, and then define a separate shortcut to kick off each.

    Stephen: I don't use ING, but would be happy to put a scrub routine on the to-do list. Feel free to contact me directly @ pocketsense1 /at\ gmail /dot\ com to discuss.

    ReplyDelete
  145. I could possibly add a routine to strip zero dollar transactions from credit card statements, and add a user option in sites.dat to enable/disable. Would that be of interest? I don't think I would do the same with investment or bank accounts.

    ReplyDelete
  146. Robert,

    Making the "strip zero dollar transactions" optionable sounds great! That way you can enable if you're expecting a bunch of Preauthorization Debits too.

    Dan

    ReplyDelete
  147. Robert: I am using a variation of the separate directory approach suggested by Cal, which is to have separate ofx_config.cfg files associated with the individual Microsoft Money files. I use a batch script to launch Microsoft Money with the specific money file, so I modified the batch script to copy the appropriate ofx_config.cfg file into the PocketSense directory before launching Microsoft Money. I then use a Microsoft Money web favorite to invoke Getdata.py from within Microsoft Money. This seems to work fine for me without requiring duplicate PocketSense directories. I plan to try progressively increasing the transaction download interval as suggested and see how that goes for me. Thanks for all the help.

    ReplyDelete
  148. Capital One MasterCard: Has anyone figured out if it is possible to use the PocketSense script to download transactions for a Capital One MasterCard? I called Capital One technical support, and they said the only download option is to login to their website and make the download request from there. That works, but takes extra effort compared to the more automated approach of scripting the download.

    ReplyDelete
  149. US Bank Visa Card: Are there any options for using the PocketSense script to download US Bank Visa card transactions, other than paying US Bank $3.95 per month for this “service”? I am able to download transactions by logging into the US Bank website, but that takes more time and effort than using a script would.

    ReplyDelete
  150. This post is not recommended for general use; it is for the experimentally inclined. I put an experimental and not prettied-up mod of quotes.py. The purpose is to use the datetime of the first stock quote fetched as the DTASOF time, which determines the date that Money records. Thus no quotes in Money will be dated for days the market is closed.

    Remember things are in flux while markets are open...

    http://www.speedyshare.com/files/26341776/quotex1.zip

    ReplyDelete
  151. I can't for the life of me get my accounts to work. I looked up all the IDs, put everything the way it seems to need to be. Yet no matter what, I always get this error:

    **An ERROR occurred when connecting to (ofx server domain)
    An online error occurred while testing the new account.

    My accounts are with ING Direct, TD Ameritrade, and Citi Cards.

    ReplyDelete
  152. Nicholas: You sound like me a week ago, and now I have all my accounts working. For TD Ameritrade here are the settings I used:
    - SiteName : TD Ameritrade
    - AcctType : INVSTMT
    - fiorg : Ameritrade
    - url : https://ofxs.ameritrade.com/cgi-bin/apps/OFX
    - brokerid : ameritrade.com
    - Account # : (9 digit account #)
    - User name : (same as Account #)
    - Account password : (4 digits)
    I had to call TD Ameritrade because transaction downloads for my accounts had been locked out because of my failed attempts, so even if you are using the right parameters it may not work until you call to get your accounts enabled for transaction downloads. I also needed to have TD Ameritrade reset the passwords. The TD Ameritrade phone number I called was: 800-431-3500 (7 am to 9 pm Eastern Time) press 3 then 2 for Technology Support.

    Regarding your other accounts, I found it very helpful to set Debug = True in control2.py so I could see what was actually happening. I also found it very helpful to turn on logging in Microsoft Money so I could see the OFX files being produced for successful downloads. If you google for Microsoft Money logging you should be able to find out how to do that. Once logging was turned on I got 4 files for each set of transactions being downloaded. The first ---RQ.txt file was the initial OFX file being sent by Microsoft Money. The first ---RS.txt file was the response from the financial institution. The second ---RQ.txt file had the OFX parameters that I needed to get the PocketSense scripts to work.

    With one of my credit cards I had a problem because I did not realize that one of the credit card numbers was the account number that I needed to use, and the other credit card numbers was a second card on the account and was not used for transaction downloads. I had to call the credit card company to figure that out, but that was before I looked at the Microsoft Money logs.

    Good luck. Once you get it working it will probably work well for you.

    ReplyDelete
  153. I've been continuing to use MS Money to the bitter end. I don't download statements for my accounts, but I *love* having stock/mutual fund prices updated online.

    I've been looking at various reviews online, trying to suss out what I'd need to migrate to, and feeling completely underwhelmed by the choices. Discovered The Finance Buff's reviews and then his python scripts, which led me to the Pocket Sense site.

    IT LOOKS SUPER COOL!!! THANKS FOR DOING THIS!!!

    I'll try to install/configure it sometime before the end of the month. While I'm reasonably techie/analytical, I'm not familiar with python, but the instructions look pretty good. Will post another comment if I run into any problems.

    Thanks again.

    ReplyDelete
  154. Regarding my Fidelity Scrubber, it did not work tonight. Quotes for the 18th came in for the 14th. Whoa.

    It seems that my scrubber was expecting a DTASOF for the 19th, and instead got a for the 15th. My scrubber backed that off to the 14th.

    I suspect that Fidelity has been doing some work on their OFX file dates. It is not correct, but it is an overshoot in the right direction. I wonder.

    Anyway, I have pulled the download with zip file with my scrubber attempt, and I shall see what develops.

    If somebody has been downloading Fidelity Investments OFX, please check to see what the latest quotes were saved at. If my observations work the same for you, with no scrubber the quotes for Jan 18 will show as quotes for January 15. It is possible that you already had quotes from the 14h for that date, in which case the prior ones would have priority.

    ReplyDelete
  155. All my quotes from yesterday are 1/18.

    I just downloaded today and all the quotes are dated 1/19.

    ReplyDelete
  156. MikeV, I was talking about quotes brought in from Fidelity investments in the OFX file. I am working with quotes that are not listed in the sites.dat file, so are not being brought in from Yahoo. I should have stated that. Thanks.

    ReplyDelete
  157. Me too (LT & GT symbol changed to allow posting):

    {POSSTOCK}
    {INVPOS}
    {SECID}
    {UNIQUEID}922042874
    {UNIQUEIDTYPE}CUSIP
    {/SECID}
    {HELDINACCT}CASH
    {POSTYPE}LONG
    {UNITS}172.00000
    {UNITPRICE}50.6200000
    {MKTVAL}+00000008706.64
    {DTPRICEASOF}20110119033032.000[-5:EDT]
    {CURRENCY}
    {CURRATE}1.0
    {CURSYM}USD
    {/CURRENCY}
    {/INVPOS}
    {/POSSTOCK}

    These are in the Fidelity OFX, not the Quotes OFX.

    ReplyDelete
  158. PHWhite,

    Unfortunately since I'm using Money Sunset, I can't do any logging in order to see what settings it was sending out. I guess I could be locked out of all of my accounts. Hmm...

    ReplyDelete
  159. MikeV, thanks! I see the {DTASOF} field I am getting now is 20110119*. Last night I was getting 20110115* although the prices corresponded to closing prices for the 18th! Fortunately I recorded it as part of a fc I did for before and after scrub. It was a transient thing, and I am going to have my scrubber routine allow for that, even tho it will slow execution some.

    Nicholas, the file that MikeV is reading from is a FIDELITY*.ofx file in the xfr folder of the PocketSense folder. The Fidelity OFX lines are run together, so he probably has some tool to make it more readable. One way to do that is to change all greater-than symbols into a newline+greater-than, as I do for viewing.

    ReplyDelete
  160. OFX Formatter

    http://microsoftmoneyoffline.wordpress.com/2010/03/07/ofx-file-viewer/

    ReplyDelete
  161. Hi! I installed Pocketsense recently, and wanted to say thank you, thank you, thank you! It works really well for me, and is much better than manual downloads!

    There are two things that happen to me that I want to share...

    1) Sometimes, I will get an error from Pocketsense connecting to a particular bank/account, and then when I retry just that account using the setup program (a convenient way of doing only one account), it works just fine. Notably, this seems to happen with Chase and with Hewitt Associates. I was wondering if this could possibly some kind of timing issue or something, where the Python scripts were possibly not waiting long enough when trying to connect, if such a notion makes sense? Alternatively, is there some kind of error code that you might display so that I can have an idea of what is going wrong?

    2) I have sixteen accounts, and they get sent into Money all together. I keep on getting Import Complete windows without any identification (which is not a problem in and of itself; see below), but occasionally, I get a message from **Money** telling me that it had a communications error (without identifying which account). Like really? It's not doing any communications! It takes me a bit of time to figure out which account the "communication error" occurred on, and then double-clicking on that OFX file in my Pocketsense XFR directory makes it all work fine.

    You've done so much with this stuff... can you think of any possible way to make Money tell me which account it wants me to resend to it? Also, since the OFX files are named with timestamps, is there an easier way to figure out which OFX file I want than browsing all of them from the same bank?

    Thanks so so SO much for all your efforts! This is so wonderful!!

    Harold

    ReplyDelete
  162. Harold: I've witnessed the same "communications error" in Money, but haven't found the definitive answer. There's no method to find out which file triggered the error.

    I'll add a slight delay between file uploads (maybe 0.5 sec) for the next release. As of now, they are fired off in *fast* sequence after all downloads are complete, which may well be the culprit.

    ReplyDelete
  163. Nicholas Bauer: Unless your TD Ameritrade setup is different than mine I think I provided enough information in my previous post for you to get that working without seeing a Microsoft Money log. Turning on the PocketSense script debugging function won’t hurt and might help. I suggest initially trying to get transaction downloading working for TD Ameritrade. The Microsoft Money logging was helpful, but not absolutely necessary. I have an account at ING Direct that I don’t track with Microsoft Money, but I could take a look at the OFX parameters for that if you get stuck on it. This all seemed more complicated before I got anything working than it did after I got one account working, so post back if you get TD Ameritrade working and need some help on ING Direct.

    ReplyDelete
  164. PHWhite,

    I hope you don't mind that I included your TD Ameritrade settings into my Blog "database". Every time someone reports successful settings for PocketSense OFX downloads, I'm trying to gather them into one spot - could turn into a Wiki someday :) @ http://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/

    -Dan

    ReplyDelete
  165. I have read the whole thing and after the installation everything works great the only problem I am facing is that some of the funds, although present in the yahoo website do not get retrieved by the script and shows as follow:

    PocketSense OFX Download Scripts for Money, Ver: 13-Oct-2010
    Download transactions? (Y/N/I=Interactive) [Y] Y
    No accounts have been configured. Run SETUP.PY to add accounts

    Getting security and fund quotes...
    USDCAD=X 0.9973 1/20/2011 8:25pm
    TDDI.TO 19.48 1/18/2011 3:00pm
    ** TDCANADIANEQ.TO : invalid quote response from Yahoo! Finance. SKIPPED...
    ** F0CAN05MQI.TO : invalid quote response from Yahoo! Finance. SKIPPED...
    ** TDUSQUANTITA.TO : invalid quote response from Yahoo! Finance. SKIPPED...
    Finished downloading data

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

    So at this point I am baffled as this occurs with every scrip laying around including HLE java application... any comments?

    ReplyDelete
  166. Thanks a lot, Robert -- and again, thanks for everything!

    Harold

    ReplyDelete
  167. I have done more work with my scrubber routines. I removed a significant bug, and I made the processing more robust by using some sanity checking. For my quotes.py I am inhibiting fetching stock quotes while the market is open, and I am inhibiting getting quotes for mutual funds during the 1.5 hours after market close. During that interval, there is ambiguity as to which date to associate with the file.

    Rather than putting them up for download right away, I will watch the the behavior for a while. If somebody wants the files earlier, let me know.

    ReplyDelete
  168. Nicholas Bauer, I use these in my sites.dat file:

    For citi cards,

    SiteName : CITICARD
    AcctType : CCSTMT #credit card
    fiorg : Citigroup
    fid : 24909
    url : https://secureofx2.bankhost.com/citi/cgi-forte/ofx_rt?servicename=ofx_rt&pagename=ofx
    bankid :
    brokerid :
    appid :
    appver :
    mininterval:


    account #: 16 digit cc number
    username and password: same as online

    ING Direct:

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


    account #: same as account number
    username: saver ID or customer number
    password: log in and go to the my info tab and enable your Personal Finance Access Code. Use this code.

    NOTE: Your ING accounts will not import correctly due to a missing field. Robert has added a scrubber routine to fix this to his to-do list (THANKS ROBERT!!)

    ReplyDelete
  169. Has anyone managed to get Pocketsense working for share prices on MS Money 2005 UK (ver 14.0.100.1105) ?

    I could not get the share prices to update with unit count set to 0. The share price is shown in the money reconciliation screen, but does not update to other accounts that use the same share code.

    My workaround is to set the unit count to 0.001 on the first run, as this then forces Money to add the part-shares into the dummy account, which updates the share price. Using 0.001 units means that the price is very small so the dummy account has a value of close to zero.

    For the next load if I use 0.001 (ie no change) money does not update the price; therefore I set the unit count to 0.002 – again this forces money to add the new part-share and update the price.

    From then on I just alternate between 0.001 and 0.002 so that money has to update the share-count (either adding or deleting a part-share), and thus update the price.

    Messy, but it works... but it does increase the database size as it is storing all the ‘dummy’ purchases and sales. Think I will only update MS Money on a weekly basis.

    Has anyone managed to get the UK Money 2005 working in a simpler fashion ??

    ReplyDelete
  170. Veronica" You say "I could not get the share prices to update with unit count set to 0. The share price is shown in the money reconciliation screen, but does not update to other accounts that use the same share code." I don't think this is because you are using Money 2005 UK. I am thinking it is because Money thinks that these are different securities than what you have in the real accounts. I suspect that when you first got your quotes this way, Money gave you a screen for each security and asked if this was a new security or was already known to Money by a different name. If you just took the default, it would create a new security with its own name. I am not sure how to tell you that these are the same, but you might want to check for that condition. If that is indeed the case, then we can think about how you can get things right. It is not normal to have to play with the quantities.

    ReplyDelete
  171. Cal Learner

    Veronica is correct, it just does not work with MS Money 2005 UK v14. On the first "import" you have the option of changing the existing tickers to match the one in the ofx file. It simply refuses to update the prices unless a new purchase is added on the same day !

    ReplyDelete
  172. Thanks for the feedback, PHWhite and Stephen. It still didn't work for me, so I delved into the code. Apparently the error I'm getting is happening at the HTTPS connection request, before the OFX query is even sent. I'm looking more into it, but I'm not as familiar with Python and these procedures.

    ReplyDelete
  173. And I think I found the problem: I've got Python 2.5 installed, but PocketSense apparently requires Python 2.6.

    I wonder if it would be possible to add a version check when Setup.py is run? Probably just a warning, not an exit-if-wrong-version.

    ReplyDelete
  174. Robert,
    I have tried all I can think off but there is definitively a problem retrieving the price of these funds:

    TDCANADIANEQ.TO
    F0CAN05MQI.TO
    TDUSQUANTITA.TO

    Everything is set up correctly, the currency is CAD and money gets updated but only with the working ones “TDDI.TO” and “TDCANADI.TO”
    I have a feeling is the number of characters in the symbol (maybe a max of 8 before the extension?)
    Can anybody try a quick run with those problem funds and post the results…?
    I hate giving up but I am at a dead end…
    This script works so great that I hate to think I would even consider another software solution after more than 12 years of MM.

    Thanks in advance

    ReplyDelete
  175. Miguel: The Yahoo CSV API does not currently support Canadian Mutual Funds. Apparently, you hit upon one of the few that they do have. I do not know the reason that Yahoo provides CA data via their web interface, but not the CSV hook.

    ReplyDelete
  176. Migual
    I tried those using the default Yahoo server. Here is the print-out from PocketSense:
    ** TDCANADIANEQ.TO : invalid quote response from Yahoo! Finance. SKIPPED...
    ** F0CAN05MQI.TO : invalid quote response from Yahoo! Finance. SKIPPED...
    ** TDUSQUANTITA.TO : invalid quote response from Yahoo! Finance. SKIPPED...

    So it did not even make it to Money. Money has a length restriction for symbols, but that is not the problem here. Yet I was able to get the associated quotes manually.

    I logged the csv response that PocketSense got back:
    '"TDCANADIANEQ.TO",0.00,"N/A","N/A",N/A,"N/A"\r\n'
    '"F0CAN05MQI.TO",0.00,"N/A","5:27am",N/A,"N/A"\r\n'
    '"TDUSQUANTITA.TO",0.00,"N/A","N/A",N/A,"N/A"\r\n'
    So with zero prices returned, PocketSense rejected the quotes.


    I did not try to switch to a Canadian server.

    ReplyDelete
  177. Thank you very much guys…

    What are the chances that I happen to have in my portfolio a couple of the fund that actually work???
    I guess I leave the set up as is hoping that yahoo changes this situation in the future or someone comes with another method for Canadian funds.
    I was doing some Google and I came up with the following: http://code.google.com/apis/finance/ but as I read before it seems that is a bit complicated trying to get this to work.

    Again thanks a lot for your hard work and I’ll be checking for a viable solution.

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

    ReplyDelete
  179. I modified Getdata.py to have a Silent mode ("getdata -silent") for people to use if they want to use Task Scheduler to automate their downloads. If you want to put it into the main script, feel free.

    silent = False

    if len(sys.argv) > 1:
    >>>>if sys.argv[1] == "-silent": silent = True

    if silent:
    >>>>doit = 'Y'
    else:
    >>>>print AboutTitle + ", Ver: " + AboutVersion + "\n"
    >>>>doit = raw_input("Download transactions? (Y/N/I=Interactive) [Y] ").upper()
    >>>>if len(doit) > 1: doit = doit[:1] #keep first letter
    >>>>if doit == '': doit = 'Y'

    ReplyDelete
  180. Great stuff and fantastic work.

    I use MSMoney soley for managing by stock portfolio. I have setup your script to download the shareprices for all of my stocks and the script runs with no errors and retrieves all prices.

    What I find is that the prices are updated on the home page in MSMoney (in the Investment Summary box) however when I click on the portfolio button and review the share prices they are out of date. I could be mistaken but it seems to take a day or so before the portgolio section is updated.

    Is there anyway to get the portfolio section to update immediately?

    Thanks for your help and again greate work.

    ReplyDelete
  181. Miguel
    I am working on a solution which will try a different mode if the CVS mode fails. Looking at the http://finance.yahoo.com/q?s=tdusquantita.to&ql=1 page, you might see that the Trade Time field is later than for most securities.

    I will let you know when it is ready for you to try out. I am just getting the elements together so far, but I am having some success.

    ReplyDelete
  182. Here's an update on my troubles with Scottrade. I just spoke with one of their tech support representatives and he was able to view my entire history of connection attempts on their OFX server. As far as he can tell, all of my attempts have been successful. This means that I have the connection parameters configured properly, and my accounts/passwords are set up correctly. From what he sees, there are no problems on the Scottrade side.

    It has been almost three weeks now that the Python scripts have been indicating an error when connecting to Scottrade. The response I get from the scripts is "An ERROR occurred when connecting to ofxstl.scottsave.com" That's all I get so there is no indication what the problem might be.

    I also asked the tech support guy to look at some of my connection attempts from a month ago when I know this was working and compare those to my recent attempts to see if there are any differences. They all look the same on his end, so again it seems that this should be working.

    I have other accounts with AMEX and USAA, for which the scripts continue to work fine. So it's not like the scripts are corrupt or something. Anybody have any idea what could be happening here?

    Thanks.

    ReplyDelete
  183. Okay, I can shed some more light on this Scottrade situation. I just downloaded a product called AceMoney Lite. It allows you to connect to a financial institution and automatically download/import transactions. I set up one of my Scottrade accounts in AceMoney Lite and when I went to define online services for that account, in addition to asking for my user ID and password, it also asked me to specify how I want to connect. There's a dropdown with a lot of options:

    - Default
    - Quicken 2005 for Windows
    - Quicken 2006 for Windows
    - Quicken 2007 for Windows
    - Quicken 2008 for Windows
    - Quicken 2009 for Windows
    - Quicken 2010 for Windows
    - QuickBooks2010 for Windows
    - QuickBooks2010 for Mac
    - Money 2001
    - Money 2002
    - Money 2003
    - Money 2004
    - Money 2005
    - Money 2006
    - Money 2007
    - Money Plus

    I initially tried "Money Plus" and the connection failed. So I changed my selection to "Default" and it worked perfectly. It imported all of my transactions from the last 30 days as expected. Then I tried "Quicken 2010 for Windows" and that worked too. Finally I tried "Money 2007" and even that worked. So I switched back to "Money Plus" and it failed again. This is the error information that I received:

    HTTP/1.1 400 Bad Request
    Server: NepalHttp/2.3
    Content-Type: application/x-ofx
    Connection: close
    Accept-Ranges: None

    I don't know how the Python scripts work, but I'm wondering if there is some attribute of the connection that is making Scottrade think that it's coming in as Money Plus. One of the Scottrade reps that I spoke with indicated that they don't support Money anymore, so that might explain what's happening here. It seems strange that Money 2007 would work as an option, but maybe they're only blocking Money Plus connections since that's the latest version.

    If this is the issue, would it be possible to change the way the Python scripts connect to Scottrade so that they'll let us in the door?

    ReplyDelete
  184. James: Are you specifying appid : Money and appver : 1700 in sites.dat? That tells the financial institution that you are using Microsoft Money Plus. If Quicken works for you then you can omit the appid and appver parameters because the defaults are appid : QWIN and appver : 1800, which is Quicken 2010. Alternatively you can use appid : Money and appver : 1600 for Microsoft Money 2007, but if Scottrade has dropped support for Microsoft Money Plus they might also drop support for older versions of Microsoft Money as well so you might be better off using the Quicken OFX files.

    Here is a link that has a complete set of appid and appver values:

    http://microsoftmoneyoffline.wordpress.com/appid-appver/

    ReplyDelete
  185. Thanks for the reply, PHWhite. I thought we had it there, but I checked and my appid and appver are blank, so it should be using the defaults. I tried manually using QWIN 1800 just in case it was defaulting to something else somehow, but that didn't work either. I also tried manually specifying Money Plus 1700, Money 1600, and QWIN 1900, None of those work. This is getting frustrating.

    ReplyDelete
  186. James: Have you looked at the .\xfr\site*.ofx file to see if anything stands out in the response?

    ReplyDelete
  187. Robert,
    The xfr directory does not contain any Scottrade files when the connection fails. The only files I see are for AMEX, USAA, and one for stock quotes.

    Now here's another wrinkle. I have five Scottrade accounts set up. Let's call them #1-#5. I just ran the GetData script and for some reason, account 1 and 5 worked, but 2, 3, and 4 all failed. For 1 and 5 there were files in the ofx directory, and in both files there was a message saying "The operation succeeded." There were no files for the accounts that failed.

    So I tried it again and this time account 1 worked and the rest failed. The only Scottrade file in the ofx directory after that test was for the account that worked.

    This really makes no sense. Scottrade tells me that they see no connection errors on their end. I know that my passwords are set up properly. Plus if it was a password problem I'd get a file in the ofx directory indicating that. The only feedback I'm getting is the script error saying "An ERROR occurred when connecting to ofxstl.scottsave.com".

    ReplyDelete
  188. James,

    Use the setup routine and test one at a time. Remember don't run getdata afterwards (until you have reviewed the OFX files) as that will erase existing OFX files. If this works, then you know it's more of a timing issue.

    -Dan

    ReplyDelete
  189. I've tried it with Setup, GetData, anything I can think of. I just tried each account individually via Setup again and all five failed. I got no OFX files for any of them. I did this same thing when I had the Scottrade guy on the phone. I used Setup to test an account, he saw that the connection was made successfully, but it showed a failure on my end.

    Is there anything else that comes into play here? Is it possible that my firewall is blocking the communication or something? Do I need to open up a special port? None of that would make sense since the scripts work fine for AMEX and USAA, and maybe 1 out of every 15 tries for Scottrade.

    This just baffles me cuz these scripts were working perfectly for me for months and I can't think of anything I have changed on my laptop/network that would affect this.

    ReplyDelete
  190. I have a question regarding the encryption password on the setup file. When I set my password, I wanted to set it to a password with 20-something characters. I did, or so I thought, but it seems that only the first 8 characters need to be correct. Is this by design that the password be 3 to 8 characters?

    ReplyDelete
  191. James: If you are not getting a file, then the connection itself is failing. I missed it before, but that is the message you are receiving too. Shoot me a direct message @ pocketsense1 /at\ gmail /dot\ com, with a quick summary.

    ReplyDelete
  192. Stephen: The current encryption uses an 8-byte key. All chars are valid, and is case sensitive. If more than 8 chars are entered, only the first 8 are used for the key. When I first added it, that's how I did it. I could add 16/24 byte support very easily, but would have to be careful with how it handles existing passwords over 8-chars in length.

    ReplyDelete
  193. Carl success!!! The script worked flawlessly and all the funds were updated with today’s date.
    I did not change the symbols in money as they were already truncated the OXF took care the update. I will update again tomorrow night… thanks again for the great work you guys are awesome.

    ReplyDelete
  194. This post consolidates 3 posts I had posted earlier. I finally figured out how to delete my posts. I uploaded a version of quotes.py for Migual or anybody else who wants to try it for fund quotes that don't return a CSV. It works by "screen scraping" only in the case of a fund where the regular way did not succeed.

    http://www.speedyshare.com/files/26466813/QUO_PY3A.ZIP

    Included in the zip file is a txt file that has simple directions. Changing it to apply to stocks also would be easy.

    I noted that Money Plus had only used the first 12 characters of the long tickers. So no match. I added code to modify the ticker fed to the ofx file to be no longer than 12 characters. The first 12 are used. A better method could be thought of, but I mimicked what Money Plus Sunset was doing when I entered a Buy for a fund with a long ticker. I only did this for funds in this code at this
    time.

    SHA-1
    ----------------------------------------
    ba9bb1d7b2e7c6d67eef97fa6725a0d12d454b64

    ReplyDelete
  195. Cal...I also had success with your "screen scraping" script for my Canadian mutual fund prices. One issue I have is that yahoo finance seems to have a long delay in updating prices on most of my funds. For example, the price I get today (Jan 25) for F0CAN0721D.TO has a date of Jan 20 on yahoo. The Jan 20 date is saved in the .ofx file as 20110120160000.000[-5:EST], but when I import that file into my Tickers acct in Money all prices get today's date (Jan 25) instead. Is there a way to have Money recognize the DTPRICEASOF as the correct price date?

    I also have to change the tag in the .ofx from USD to CAD before importing (the prices from yahoo are in CAD which I confirmed). That's not a big deal to fix before importing to Money, but if I could set the currency in sites.dat so the .ofx is correct that would be nice. Just a thought...

    Great work on these scripts guys!!

    ReplyDelete
  196. 1.In quotes.py, there is a statement
    'currency = "USD"' that you could modify. That way you would not have to edit your OFX file.

    2. You are not going to get Money to look at the DTPRICEASOF value. It is possible to use one of the DTPRICEASOF values to replace the time for what Money looks at. One thought would be to make that the first quote fetched. Or last....

    Now what if you are fetching quotes from various dates. You pretty much have to select one date without a major rewrite. You could have separate PocketSense folders with different lists....

    ReplyDelete
  197. Has anyone had success getting data from a Fidelity brokerage account? I'm successful with all my credit card and bank accounts and when I process Fidelity through setup, it returns with no errors, however, when the qfx file is passed to Money Plus no transactions or balances are found and Money tries to adjust security balances to zero. When comparing Money's logged response (rs.txt) to the xfr/Fidelity.qfx, I see that there are no statements even though the statements all have valid broker IDs and account IDs.

    Is there a way to see what QFX GetData creates in order to compare it with Money's request (rq.txt)?

    ReplyDelete
  198. UnderWay, I have had good success with getting data from a Fidelity brokerage account.

    If you change "Debug=False" to "Debug=True" in ofx.py, then you will get the request printed to the screen.

    You can change the properties of a command window by clicking on
    the [C:\.] icon at the top of the command window, and choosing Properties. You can increase buffer size on the Layout tab to let
    you scroll farther back. You can change other things on other
    tabs.

    In comparing the PocketSense request with the Money *rq.txt, note that Money uses OFX2 and PocketSense uses OFX1. The main difference is that all tags get end tags in OFX2, but some do not in OFX1.

    Thus (substituting square brackets for the pointy brackets and semicolon for backslash this time --third try)
    [ORG]fidelity.com[;ORG]
    in OFX2 would just be
    [ORG]fidelity.com in OFX1.
    My first reply to you had pointy brackets, and they got eaten. I made a postscript post with pointy brackets. Then the first reply post disappeared entirely. The second reply did not make sense, after the first reply disappeared. So I deleted the postscript reply. Now the revised post disappeared. I wonder what is triggering that.

    You will note that PocketSense will have
    [APPID]QWIN
    [APPVER]1800
    included in its lines. That should not make a difference, but in the sites.dat, you could make that field be whatever you like.

    Note that for testing this kind of thing, running setup item
    "7. Test Account" is a handy way to test a single account.

    You can increase the buffering for a Command Window (to let you scroll back farther) on the layout tab after clicking on the "C:\." icon at the top left of the window. You can adjust other things including size of font also.

    I have made an enhanced scrubber.py routine for Fidelity. That would have no impact on your situation. Instead it has to do with the dates stored for quotes that come in in the OFX.

    ReplyDelete