Tuesday, September 3, 2013

Adding support for non-US versions of Money (10-Sep-2013)

I have recently been working with JM, of France, in an attempt to provide support for quotes in non-US (e.g., European) versions of Money.  It's a bit tedious to debug when I can't test changes locally, but it looks like we have something that works, so I'm putting it out for general use.  Many thanks to hle for pointing in the right direction.  The release snapshot has been updated (10-Sep-2013), and is available for download.  If you want to try enabling quotes for a European version, add the following option to your sites.dat file (as described in sites.template):
ForceQuotes: Yes
Enabling this option requires that you accept one additional download statement each time Getdata is run, regardless of your CombineOFX setting. At this time, the option should only be enabled for non-US versions of Money, and only if you're having problems downloading quotes.  Additionally, Money must be open before executing Getdata with ForceQuotes enabled.  If Money is closed, it may not update correctly, but it shouldn't hurt anything either.  Re-running Getdata with Money open will overwrite whatever happened the first time.  Be sure to follow the additional "accept statement into Money" prompt before pressing Enter to continue.

While focusing on quotes, I also went back and rewrote the Yahoo "screen scrape" code, in hopes of providing support for stocks/funds that aren't explicitly supported by Yahoo's quote service.  This feature is especially critical for non-US versions of Money, so it made since to look at it again.  As before, capturing html output and finding the pieces that we want is troublesome, and all but guaranteed to eventually fail (when Yahoo changes their output formatting... again). Nevertheless, the beta code works for the few foreign funds that I've tested, and will hopefully continue to do so.  As before, you must enable the following option in sites.dat to use this feature:
EnableYahooScrape: Yes
Enabling the "scrape" option does not affect quotes that are correctly provided by the standard Yahoo quote service, since the routine only gets called if the primary quote request fails to return a valid response.

- Robert

153 comments:

  1. Hi Robert, just wanted to say that this new Beta worked perfectly for me for Canadian mutual funds from the Yahoo site, something which I never was able to get working before. Thanks for all your hard work in this!

    ReplyDelete
  2. One comment in one week. I'll take that to mean that either a) few folks have tried this, or b) it works so well that there's nothing to say :) Either way, I'm going to try to put it out as final today.

    ReplyDelete
  3. Release posted @ https://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2.

    ReplyDelete
  4. 2 out of 4 Vanguard accounts test with error message as follows, they have tested ok before and I did not make any changes

    OFX statement is missing required section.
    ** Review .\xfr\VANGUARD20130910220204250677.ofx for possible clues...
    An online error occurred while testing the new account.

    Is there an OFX reader that is recomended here?

    ReplyDelete
    Replies
    1. Gun: Verify that you're using the 10-Sep-2013 version. If so, I'm hoping that the error is actually "OFX statement is missing required <SECLIST> section", which likely means there were no transactions during the download period. Vanguard seems to leave out this section when there are no transactions (iirc)... but Money *requires* it (bombs if missing). I purposely block sending a an investment statement that is missing the section. You can set the minimum download period for your Vanguard site entry to something longer, to capture at least one transaction for the period.

      Just FYI... my Vanguard accounts are working fine w/ the new version.

      Delete
    2. thanks for the reply.
      I updated PocketSense to the 10-Sep-2013 version and tested the 2 problematic Vanguard accounts....same error message. Then I increased the minimum download period from 30 to 60 and tested again, this time success!!!

      Delete
  5. Robert,
    quotes have been working great thanks to you.
    previously when I downloaded quotes in my portfolio, it would update the daily $ and % change of each stock. It would also give me the total $$daily change and total% change of my portfolio as I had set it up.
    how come I don't get the figures now?

    ReplyDelete
    Replies
    1. Jeff: When you say "previously", I'm assuming you mean when previously auto-updating using the built-in "online update" feature of Money? The script package makes use of the OFX import function, but has no effect on some features that Microsoft updated via their built-in updates. There is an option in sites.dat to enable generating a quote summary outside of Money, but it only reports price change(s). If enabled, a quotes.htm file is created in the xfr folder. It does not tie into holdings/values of user accounts, however, and only provides a summary of quote updates.

      Delete
    2. yes. online update.
      you are refering the the outside browser in yahoo right. yes. shows the price %change.
      I will set up a differnt portfolio to track the other options.
      Thanks again for your massive undertaking of reserescting Money.

      Delete
  6. Robert:
    Thanks for all your great work on this. I am a new user, and I wonder if you can point me to some advice on how to get quotes working with a Canadian version of Money (2003). I have imported some test quotes into a dummy account in Money, but the price of the stocks in the real account are not updating. I would love to hear from anyone who has figured out how to get this working. Thanks, Richard

    ReplyDelete
    Replies
    1. Richard: Try setting ForceQuotes: Yes in your sites.dat file, and let me know how it goes. You'll be prompted to accept two statements at the time of download.

      Delete
    2. Robert: thanks for the suggestion. I set ForceQuotes: On and prices are now being updated in the Money account that holds the stock. This is very encouraging!
      There are a couple more issues you may be able to help me with.
      My default currency in Money is CAD. This means that if I buy a stock in USD, Money adds "US:" in front of the stock symbol. For example, I own MSFT in USD, and money sets the symbol to "US:MSFT".
      For this test, I have changed the currency for MSFT to CAD in the real account. Money then changes the symbol from "US:MSFT" to "MSFT". I have put the same stock in the dummy portfolio, and the symbol "MSFT" in sites.dat. With this change in symbol, the stock price for MSFT is updated in the real Money account (with the US price). Can you suggest any way I can get prices for US stocks without setting their currency to CAD in Money?
      The second issue I am having is that I don't have any accounts configured in sites.dat (because there are no OFX files available for accounts at my brokers). If I run getdata.py I get the message "No accounts have been configured.....". As a work around, I run setup.py, and enter [7] Test Account. I then get "Stock/Fund Prices" listed as Site 1. If I select # 1 as the Test Account, I can test "Stock/Fund Pricing Updates", see the quotes, and get the message "Download completed successfully." Is there any way to use getdata.py to get stock quotes without configuring any accounts?
      Thanks again for sharing the work you have done, and for taking the time to help me with these issues.
      Richard

      Delete
    3. 1. Run Setup and enable quotes. You can now run Getdata for quotes only.

      2. I'm not sure if this will work for the stock/currency issue, but it may. There is an option in the sites.dat setting for each stock/fund symbol, that tells the script what symbol to send to Money when loading. Try the following for your MSFT example:

      MSFT s:US:MSFT

      This tells the script to get the quote for MSFT from Yahoo, but send it to Money as US:MSFT. There is a note in your sites.dat file (and sites.template), just before the quote section, that describes this option.

      Delete
    4. Thanks Robert, this really helped. I'm not quite there yet, but I am making real progress, Richard

      Delete
  7. Hi Robert

    I've just come across your stuff today whilst looking for what could be done to get Money updating again.......

    I live in the UK and only use Money to update my Mutual Funds and Stocks. Bank accounts etc I do manually.

    I've made some progress installing Python and downloading the scripts but I've struggle a bit to understand what I need to do to configure the sites.dat. Nevertheless, I have entered some stock records and run setup.py. I can see that the correct prices are being picked up so I'm very hopeful that I can use this....

    At the moment, my biggest issue is that I can't seem to find any British Mutual Funds on Yahoo.Finance. Is this stuff hard coded to access Yahoo or can it be configured to pick up data from elsewhere?

    The default sites.dat includes one investment site, Vanguard.... Is this where the data comes from?

    You mentioned somewhere that you were doing something different for Europe. What sort of feedback are you getting on it?

    thanks for all your efforts on this, it could prove to be a godsend for me, Brian

    ReplyDelete
  8. Hi again Robert

    A little more progress this morning... I've now found how to access Yahoo Mutual funds but I still have an issue picking up quotes

    eg Aberdeen American Equity A is shown on Yahoo as GB00B0XWN143.L. When I enter this literal in the funds section of sites.dat and run setup.py I can see it retrieve the share prices but when it gets to this record error messages are generated and setup.py closes down before they can be read. Am I entering the wrong format for this Fund?

    thanks Brian

    ReplyDelete
    Replies
    1. Brian: I found a bug in the script when testing that fund. I'll get an update out soon, so stay tuned. As for the quote source, the scripts only support Yahoo, but you can try any of the Yahoo sites. The default setting for YahooURL is finance.yahoo.com, but it can be changed in sites.dat by removing the comment (#) for the setting, and changing the value. Again, only Yahoo finance sites are supported, but countries with an exchange seem to have their own. I'm assuming you figured out to enable the EnableYahooScrape option for most London Exch funds.

      Delete
    2. I updated the scripts. Before putting the update out for general use, I'm putting it up as a test "beta" version. Let me know how it goes.

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

      Delete
  9. Morning Robert

    I can confirm that your update appears to have fixed the issue with funds and their prices are now being picked up correctly. Great stuff!

    Just to reiterate, I am only interested in automatically updating Stocks and Funds in Money. All banking transactions etc I will handle manually. As a result, the only changes that I have made to sites.dat is to add lines to the Stocks and Funds sections. I haven't added any institutions or accounts.

    In my Money, I have several investment accounts for example
    Personal Portfolio which holds a number of stocks
    Aberdeen Account which holds various Aberdeen Funds
    etc

    I have added a small selection to sites.dat and set up a Money Ticker Account with the same entries (though I'm unclear why it is needed)

    When I run Setup.py and select option 6, I can see the correct data being extracted. If I then choose to send the data to Money

    1 A Money Import a File Screen appears with the messages "Import Summary" / "Import Completed"
    2 I click OK and
    3 A screen appears headed "You have no statements to read" but
    4 There is an unread entry listed quoting my Personal Portfolio with 11 items to read
    5 If I click on this 11 quotes are shown with the correct prices. Some items are in my Personal Portfolio and some are in other accounts.
    6 I am prompted to click to finish the reconciliation
    7 The screen returns to step 4 and the entry is no longer unread and shows 0 items

    All appears well but none of the prices in Money have been updated...

    If I run getdata.py I'm told that I have no accounts set up

    I'm unclear what I need to do now to "make things happen"., If you can offer any guidance/advice then I'd be very grateful, Brian

    ReplyDelete
    Replies
    1. Brian:

      1. Run Setup and enable quotes.
      2. Open Money. Always make sure that Money is running *before* running Getdata.
      3. Run Getdata.

      The reason for the "ticker account", is to match the quote statement in Money. If you have never done that, but it is importing, then there's something strange. The very first time that you import the quote statement, Money should prompt you to match it with an account. Well... that's how it works in the US versions...

      Delete
    2. Hi Robert

      thanks for the speedy reply

      I've run Setup a number of times in ignorance.... Maybe the first time it prompted for something and I defaulted or something, I can't remember

      My best guess is that, at present, the link has been formed to my Personal Portfolio Account rather than the Ticker Account (ticker account may not have been present when I first ran). Is there a way to undo this link and start from fresh?

      cheers Brian

      Delete
    3. Should have also said: you should create a backup of your Money file before configuring and testing the scripts. If you did, then just restore the backup copy, and start again. If you didn't, then you may want to read the installation instructions...

      Delete
    4. Hi again

      thanks for your response which pointed me in the right direction, I'd have been lost without it.......

      Things have taken a backward step but it may well be progress

      When I checked my investment accounts on Money, only one, my Personal Portfolio, was associated with an institution. This institution had nothing to do with it and so I cleared it out. None of my investment accounts (including Tickers) are currently linked to an institution. In the past, it wasn't necessary to link investment accounts to institutions????. Quotes came across and linked via tickers????

      Now, when I try to send the quotes to Money, I get the following error message

      "a communications error has occurred, if you continue to have problems call your financial institution's online services customers support"

      So, it appears that I have data (dummy institutions/accounts???) to set up somewhere. Could you point me in the right direct again, thanks, the pest, Brian

      Delete
    5. The best advice that I can give is to start with a fresh backup copy of your Money file, and follow the setup instructions for the script package. When you walk through the instructions, you'll see that it is also recommended to disable online updates, as they're no longer functional. I don't recall the specific error that you've listed, though.

      It shouldn't matter whether an account is associated with a specific bank, since the quote update will simply feed prices into the dummy (ticker) account... well, once it's setup and working. Quotes should not be feeding into an existing (real) account.

      Delete
    6. Guess what, I didn't take a backup doh.......

      The communication problem appears on various blogs but no sign of a solution yet.

      I can't open the xfer files.... Can you tell me if they are tagged with an institution?
      I'm grasping at straws but I wondered if the ticker account needs to be associated with this in order to create a link. Otherwise, I can't see why tickers is needed.

      Delete
    7. I'll first reply with a strong recommendation: have a backup plan for your files :) A simple drive failure can wipe out years of effort. That said, the US version of Money has a built-in backup protocol, so you may actually have backup copies somewhere, and not know it. Check your settings, and see if there is a backup path/schedule in Money itself. I'm not sure what the issue is with the error you're getting, but I'll think on it. As I don't use your version, the odds aren't great of me figuring it out.

      Delete
    8. Brian: Try the instructions given here for breaking online account associations. I'd probably try option #3 first, but it shouldn't hurt to try the others. Read all the instructions first, and please backup your file beforehand.

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

      The idea is to disconnect your existing accounts from any online statement associations. If successful, importing a new account statement (with a unique account#) will prompt you to match it to an existing account. At that point, you would choose your ticker account for the quotes statement.

      Delete
  10. Robert, back on 6/30/13 I had reported a bug in the Scrubber routine with ING Direct, which you responded to and fixed in July. I didn't get a chance to get back to this all summer. I just tried it and problem fixed. I just wanted to thank you for the fix and your dedication!

    ReplyDelete
  11. Hi Robert
    C'est moi encore...

    I've been going round in circles for the last couple of days and, whilst I still haven't cracked the problem, I have a clearer understanding of what seems to be happening.
    I recognise that it is my problem and I don't want to involve you in any time consuming efforts.
    However, if anything "springs to mind" then I would be very grateful for any help....

    Following the "How to re-associate an Online statement to the appropriate Microsoft Money account" pages....

    Method 1. I moved every account to "Not held at a Financial Institution". This configuration caused "Communication Error" on each run of Setup

    Method 2. The change of currency option requires an Interrnet Update of prices which is no longer available

    I've noticed that if I Move "My Portfolio" to any old institution then the import appears to be successfull.
    The import contains items for both "My Portfolio" and other investment accounts.
    The Money screen says "You have no statements to read" but there is an unread one present for the "My Portfolio" with 39 items present.
    I can click on it and see the quotes and click Finish to end the reconciliation. The items are reduced to 0.
    But no prices are updated in any of the accounts

    Method 3. I can't delete this statement either pre or post reconciliation.

    So, the link between Pocketsense and My Portfolio looks to be permenent and preventing updates from happening.

    Any thoughts?? Are there any other sites that you know of where I could raise this issue?

    Cheers Brian

    ReplyDelete
  12. Brian,
    Although the link Robert referred to has clues how to fix, because Online Updates are no longer working, I think by following each step to the tee you are messing things up as Online Updates should always be OFF. Instead try looking at my Reference Pages and see if they might be helpful.
    http://microsoftmoneyoffline.wordpress.com/fixing-ofx-investment-mismatch/
    http://microsoftmoneyoffline.wordpress.com/fixing-ofx-financial-institution-file-association/
    http://microsoftmoneyoffline.wordpress.com/reg-file-to-empty-the-statement-import-que/

    ReplyDelete
    Replies
    1. Thanks for this, I'll give it a whirl

      It has been stressed to me a couple of times that On-line Updates needs to be off... I'm not entirely sure that I have achieved this. All I have done is to uncheck the Update Prices/Update Automatically setting from the Portfolio Manager Screen. If I've got to do more than this please let me know

      Cheers Brian

      Delete
  13. Hi Cal/Robert

    They say that a little knowledge is a dangerous thing and I've only got a little knowledge but anyway here's some feedback...

    Fixing OFX investment mismatch didn't appear to be relevant to my problem
    Fixing OFX Financial Institution File Association ditto

    Reg file to empty the statement import queue, this looks really promising because if I could delete the (incorrect) statement of quotes on Money then I would expect that next import would ask me how to associate the quotes with my accounts....

    REGEDIT4

    [HKEY_CURRENT_USER\Software\Microsoft\Money\17.0]
    “Import Files”=-

    seems to be a small program that changes the registry. I was a bit nervous of using it and preferred to go into regedit manually and make changes that I could track and (if necessary) undo.

    This is the sequence of events

    Run Setup and download to Money
    No Import Files present in registry

    Click Import Complete
    Files now present shown as
    C:\DOCUME~1\Brian\LOCALS~1\Temp\~of9C.tmp
    C:\DOCUME~1\Brian\LOCALS~1\Temp\~of9D.tmp
    Change value to -
    Click OK Import Complete

    Money Screen says "You have no statements to read" but an unread item is shown....
    My Portfolio(39) download 24/09/2013 Bank Balance £0.23 Local Balance £53,929.72 Why was this imported rather than deleted?
    My Portfolio is underlined and appears to be clickable. Right click does nothing ( I would like to delete it)
    Left Click Review investment holdings screen. Items in the import file but not in My Portfolio shown suggesting I increase Quantities by 0.001
    Click Next
    Update My Portfolio:Position Matching Screen where I can either
    Add 0.001 shares to each holding not currently in My Portfolio or
    Do not update I will make changes myself - I choose this option

    Goes back to Money Screen says "You have no statements to read" read item is shown, number of items (0)

    No prices or qty changes in any of my accounts

    I'm unclear what's happened but it hasn't cleared out the bogus statement from Money. Just a thought.... Why does Money say "You have no statements to read" when there is an unread set of quotes? Is this why I can't delete it? ie If it said 1 would this allow it's deletion?
    Is the value (0) created within Money or passed via the Import?

    Like I said a little knowledge is dangerous.....

    Sorry for the long comment but it also documents things for me... I'll delete all my comments shortly

    Cheers Brian

    ReplyDelete
  14. Hi Cal/Robert

    This morning I modified sites.dat so that it now requests a single stock quote (BG British Gas) that is present in My Portfolio Money account. This was done to remove any potential issues relating to multiple quotes across multiple accounts.

    I then performed 3 imports and reset the .mny after each import to remove any possible updates made by the imports...

    Run 1

    All accounts in Money Unassigned. ie No financial institutions used. As I understand things, in an ideal world, this would provoke Money to ask which account holds BG and I would answer, "Tickers". This would set up a link for future imports.
    Unfortunately, this configuration generates a "communications error". This is a Money message and far from helpful. I don't know why Money takes issue with this but is unlikely to be a genuine communications error......

    Run 2

    Assigned My Portfolio to "Dummy" institution using the Move option. ForceQuote set to No. The import appeared to work but no prices were updated.

    Run 3

    ForceQuote set to Yes. The import appeared to work but no prices were updated.

    So, I conclude that somehow I have screwed up my .mny file and I don't know how to fix it. Fortunately, updating apart, Money appears to be functioning as normal. I can assign prices manually.

    I would like to make one final attempt to clear things up but I will need some help. The idea is to create a import file that contains a statement rather than just quotes. The hope is that if I import (but don't read) this file then just maybe I'll be able to delete it and break the link between Money and the import process. So that the next import would ask me to assign accounts.

    Could either of you tell me what are the minimum set of records needed for sites.dat to generate such a file and what values the fields should hold? I recognise that at this stage I may be asking a lot but any further help would be much appreciated. If this doesn't cure the problem then I promise to leave you alone and delete all my comments to tidy things up.......

    thanks Brian

    ReplyDelete
    Replies
    1. I wish that I could help Brian, but to be honest, I'm not even sure what "My Portfolio" means. When I look at my copy of Money Sunset, I manage individual accounts. Each of these can be an investment, bank, asset, etc. type account, but the only "portfolio" that I ever see is in the investments section. The scripts are intended to send data to accounts, one of which can be a "ticker" account for quotes. Any portfolio view into those accounts is really unrelated to account transactions.

      Delete
  15. "My Portfolio" is the name that I have given to an investment account on Money. I used it store my stock holdings.

    ReplyDelete
    Replies
    1. Well, that's simple. I was confused by what you meant all this time. Try the following.
      1. Open the .\xfr\quotes*.ofx file.
      2. Find the line that says <ACCTID>0123456789
      3. Change this to something random. Personally, I'd just add the letter 'A' to the end.
      4. Save the file, and then double click it.
      5. Money should prompt you to match a statement to an account. If it does, then great. Match this statement to your "My Portfolio" account. If not, then stop, and report.
      6. If the above worked, then change the account number again (e.g., change the A to a B), and repeat the import/match, but match to another account.
      7. Keep doing this until you have matched a statement to each account.

      Assuming your version works like mine, the above steps will effectively "disassociate" existing import accounts with actual accounts in Money, since Money only remembers a single (one) match per account. Matching a new import statement with a new ACCTID value to an existing account in Money *replaces* the previous match. So, if you then run the scripts for real, you should be prompted to match the quote statement to an account, since Money will no longer have a match for the 0123456789 ACCTID value.

      Worth a shot...

      Delete
  16. Thanks for this Robert, it looks very promising.....

    Just to clarify, should My Portfolio be unassigned or linked to Dummy financial institution when I attempt this?? If you're not sure that's fine I'll try it both ways but I'll await your response first

    Thanks again, I greatly appreciate your help with this, Brian

    ReplyDelete
    Replies
    1. I'm not sure what "dummy" financial institution is. My online updates are disabled for all accounts, but it shouldn't matter if there is a financial institution defined for "where is this account held". Online connections are independent of the account "institution" setting, but I'd assume that each account should have something defined for the institution name. I'm pretty sure that mine are.

      Delete
    2. I should have added a step. Disable ForceQuotes in sites.dat first, so that you have only one quote statement in the xfr folder after running Getdata. Use that quotes file to do the above steps. If ForceQuotes are enabled when you create the quotes*.ofx files, then you will have two quote files. One of these would work as I'm hoping, but the other may not. So... disable ForceQuotes, run GetData, and then follow the steps above.

      Delete
  17. I ask because the current ACCTID causes "communication error" if My Account is not assigned to an institution. Any institution appears to stop this happening, I call mine Dummy. I'm not sure what to expect when I add A to ACCTID but I'll find out in the morning.......

    ReplyDelete
  18. For My Account please read My Portfolio, sorry .....

    ReplyDelete
  19. A bit weird....

    Just to summarize at this stage, went through the steps that you listed to re-assign my investment accounts by changing the ACCTID. At each step I was prompted to link to an institution as expected.

    All seemed fine, but when I'd finished and reverted back to 0123456789 I wasn't prompted to re-link, it just went ahead with the import. So, something must still be linked to 0123456789????

    I'm going to repeat and include my bank accounts in the de-linking. Any thoughts in the meantime?

    ReplyDelete
  20. Still weird..... My bank accounts aren't offered as an option for the "Money Account for the selected file".

    So, I've changed the linkage for all my investment accounts but it still imports for 0123456789, (though prices aren't updated.)

    Getdata returns the message "No accounts have been configured. Run setup to add accounts" Could this indicate I still need to set something up?

    ReplyDelete
    Replies
    1. Did you re-link the "ticker" account too?

      Delete
    2. Yep.

      I think I've just noticed what is causing the problem but it will be a bit torturous to describe....

      When you double click on the ofx file, a Money "Import a File" Screen appears. In the top part, you can select Money accounts from a drop down box. In the bottom you have Account Type Brokerage and the ACCTID of the latest import file.

      For all but My Portfolio, when I select an account it shows the last ACCTID used for that account. However, when I select My Portfolio then the message is "Account number: unknown"

      So, it looks like the import process is not updating this account, but it continues to be linked to 0123456789 somehow.

      Unless you know otherwise this looks like the end of the road.....

      Another option, depending on how much work is involved, might be to let me have a private Getdata (and Setup???) which has a different default account to 0123456789???? Perhaps then the apparent data corruption on My Portfolio wouldn't matter

      What do you think?

      Delete
    3. When you run Getdata now with ForceQuotes enabled, are you prompted to accept one statement, or two separate ones?

      Delete
    4. Hi Robert

      apologies for not responding earlier but I have been away over the weekend...

      Still getting some strange results......

      ForceQuotes = No

      1. If no investment accounts are assigned to an institution the import always fails with "communication error". So I assign My Portfolio to "Dummy".

      2. ACCTID = "A" Import prompts to choose an account. I select My Portfolio. Account No: unknown
      3. ACCTID = "B" Import prompts to choose an account. I select My Portfolio. Account No: unknown ie remains unchanged
      4. ACCTID = "C" Import prompts to choose an account. I select Trust Fund. Account No: unknown
      5. ACCTID = "D" Import prompts to choose an account. I select Trust Fund Account No: "C" ie assigned ok during import
      6. ACCTID = "E" Import prompts to choose an account. I select Trust Fund Account No: "C" ie not updated (should be "D")

      ForceQuote = "Yes"

      7. ACCTID = "F" Import happens automatically. ie no prompt to select accounts. My Portfolio receives one update and so does Trust Fund

      I'm unclear whether this "strangeness" is down to bad data in my .mny file or whether there is a problem with the Import process. If you need anything clarified then just let me know, Brian

      Delete
    5. If you start Money, and then run Getdata with ForceQuotes enabled, and do *not* edit the quotes*.ofx file to change the acctid, what happens? Are you saying that it just quietly imports, with no "accept statement" dialog?

      Delete
    6. Reset .mny file from back-up. Only My Portfolio assigned to Dummy. Getdata imports automatically. No dialogue. I would expect this as My Portfolio appears linked somehow to 0123456789. It then takes me to "Review Investment Holdings" screen and suggests updating the holding of symbol BG by 0.001 price 11.820 cost 0.01. "Normally", the screen would just show the prices in the import and no qty update would be required. Gosh, writing is a lot harder than talking....

      Just to confirm, Run 7 had no dialogue, and I would have expected one.

      Delete
    7. I think I may be confusing us both....

      Run 7. Changed ForceQuote to Yes but didn't re-run Getdata. I just double clicked the existing .ofx with modified ACCTID. So, only 1 file was present. If I want to manually update following Getdata and ForceQuote = yes, is it ok to edit ACCTID in both .ofx files?

      Delete
    8. Make sure that your account is assigned to an institution (e.g., Dummy inst). Edit Control2.py, and change the following two lines to read as:

      #Debug = False
      Debug = True

      This enables Debug mode. Run Getdata, and load statements to Money when asked. You will see a more verbose output, but that's by design. Check to see if your quote values updated.

      Delete
    9. not very verbose....

      PocketSense OFX Download Python Scripts, Ver: 19-Sep-2013

      Download transactions? (Y/N/I=Interactive) [Y] y
      Download interval= 7 days
      No accounts have been configured. Run SETUP.PY to add accounts
      Getting security and fund quotes...
      Getting quote for: BG.L
      Quote result string: "BG GROUP",1180.50,"9/30/2013","11:35am",1192.00,"-0.96%"

      BG.L(BG.) 11.805 9/30/2013 11:35am
      Writing .\xfr\quotes.htm

      Importing .\xfr\quotes20130930200253865887.ofx

      Finished downloading data

      Upload online data to Money? (Y/N/V=Verify) [Y] Y

      Sending results to Money...
      Importing .\xfr\quotes20130930200253261966.ofx
      Open in the default browser (y/n)?n
      DEBUG END: Press to continue...

      Delete
    10. Should have added, nothing updated in Money

      Delete
    11. Did you map BP.L to the stock symbol used in your Money file in sites.dat?

      Delete
    12. Yes and, er no.....

      sites.dat BG.L m:0.01 s:BG. #BG GROUP

      However, I have set up STOCK BG as a MANAGED FUND in My Portfolio.

      I think this may well be a problem as I had a little play......

      Amended sites.dat to include a Managed Fund from my ABERDEEN investment account
      GB00B0XWN143.L m:0.01 s:GB00B0XWN143 #ABERDEEN AMERICAN EQUITY
      and ran Getdata.

      The import suggested adding 0.001 of ABERDEEN AMERICAN EQUITY. I allowed it to update units in Money.
      0.001 was added to ABERDEEN AMERICAN EQUITY in My Portfolio with the current price showing as a Buy.

      In fact, all the Stocks in My Portfolio were added as Managed Funds!!!!

      I've seen a note somewhere showing how to change from Managed Funds to Stocks. It involves editing each financial transaction. I've tried it and it does the job but, as a consequence of this action, all historical prices are lost (which is a big downside).

      If (in contrast to automatic updates), the Money Import process differentiates between Stocks and Managed Funds then I think I'm stuffed. Is there anyway of preventing this Buy happening and still recording Prices? If so, I could allow Fund "tickers" into My Portfolio account and at least automate the the Funds portion of my investments whilst adding Stock prices manually.

      I'm not sure whether I've explained this too well, if you need any clarification please let me know...

      Delete
    13. Robert, for your info....

      The current configuration with ForceQuotes = Yes is resulting in two imports. I'm not sure what the effect of this is.....

      Here's the output from Getdata showing the two separate imports

      PocketSense OFX Download Python Scripts, Ver: 19-Sep-2013

      Download transactions? (Y/N/I=Interactive) [Y] y
      Download interval= 7 days
      No accounts have been configured. Run SETUP.PY to add accounts
      Getting security and fund quotes...
      Getting quote for: BG.L
      Quote result string: "BG GROUP",1173.50,"10/1/2013","5:36am",1180.50,"-0.59%"

      BG.L(BG.) 11.735 10/1/2013 5:36am
      Getting quote for: GB00B0XWN143.L
      Quote result string: "GB00B0XWN143.L",0.00,"N/A","N/A",N/A,"N/A"

      Attempting screen scrape for: GB00B0XWN143.L
      Screen scrape csvtxt= "ABERDEEN AMERICAN EQUITY A (GB00B0XWN143.L)",1431.00,"9/2
      7/2013","4:00pm",?,?
      GB00B0XWN143.L(GB00B0XWN143) 14.31 9/27/2013 4:00pm
      Getting quote for: GB00B0LG6H52.L
      Quote result string: "GB00B0LG6H52.L",0.00,"N/A","N/A",N/A,"N/A"

      Attempting screen scrape for: GB00B0LG6H52.L
      Screen scrape csvtxt= "ABERDEEN UK EQUITY A ACC (GB00B0LG6H52.L)",163.00,"9/27/2
      013","4:00pm",?,?
      GB00B0LG6H52.L(B0LG6H) 1.63 9/27/2013 4:00pm
      Writing .\xfr\quotes.htm

      Importing .\xfr\quotes20131001105217523962.ofx

      Finished downloading data IMPORT HAPPENED HERE

      Upload online data to Money? (Y/N/V=Verify) [Y] y

      Sending results to Money...
      Importing .\xfr\quotes20131001105217340367.ofx AND AGAIN HERE
      Open in the default browser (y/n)?

      Delete
    14. I assume that the period after BG is a typo (s:BG.), or that the actual symbol in Money has that same period? I'm at a loss at this point. Any way that you can convert to Money Sunset? :)

      Delete
    15. I think that there maybe a free version of Sunset available to download. Will it be able to read by existing .my file?

      Delete
    16. Yes, Sunset is free. Not sure about the conversion for non-US versions, but it does convert earlier US Money files.

      Delete
    17. Hi Robert, some progress at last, it might even be working, I just wanted to check it out with you......

      Three issues have been hampering my progress and this is what I have done to address them

      1. Communications Error when no institution assigned. This seems to be a feature of UK Money 2005. My portfolio assigned to Dummy

      2. All my Stock accounts wrongly set up as Mutual Funds on Money. Sites.dat now only contains genuine Mutuals

      3. My Portfolio permanently attached to 1234567890. If you can't beat them, join them..... All the Tickers for my mutual funds added to My Portfolio. ie My Portfolio acts as the dummy Tickers account as well as holding stocks

      Now, when I run Getdata
      1 The first import completes automatically. I can let Money add 0.001 to each holding. If I don't prices aren't retained
      2 The second import completes automatically. I can let Money remove 0.001 from each holding.
      3 The prices are shown as Buys in Price History
      4 The transactions are listed as Add/Remove Shares in My Portfolio Account Register. The Column headed "C" shows an "E"

      Is this what you would expect to happen?

      Delete
    18. Yes, pretty much. If you set Debug=False, then the two "share adjust" events should happen automatically, but I'm not sure if you still have to accept the two adjustments. In the US version, passing the two statements "at once" does not require any action on the part of the user to record the buy/sell transactions.

      Delete
    19. Great! Thanks for your patience and time throughout. I've a suggestion re dates held in the import file but I'll log that separately tomorrow.

      I'm happy to delete all my comments if that's ok with you?

      Delete
    20. I can clean it up later, if needed. No problem.

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

      Delete
    22. This comment has been removed by the author.

      Delete
  21. The weirdest thing is happening to my Vanguard accounts after getdata.py:

    ** Review .\xfr\VANGUARD20131001091104333528.ofx for possible clues...
    An online error occurred while testing the new account.

    I have not made any changes and I get the same error on all Vaanguard but not on TD Ameritrade

    ReplyDelete
  22. Hi Robert:
    I wonder if you can help me again to get quotes download working with Money 2003.
    I have set up "Quotes" as a dummy account in Money, with some test investments in it, and entered the symbols for these investments in sites.dat. The first time I download quotes using setup.py after adding a new investment to sites.dat, the price of the new investment is updated in the real account in Money. (The first download for this investment is when Money uses "Position Matching" to reset the number of shares in the dummy account to 0).
    However, the next time I download quotes, the price is downloaded, and I can see it in the dummy Quotes account in Money, but after I complete the download process (by clicking Finish in Money's "Unread Statements" window) the price is NOT updated in the real Money account,
    To summarize, prices are updated in the real Money account the first time I test a new investment, but they are only updated in the dummy account, not the real account, after any subsequent download.
    I am clearly doing something wrong, but I'm not sure what.
    Thanks for any help, Richard

    ReplyDelete
    Replies
    1. Is this a US or non-US version of Money?

      Delete
    2. This is the Canadian version of Money

      Delete
    3. See the main text for this post (above). Enable both options in your sites.dat file.

      Delete
    4. I added two lines to sites.dat:
      ForceQuotes: Yes
      EnableYahooScrape: Yes
      Both variables are now set to "Yes" in the Stock and Fund section of sites.dat.
      When I test using Setup.py, quotes are updating in the dummy Money account, but not in the real account (i.e. no change in behavior).
      Also, if I run Getdata.py, I still get the message: "No accounts have been configured. Run SETUP.PY to add accounts".I have run "6 - Enable Stock/Fund Quotes" in Setup.py, and Setup.py sees "Stock/Fund Prices" as an account when I run "7- Test account". Could this be part of my problem? Is there a setting in sites.dat that I can check to see that Stock/Fund Quotes have been correctly enabled?
      As always, your help is very much appreciated. Richard

      Delete
    5. The ForceQuotes option is new, and not well tested with Canadian versions. In fact, most of this comment thread has dealt with it. Before digging in much further, I need to first verify that running Getdata today doesn't work either. I ask, because you can only import quotes once per date in Money. Once you import once, it won't update again until the quote date changes. If it doesn't update today, then I'm thinking that I need to add another step, to make ForceQuotes work with your version.

      Delete
    6. I got the same message today when I used getdata.py: "No accounts have been configured...." As I understand it, getdata did not try to download any quotes (because it doesn't see an account).
      I did download quotes for today using Setup.py (verified by viewing results in the browser). As before, they were updated in the dummy Money account, but not in the real account.
      QuoteCurrency is set to USD in sites.dat (and my dummy account is set up in US currency). Should I try changing QuoteCurrency to CAD, and changing the dummy account to Canadian Currency?
      Richard

      Delete
    7. The "no accounts have been configured" message is normal, but Getdata should still download the quote statement(s) if quotes are enabled in Setup. You should see the quote results on screen as Getdata runs. If it goes too fast, run Getdata with the "I" option (interactive).

      Delete
    8. I updated the code to have a slightly longer delay between the two quote statements when ForceQuotes is enabled. If this doesn't help, then I will add a manual pause between them. I'm hopeful that one of the two will help w/ the Canadian Money quotes not updating.

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

      Delete
    9. Robert, I am downloading the file ofxpy_pocketsense.zip from https://sites.google.com/site/pocketsense/home/msmoneyfixp1/p2. I just want to check that this file is updated with the latest code? Or should I be looking elsewhere for the updated code?
      Richard

      Delete
    10. Use the link in my reply above.

      Delete
    11. Hi Robert:
      Sorry for missing the link in your earlier reply. I have now downloaded the correct files.
      I ran Setup.py, entered 6 to Enable Stock/Fund Quotes, then Y to test Quote downloads, and Quotes were displayed with the message "Download completed successfully", then Y to Send the results to Money, and updated quotes were shown in the dummy Money account, before I clicked "Finish" in Money.
      However, after I clicked "Finish", the quotes were not updated in the real Money accounts.
      I then ran the updated Getdata.py, entered I for interactive, and got the error message "No accounts have been configured. Run Setup.py to accounts" (same behavior as before).
      So unfortunately, the code updates to do not seem to have changed things.
      Richard

      Delete
    12. I'm confused. I just setup a test version, with no accounts, but about 10 different stocks/funds in sites.dat. I enabled stock/fund quotes in Setup (option 6), and ran Getdata in Interactive mode. All stocks/funds were collected from Yahoo, and displayed to screen. The "no accounts have been configure" message shows first (as intended), and then the quotes download. I'm not sure why you're Getdata run isn't getting quotes, if Option 6 is enabled. Just to verify, if quotes are enabled, the menu will read as follows:

      ----------Main Menu-----------
      1. Add or Modify Account
      2. List Accounts
      3. Delete Account
      4. Encrypt account settings

      6. Disable Stock/Fund Quotes
      7. Test Account
      8. About
      0. Save & Exit
      ------------------------------
      Selection: [0]

      Delete
    13. Robert:
      Thanks for your patience! I have found my mistake with running Setup.py. After running Option 6, I was running Option 7: Test Account (which worked), but I never ran Option 0, Save and Exit. I have now run Option 6, followed by Option 0, Getdata now gets quotes.
      However, although quotes are downloaded and updated in the dummy account in Money, they are still not being updated in the real Money accounts. ForceQuotes and EnableYahooScrape are both set to yes in Sites.dat.
      Richard

      Delete
    14. OK. Beta updated again for testing (use same link above). You will now be prompted to continue after the first ForceQuotes statement.

      Delete
    15. Robert:
      The download process worked properly, with the prompt in Getdata.py, but as before, the updated quotes are downloaded to the dummy account in Money, but when I click "Finish", the quotes are not updated in the real accounts that hold the stocks.
      Richard

      Delete
    16. When prompted to continue, go into Money and accept the first statement. Then, go back to the Getdata prompt, press return, and accept the second statement into Money.

      Delete
    17. Robert, the quotes are now uploading into the real Money accounts after the first time I go to Money and accept the statement. There doesn't seem to be a need to go back to the Getdata prompt, press return and accept the statement a second time. Thanks so much for getting this to work for me. Richard

      Delete
    18. Are you saying Money doesn't prompt you after the second statement is sent? I can't test your version, so I'm just going by what you say, and then guessing at what's happening. The only difference between the current beta and the last is the prompt between statements, so having the prompt does matter. The second statement (after pressing Enter) clears the amounts recorded by the first statement... otherwise, you would show a balance in the dummy quotes account, and the quotes would fail to record on the next update.

      Please let me know if this approach continues working for you, and I'll update the release version to work the same way for non-US versions.

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

      Delete
    20. Money prompts after both statements.
      I now understand what should be happening. The first Getdata statement downloads the quotes and sends them to Money. Money displays the dialog box: "Review investment holdings", and the downloaded quotes/prices display correctly for each investment. When I click "Next", Money displays the dialog "Update Quotes: Position Matching", indicating that .001 shares will be added to the dummy account for each investment. I choose "Yes, update my positions as indicated below" and Money adds .001 shares to the dummy account, with the newly downloaded price. The price of each investment is then updated in any real account that holds that investment. The second Getdata statement removes the .001 shares that were added to the dummy account by the first statement. Perfect!
      When I tested this yesterday, it worked as you would expect. Checking the "Investment Transactions" report in Money for the dummy account confirmed that .001 shares were first added, and then removed, for each investment when Getdata was run. Prices for each investment were updated in all real accounts.
      When I tested this today, the "Review Investment Holdings" dialog confirmed that Getdata had downloaded the correct quotes into the dummy account in Money. I was again able to select "Yes, update my positions..." and click "Finish" , which SHOULD have added (or removed) .001 shares from the dummy account. However, Money's "Investment Transactions" report did NOT show the .001 shares added, and then removed, for each investment. Therefore the prices for these investments were not updated in the real accounts.
      I can't think of anything I am doing differently that would explain why shares were added and removed, and price updated, yesterday, but not today.

      Delete
    21. Note that the Money OFX import only allows a single entry per "statement date". The statement date for quotes is the "most recent quote date/time". If today's download grabbed quotes before Yahoo updated COB prices today, then Money wouldn't update anything. If quotes were updated earlier today, then they probably won't update again today either. That may not be the case here, but I thought that I'd mention it. Basically, quotes can only be updated once per day... which is identical to if you went into Money and manually recorded a price.

      Delete
    22. Thanks, that could be the issue here, although the date of the most recent download (which didn't update prices in the real accounts) is one day later than the date of the last successful update.. To be sure, I will wait a couple of days, and then try again. Hopefully, everything will work as it should.

      Delete
    23. You shouldn't have to wait that long, but it's usually best to wait until after ~ 7pm eastern, so that you're sure to get the "close of business" date/price for the day. Funds, for example, always return the "last closing price" until the fund prices are updated again.

      Delete
    24. Just to be sure, I waited until today to test downloading quotes and updating prices. The process appeared to work, with the expected dialogs in Money, but no shares were actually added or removed in the dummy account. Looking at Investment Activities for this account,in Money, for any of the test investments, shows .001 shares added and removed multiple times as a result of testing on Oct 7 and on Oct 8, once on Oct 9, but no shares added or removed today, Oct 11.

      Delete
    25. Not sure. I'm assuming that you are following this procedure:
      1. Run Getdata
      2. When prompted to press Enter to continue, you go into Money and accept the first statement.
      3. Return to the Getdata prompt, and press Enter to continue.
      4. Go back into Money and accept the last statement.

      Delete
    26. Yes, that is exactly what I am doing. It is very strange. I agree that Money should update the positions after I accept the first and second statements, but the .001 shares are not being added to or removed from the dummy account.

      Delete
    27. Considering all the activity prior, try deleting the dummy account, and creating it again. Run Getdata, and re-match the statement to the new account.

      Delete
    28. On Oct 11 I deleted the dummy account in Money and then recreated it. Stock price updates on Oct 11 worked properly. I tested again today. Prices were updated correctly in the dummy account, but the transaction dates were left set to Oct 11. The incorrect dates don’t appear to affect how the prices are updated, so this is not a problem (although it would be helpful if the transaction date was updated correctly).
      The remaining problem I have is that some prices are downloaded from Yahoo in CAD. Money assumes all downloaded prices are in USD, and converts prices for all Canadian stocks from USD to CAD. When the price was downloaded in CAD, this gives a wrong result. Would it be possible to set up 2 accounts in Getdata, and two dummy accounts in Money, one in USD for stocks which get prices downloaded in USD, and one in CAD for stocks which get prices downloaded in CAD? Or is there some way for Getdata to tell Money which prices are downloaded in CAD?
      Perhaps this problem has already been solved, but I didn’t see the solution.

      Delete
    29. Hello Richard,
      My default currency is €
      To avoid this conversion problem I have created an account for all USD stocks, and I have set the contry to " United States"
      See picture @ the following link.

      http://hpics.li/4b998f9

      PS: is there a better way to publish picture in this blog ?

      Delete
    30. Hello Richard,

      My portfolio default currency is €
      For USD stocks you have to specify the country so the appropriate currecy will be used
      See details in the followin link

      http://www.hostingpics.net/viewer.php?id=569287Google.jpg

      Delete
    31. Thanks Papilou. I have also specified the country for each of my US stocks as US (as you show for Google). My problem is that all prices are downloaded into one dummy account, which is set up in USD. When prices for Canadian stocks are downloaded into this account in CAD, Money assumes they are in USD, and "converts" them back to CAD. As they are already in CAD, they shouldn't be converted. How do you avoid this problem with your US and Euro stocks?

      Delete
    32. Richard: As of now... no, the scripts do not directly support multiple quote currencies. Let me think on this, and see how best to handle it. There are multiple options, but I need to pick one. Other than currencies, I'm assuming that the beta is working for you?

      Delete
    33. Yes, the beta is working fine now. Prices and transaction dates are downloading and updating correctly. Thank you!
      At this point my understanding of Python (and OFX) is minimal, so any suggestions from me wrt multiple currencies may not be worth much, but I wonder if it would be possible to configure two "Stock/Fund Prices" accounts in sites.dat, and have Getdata prompt to download quotes for each account separately. The first account in sites.dat could download USD prices into a dummy account set up in USD, and the second could download CAD prices into a different dummy account set up in CAD. It would be up to the user to put the appropriate stocks and funds in each account. Conceptually, that would work, but I don't know if anything along those lines would be feasible. Richard

      Delete
    34. @Richard, @Robert

      I looked in detail to one of the US stocks: KO
      In the ofx file, you can see the following data:



      KO
      TICKER

      Coca-Cola Company
      KO
      37.66
      20131015173300[-5:EST]



      The value for KO is correct 37,66 but after import into Money the value becomes 49,287..... (Euro?)

      Regarding the date stamp the ofx show 15/10/2013 (dd/mm/yy) but money buy (achat) stamp became 16/10/2013
      I invite you to see the post from btsman October9,2013 at 7:43 AM regarding the stamp date

      Yesterday I ran the script @ 23:30 local time (GMT+2), but I wonder what will be results when I run the script before the stock exchange close in the US.

      Logically since the import works once a day, we should only get the last available closing quote and the closing date

      See the screenshot of the last import here

      http://img11.hostingpics.net/pics/26204621KO.jpg


      @Richard, you where right!

      Although I have placed the stock in a USD currency account, the import process has converted the the value in EUR.
      You should therefore disregard my previous post on this matter

      Christian

      Delete
    35. Why the correct value for KO (37,66 USD) became 49,287 ?
      Since the default currency setting is in Euro, Money imported all position (Add Shares) as if they where EUR. It ignored that the currency rate setting to 1. So it calculated one KO share to be 37,66 Euro that is about 49,287 $. Pity that this is not true in the moment!!!

      Delete
    36. I thought that I understood the question re: multiple quote currencies, but now I'm confused. Can one of you answer these questions:

      1. Does Yahoo provide quotes in multiple currencies? I haven't tested this myself, and only use US and CA servers for testing.
      2. If a quote statement has the correct currency (e.g., USD), and the Money account has the exact same currency, does the import work correctly?

      This comment string is getting pretty long, so lets move it to the bottom on reply (i.e., new comment). It's easier to find the last comments at the bottom, than searching through all of it.

      Thanks.

      Delete
  23. Does anyone know if there is an OFX download available for ShareBuilder 401K (http://www.sharebuilder401k.com/)? Mint.com is able to connect and download transactions using the institution name "Plan Administrators Inc.", www.pai.com, which are the administrators of the plan and where you login at. There is no indication of an OFX download on the website, but was hoping there is one and just not advertised. Thank You.

    ReplyDelete
    Replies
    1. Not really, but you may want to look in this file. Look at settings for ShareBuilder and Capital One.

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

      Delete
  24. Hi Robert

    I've had some confusing results over the past few days... I think that the dates used by Money are responsible......

    Also, I've gotten differing results depending if Debug is True or False..

    Firstly Force Quotes

    If I my understanding is correct then this is designed for non-US versions of Money. Unlike the US version, they add fractional stock buys even if the Money stock holding is 0.
    So, a second import is created which removes these fractional holdings.

    With Debug = True this seems to work fine but when False neither Add nor Remove shares occurs. So, no prices are updated.
    The sequence behind the imports may account for this because if I manually import I can get the transactions to happen.
    The last few comments may mean that you are already looking into this and that I should download a new Getdata????

    Secondly Dates

    Quotes obtained from a single extract from Yahoo may be associated with a variety of dates. Typically in the real world, stocks will be current day, mutuals will lag by a day or so, maybe more...
    The import file correctly reflects this for example
    DTSERVER>20131008095856 current date
    DTASOF>20131007160000[-5:EST] set by Python????
    DTSTART>20131007160000[-5:EST] set by Python????
    DTEND>20131007160000[-5:EST] set by Python????
    DTPRICEASOF>20131004160000[-5:EST] An "old lagging" mutual quote
    DTPRICEASOF>20131007160000[-5:EST] The most recent mutual date
    (NB my file current only contains mutual ie no Stocks)

    Any transactions created by an Import are all set with the same date, I'm not sure what it's based on but DTASOF is my best guess as this file created transactions for 20131007.....

    I won't go into details now because it gets complicated, but various scenarios occur depending on whether this date for this fund has already been used....
    I think that a (pair of) separate import file needs to be created for each date that quotes are found ie If Yahoo provides quotes dated with say 4th 7th and 10th of October then 3 import files should be created and loaded separately into Money.
    This would avoid creating invalid (and irreversable) Money records for quotes that are "old". I can explain more if needed.... I also think that, at least potentialy, this could be relevant to US versions of Money....

    I don't know whether you will want to take any of this further but, if you do, then I'm perfectly happy to help with testing etc..... Brian

    ReplyDelete
  25. Brian: Grab the latest beta @ sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    Ensure that ForceQuotes is enabled. Run the new Getdata, and when prompted to continue, go into Money and accept the first statement. Then, go back to the Getdata prompt, press Enter, and accept the next statement.

    As for the date issue, yes, that's how it works. Supporting multiple dates via multiple files would be messy, if it worked at all, as Money only accepts a single statement per account at a time (hence the new beta for ForceQuotes above). The quote date will be the time of the last quote, and applies to all quotes. Keep in mind that the only reason for quote imports is to grab the last quote price for a security. The exact date really doesn't matter when determining what the value is today, but we try to keep it reasonable by using the most recent date/time supplied.

    Let me know if the new Beta works for you.

    ReplyDelete
    Replies
    1. The new beta version appears to work fine......

      Pity that multiple files would be messy but, I guess that, most people (myself included) will be happy with a good indication of their portfolio's worth. It makes reconciliation to other systems a bit of a nightmare though....

      thanks again for all your help, Brian

      Delete
  26. Hello Robert,
    I have discovered your blog yesterday, and i have made some test.
    I was very enthusiastic to discover that you have worked on a solution for non US version of Money
    I am using Money2004 (FR) V12.0 .(the french version) and would like to import stock and mutual funds quotes through this OFX process.

    The first test I made, to check feasibility, was to download the Sunset Version and tried to open my .mny file. ----> This did not work!
    I decided therefore to export one placement account from my2004.mny in a .qif file and import it successfully into Sunset.

    I have followed your instructions and succeed to update my European quotes into Sunset without problems.

    Today I have removed Sunset ans re-installed Money2004, but I am unable to obtain the quotes to be updated in the portfolio, although the ofx file loaded successfully.

    Any idea on what I did do wrong ?

    ReplyDelete
    Replies
    1. I'm debugging the non-US implementation as comments come, but you're welcome to try it and provide feedback too. Download the latest beta version @ sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

      Follow the instructions at the start of this post, enabling ForceQuotes and EnableYahooScrape options in sites.dat. When running Getdata, you will be prompted to continue after the first quote statement sent to Money. Go into Money at that time, and accept the statement. Go back to the Getdata prompt, press return, and then accept the remaining statement(s) into Money.

      Delete
    2. p.s. I highly recommend that folks migrate their data over to Money Sunset whenever possible. I'm trying to add support for non-US versions, but it's proving difficult to develop/troubleshoot when I don't have a copy myself.

      Delete
  27. @robert,
    Thanks for your efforts trying to solve non us version of money.
    So far i used the released version of pocketsense with the following parameters:
    QuoteCurrency: EUR # Currency for quotes. Default = USD
    EnableYahooScrape: Yes # Try a screen scrape if the primary Yahoo csv interface fails
    YahooTimeZone: -5:EST # Timezone rule for the Yahoo server (default = -5:EST)
    ShowQuoteHTM: No # Always show quotes.htm from Getdata
    AskQuoteHTM: Yes # Ask to show quotes.htm from Getdata (overrides ShowQuoteHTM)
    ForceQuotes: Yes
    I will try the beta version,.. this will take somes days since MM update only once a day,
    I keep you posted on the results.
    I will not be surprised that the problem is similar to what conduct hle to add the parameter " Incrementally Increase Share Count" on his script.
    Thus let's wait ans see....

    ReplyDelete
  28. Hello Robert
    It looks like this beta version give better results.... lets see tomorrow...

    http://hpics.li/3f17f5f

    ReplyDelete
  29. Firstly, thanks Robert for the impressive work with the python scripting that is keeping a trusted product alive. A question to you and those that only capture quotes into MS Money Plus Deluxe Sunset is the behaviour of the portfolio performance view of the “TotChng” and “last update” column. In both cases, these columns are now blank after successive downloads. Is it because the new quotes are imported as “update” and not “online” (see price history/update prices/update prices manually for money history with source column)?

    ReplyDelete
    Replies
    1. Yes. The script brings quotes in the back door, so to speak, but cannot update some of the (no longer active) built-in update parameters.

      Delete
    2. Alright ... nevertheless, pocketsense "updates" do yield annual returns and extends the life of this product. Thanks for your reply.

      Another question that involves outdated/past fund price which Yahoo only updates once per week. With each download/upload of the ofx when "getdata" is executed, is it possible to create a flag that ignores older quote dates? This would prevent an outdated fund price or stock closing price detected during exchange holidays from being uploaded to Money. The rule would only create ofx quotes for prices with the current date rather than assume all prices are current. Your thoughts.

      Example, see http://ca.finance.yahoo.com/q?s=BMOAMRICAINR.TO (last date October 9th)

      Frank

      Delete
    3. Money uses a single date field in the statement for *all* quotes, and there is no graceful way around it. The script therefore uses the most recent quote date for all quotes.

      Delete
    4. In the quotes.py, would an if statement to check if not date.today(), then skip the quote symbol? ... prior to creating the ofx for the current day? I'm sure this has been tried ... only guessing and presenting an idea. Thanks again - Frank.

      Delete
  30. To All:
    Am I the only one who is no longer able to download from Bank of America?

    Everything worked fine until Saturday when I got error messages the system was down, and since then I now get error messages:

    There has been an error with your session. Please call your Customer Service Representative with reference number 2000.

    Of course the service rep was no help, all he could say is that my account which was still classified as "Money" had not worked for five years. It did not matter that it had worked the Friday before. Anyhow, I had him change the account to be a Quicken account and proceeded to test again after making the appropriate changes in my sites.dat file, still no go.

    I did use Quicken Deluxe and downloads worked.
    Then I used Moneydance. Downloads worked.
    PocketSense still fails on two computers ( Vista and Win8).

    Anybody have any ideas?

    Ray

    ReplyDelete
    Replies
    1. Bank of America SOLUTION :)
      I downloaded a QFX from their web site and poked around. I noticed that it had an FID of 5959 instead of 6812 that I was using. Changed over the FID and presto, downloads work again.

      I still use appid "Money" and appver "1600" and that was working fine. Interestingly in the process of debuging I changed to QWIN/2100 and found that with the new FID the error message told me that my account was not set up for that product.

      Delete
    2. Thank you for the SOLUTION.

      I changed the FID from 6812 to 5959 and it worked. I used the default QWIN/2200 .

      I changed from Money/1600 when my account was changed from a Money account to a Quicken account.

      I will have to try and remember about looking at the download from their web pages.

      Thanks again for the help.

      Ray

      Delete
    3. AWESOME! I had the same problem - my settings were FID 6812, QWIN, 1800
      Changed to FID 5959, QWIN, 2100
      and it's working again.
      Prior to this posting, I also trying calling Bank of America who were absolutely useless.
      Thank you so much for the help!

      Delete
  31. Ray - we have the same issue. Stopped working about a week ago. Do you know if Moneydance uses OFX or QFX? At least on the web, BoA no longer offers OFX downlinks.

    Carlos

    ReplyDelete
  32. Re: Bank of America: If Moneydance works, then check their settings file for parameters. I've found this to be very useful.

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

    ReplyDelete
  33. Bank of America still offers QFX downloads from their website. Click on account after signing on, and then the download link while in the activity tab to select "WEB Connect for Quicken 2011". You will have to "save" though rather than "open" as you will have to manually "scrub" the file of any lines containing the tags CORRECTFITID and CORRECTACTION before processing, as Money doesn't know what to do with those tags.

    ReplyDelete
    Replies
    1. From what I gather, changing the BOA FID value to 5959 works with the scripts. The default appID and appVer values should work. I don't use BOA, so I can't test.

      Delete
    2. The FID change is what did it for me. The reason I had to change the appid and appver is because I had the bank change my account from a Money account to a Quicken account. I am not sure what other banks do, but BoA does key on the account credentials and appver and appid. It is the reason why Moneydance did not work for me in the past, since Moneydance assumed a Quicken account. With that change, I was able to try Moneydance.

      The real shame is that BoA technical support could not tell me that the FID changed or explain why it had worked for the last five years. They claimed it had not worked since 2008 when MS stopped Money Support.

      At least now I have alternatives (though not good ones) to using Money when the bank messes up.

      Ray

      Delete
  34. Multiple Currencies
    Yahoo provides quotes in multiple currencies. Quotes are in USD for all stocks listed in the US. They are in other currencies for stocks listed elsewhere. If a stock is listed on the NYSE and the TSX, quotes are available in both USD and CAD.
    For example, the symbol ABX gets a USD price from the NYSE; the symbol ABX.TO gets the quote for the same stock in CAD from the TSX. The CAD price from the TSX is approximately equal to the USD price from the NYSE, multiplied by the USD->CAD exchange rate. Both the US and CA servers will provide these quotes, using the same symbols.
    Quotes for stocks listed on the TSX, but not listed in the US, are only available in CAD, from both the US and CA servers. For example, XEG.TO gets a price in CAD, regardless of which server is used. (This stock is not listed in the US.)
    My dummy quotes account in Money is set up in USD, so Money assumes all quotes are downloaded in USD. The download works correctly for all quotes downloaded in USD. If the quote is for a stock that is in Money in USD, Money uses the price as is. If the quote is for a stock that is in Money in CAD, Money converts the price from USD to CAD. Again, this is correct.
    The download does not work correctly when quote is downloaded in CAD for a stock that is in Money in CAD. Money assumes the price downloaded into the dummy account is in USD, and multiplies it by the USD->CAD exchange rate. The stock price in Money is therefore incorrect when the download and conversion process is finished.

    ReplyDelete
    Replies
    1. Got it. The simplest change would be to add the option for users to define a unique account number for their quotes ofx statement. The user could then create multiple instances of the script package (separate folder for each), and setup each for a unique currency/account pair. This would not take long to implement, would use the same setup procedure for each, and would have no impact on existing users.

      The other option is to integrate variable currency into the main routines. This can be done, but will be more complicated from a coding perspective, due the script structure. Setup instructions would also need to be rewritten a bit to describe how/why it works. I also have to be careful not to negatively affect existing account setups.

      Option one is the quickest and easiest to support, while option 2 provides the most cohesive solution (although a bit of a hack for how the code is structured). I probably can't work on it for a couple of days, so I'm open to opinion between the two.

      Delete
    2. I would go with option 1. I experimented this approach (2 instances of the package in 2 separate folders, with 2 different Sites.dat files), but of course Money loaded both OFX files into the same dummy account, so it didn't work. With unique account numbers, so Money could distinguish between the different OFX files and load them into different dummy accounts, this would be a good solution.

      Delete
  35. New beta available for testing. A new option has been added named QuoteAccount, allowing the use to define a different bank account number for quote statements. The account number can be any value, so long as it doesn't conflict with existing (real) bank account numbers in your Money file. See sites.template for more info.

    Download @ https://sites.google.com/site/pocketsense/ofxpy_pocketsense_beta.zip

    ReplyDelete
    Replies
    1. Robert, I really like this approach, but I couldn't quite get it to work.
      To test, I created two dummy investment accounts in Money: QuotesCAD in CAD currency, and QuotesUSD in USD currency.
      I created two PocketSense folders, one for CAD quotes and one for USD, with different Sites.dat files in each folder.
      The Sites.dat file in the CAD folder had:
      - YahooURL: http://ca.finance.yahoo.com
      - QuoteCurrency: CAD
      - ForceQuotes: Yes
      - QuoteAccount: QuotesCAD
      - symbols for quotes from the TSX.
      The Sites.dat file in the USD folder had:
      - QuoteCurrency: USD
      - ForceQuotes: Yes
      - QuoteAccount: QuotesUSD
      - symbols for quotes from the NYSE or NASDAQ.
      Test 1. I ran Getdata.py from the CAD folder. In Money, I selected QuotesCAD as the account for the import. I got the error message: “Your file import completed, but 1 item could not be processed. File import: Your statement contained a default currency (USD) which was unrelated to the currency of the account ‘QuotesCAD’…”. No quotes were imported.
      Test 2. I ran Getdata.py from the USD folder. In Money, I selected QuotesUSD as the account for the import. The download worked properly, prices were imported into QuotesUSD, and updated in Money.
      Test 3. I ran Getdata.py from the CAD folder a second time. The quotes for the CAD stocks were imported into the wrong account, QuotesUSD.
      It appears that:
      1. Setting QuotesCurrency: CAD is not telling Money that the quotes are in CAD (see Test 1 and Test 3)
      2. Money is not reliably reading the account associated with the download (see Test 3).
      I really appreciate your efforts to get quotes working in multi currencies, Richard

      Delete
    2. Richard: Before digging into this, let's verify/try a couple of things.
      1. You don't have the comment symbol (#) in front of QuoteAccount setting line
      2. If not, then try a different account format. I tested something like 1234567USD and 12334567CAD w/ mine. I don't know that it matters, but Money may not like the pure alpha account numbers.

      After running Getdata, look at the .\xfr\quotes.ofx file in a text editor, and verify that ACCTID and CURDEF are populating correctly.

      Delete
    3. Robert: I checked that there is no comment symbol in the QuoteAccount setting line.
      I deleted the previous dummy accounts, and created two new dummy accounts:
      123QuotesUSD; a/c #: 1234; abbreviation: 123QUS; currency USD
      456QuotesCAD; a/c #: 4567; abbreviation: 456QCA; currency CAD
      I ran Getdata.py from the CAD folder first, and linked the download to the a/c: 456QuotesCAD. I got the same error message as before: "Your statement contained a default currency (USD) which was unrelated to the currency of the account ‘456QuotesCAD’…”.
      Running Getdata.py from the USD folder, and linking to the a/c 123QuotesUSD, worked with no error messages.
      Richard

      Delete
    4. Did you review the quotes.ofx file to verify the currency and account id fields?

      Delete
    5. Sorry, I did not check that. In all the .ofx files I see:
      < CURDEF>USD
      < ACCTID>0123456789.
      This is true whether Sites.dat has:QuoteCurrency: CAD and QuoteAccount: 456QuotesCAD, or QuoteCurrency: USD and QuoteAccount: 123QuotesUSD.
      After checking these values, I ran Getdata.py from the "CAD" folder to get two new .ofx files. I edited ACCTID to 456QuotesCAD and CURDEF to CAD in one of the two .ofx files. I then imported the edited .ofx file into Money. I selected the account 456QuotesCAD in Money, followed the other prompts, and the CAD stock prices were updated without any errors.
      Richard

      Delete
    6. When you run Getdata, what version does it say? It should be "Ver: 21-Oct-2013 Beta". I'm wondering if you are using the latest Beta version from the link above, or if you're using an older version?

      Delete
    7. Richard: I'll be out of pocket for a few days, but if you have a chance this morning to send me a copy of your CAD sites.dat file, I'll take a look before I leave. Otherwise, it will be a few days. Send to pocketsense1 /at/ gmail /dot/ com. Something is odd, as I mimicked what you described above, and it works fine on my setup.

      Delete
  36. Users that enable the ForceQuotes option will notice that you are instructed to accept a ForceQuotes statement into Money *before* proceeding to load the remaining statement(s). This is a pretty conservative approach, but it guarantees that Money doesn't jumble up the import files (put them out of sequence). I will be curious to hear what happens if a user *does not* accept the first statement into Money, and simply presses Enter to continue. I'm going to guess that it may work sometimes, but not for others... but that's a guess, as I can't test these options on Money Sunset (US).

    ReplyDelete
  37. JP Morgan Securities question:
    The only parameters settings I can find are the ones listed with Moneydance.
    But when I test with setup.py i get an error message.
    I tried to publish the ofx file, but I gues I am not allowed: "Your HTML cannot be accepted: Tag is not allowed: CODE"

    ReplyDelete
    Replies
    1. If your problem is an error message with setup.py, I think you want to say what the actual error message is.
      Also, I suggest your post the contents of your <site> entry. Leave out the < and > or replace them with &lt; and &gt;

      Cal Learner

      Delete
    2. Error message:

      J.P. MORGAN SECURITIES : 739XXXXXX : Getting records since: 20131001
      OFX message contains ERROR condition
      ** Review .\xfr\J.P.MORGANSECURITIES20131101001544112242.ofx for possible clues
      ...
      An online error occurred while testing the new account.

      sites.dat:


      SiteName : J.P. Morgan Securities
      AcctType : INVSTMT
      fiorg : PCS
      fid : 7315
      url : https://ofxpcs.toolkit.clearco.com
      bankid :
      brokerid : chaseonline.chase.com
      ofxVer :
      appid : QWIN
      appver :
      mininterval:
      timeOffset :

      Delete
    3. I don't use JP Morgan, but the settings for MoneyDance are a little different than yours. Note that the latest default for appver in the scripts is 2200, so if JPM expects a lower version, then it must be explicitly defined. I'm not sure if brokerid matters, but it's different.

      appver=2100
      brokerid = https://ofxpcs.toolkit

      Delete
  38. thanks for taking the time.
    I played around with different appver and it may have helped, because now by looking at the ofx file the only error that I can detect is "invalid ID". That I don't understand, because I log on with that ID. The only explanation I have is, the investment account is bundled with my chase credit card under the same login and that might cause a conflict.
    It could also be because Chase only works with Quicken and charges a monthly fee of $10.00 for that service. But anyhow, I'll call the bank and maybe they can separate the investment account from the credit card account.

    ReplyDelete
    Replies
    1. See http://microsoftmoneyoffline.wordpress.com/moneydance-ofx-settings/ has some entry info.
      CL

      Delete
  39. Live snapshot updated to 14-Nov-2014 release. This release includes all patches related to support for non-US versions of Money.

    ReplyDelete