Tuesday, September 23, 2014

Vanguard Direct Connect Upgrade: Sept 2014

(comments closed)

Some users, including myself, have had problems connecting to Vanguard since they recently upgraded their OFX server.  The following settings have been confirmed to work with the new system.  Vanguard OFX is case sensitive, so be sure to match fiorg, url, and brokerid exactly.  The ofxVer and appVer values are optional, since the defaults still work, but I included them to show that Vanguard supports more recent versions. 

<site>
    SiteName   : VANGUARD   #whatever name you use for the site
    AcctType   : INVSTMT    #investment
    fiorg      : Vanguard
    fid        : 1358
    url        : https://vesnc.vanguard.com/us/OfxDirectConnectServlet
    brokerid   : vanguard.com
    ofxVer     : 103
    appver     : 2300
</site>

54 comments:

  1. Hi Robert, I started using your scripts today. I am having trouble figuring out how to deal with authentication that requires a username, password and pin. Also, authentication that looks for a username, password and security question answer. Any thoughts. Thanks!

    ReplyDelete
  2. Contact your financial institution, and ask them what username and password you should use for Quicken direct connect, and whether they need to do anything on their end to enable access. The OFX protocol uses two part authentication (username and password).

    ReplyDelete
  3. Does anybody have any idea as to what happened with DiscoverBank (NOT DiscoverCard)? I am using fiorg of "Discover Bank", fid of "12610", url of "https://ofx.discovercard.com", and bankid of "031100649". It used to work flawlessly, if slowly, but for the last few days I have been getting back "I'm sorry, the system is unavailable right now, please try again later."

    Anybody have any thoughts or knowledge?

    Harold

    ReplyDelete
  4. The DiscoverBank site started working again, so I guess it was a local problem on their side...

    Harold

    ReplyDelete
  5. Good afternoon. It looks like Capital One Bank went through some sort of upgrade to their online banking system this weekend. I have not been able to get my moneysense scripts to work for them since, and suspect thwy have changed some of the OFX settings. I've looked in the most obvious (well obvious to me!) places and can't find anything. Anyone else hitting thes issues with Cap1 Bank?

    ReplyDelete
  6. I think I messed up the latest revision Sep 2014.Now I show some double files like rlibl1.pyc or control2.pyc. I also can't find the latest ver. listed, in sites.dat it shows the latest revision as Aug. 2012.
    Can I delete the old files except the ones that hold my data and download the zip file again?

    ReplyDelete
    Replies
    1. Yes. I recommend installing to a new folder, and copying your sites.dat and ofx_config.cfg files to the new script folder.

      Delete
  7. Hi Robert,

    I am currently using this to get the price of a few stocks - I keep forgetting to run this though. I was thinking about amending the Getdata.py script so that it does:
    1) Appends each day's results to the same OFX (so doesn't create a new one every week).
    2) Not auto-load the OFX on running - I think this could be done by removing the runFile functions?

    I would then load the OFX file once a week/manually and then delete it after this - ideally, the next time I run the getdata.py script, a check would happen to check for an existing OFX file - if one exists, data would be appended; if not, a new OFX file would be created.

    I'd be then able to cron the Getdata.py script daily...

    Any ideas/comments would be appreciated.

    D.

    ReplyDelete
    Replies
    1. That approach wouldn't work. OFX messages can't be "appended" to previous messages. Even if multiple quote statements were properly bundled into a single OFX message, I'm pretty sure that Money will reject it, since it only allows one "statement" per account per file. It would be pretty easy to keep ofx files (not delete), and to auto-send them all using a separate script, but it would mean "accepting" each in Money.

      That said, I'm not sure I understand the reason for doing it. The purpose of quotes in Money is to update the values for "today". It really doesn't matter what they were "yesterday", or I should say it doesn't matter to me :)

      Delete
  8. Hi there, I use sharebuilder for investments and they require a personal access code for quicken / mint is there a way to enter this in somewhere, because when reviewing the OFX file in notepad++ it has an error mentioning that it needs this code, i have created the code but don't know where to enter it

    ReplyDelete
    Replies
    1. Not sure. The OFX sign-on message set only supports a username and password. I wonder if the PIN is appended (or pre-pended) to the password? It's also possible that Quicken/mint aren't using "direct connect" (i.e., not using OFX).

      Delete
  9. Hi Robert, I've been using your scripts for awhile now. Everything works great, but very recently something strange started happening with my Discover Credit card. Right now for every valid transaction it downloads, I get another bogus transaction with $0.00 amount. It used to not do this, but it started recently. Any way this can be addressed so that Money doesn't pick up these bogus transactions?

    ReplyDelete
    Replies
    1. Sorry I missed this comment before. I use Discover myself, and some transactions from them do have a null secondary transaction where they transmit extra data. It doesn't happen with every one, however. I just accept them, as they don't change anything. I considered filtering out transactions where the dollar amount is $0.00, but I never did.

      Delete
    2. Thanks for your reply. It could be because all my recent transactions have been from the same vendor, it could be vendor specific?

      Delete
    3. It is vendor specific. Some will transmit extra data in a "null value" transaction, immediately following the main one.

      Delete
  10. For banks that are discontinuing MsMoney downloads, is there something out there that allows us to take a quicken download and transform it into MS Money format so that we can continue using MS Money / Pocketsense combo?

    ReplyDelete
    Replies
    1. Yes. The script package downloads data as Quicken QFX, the same as it does for Money OFX. It is an option in the sites.dat file for each site definition. See the APPID and APPVER tags. An APPID of QWIN tells the server that a Quicken client is connected, and it will respond appropriately. The resulting statement should translate into Money without issue.

      Delete
  11. This maybe out of scope, but I figured I would ask. I have recently encountered issues with Ally Bank. I have to manually download statements (no different than before), but they are discontinuing MS Money downloads. The only real difference I see is this and MS Money doesn't like it 0. It is the last item in each statement transaction. Money thinks the file is corrupt. If I take the statement out, it works just fine. Any hints on how to automate deleting this would be appreciated. Thanks

    ReplyDelete
    Replies
    1. Apparently this doesn't like me posting the real line from the OFX file.
      The only real difference I see is this: CHECKNUM 0 CHECKNUM MS Money doesn't like it.

      Delete
    2. I ran into the same issue with Ally. I've written a quick and dirty python script to help me with this issue and automate the process. The script works like this:

      1) I manually download .qfx files to the desktop from the Ally website
      2) I run my script which lives in the same place where the downloaded files are (the desktop) which does everything else:
      3) the script removes all checknum tags which money doesn't like in from all the .qfx files
      4) the script renames .qfx files to .ofx
      5) the script opens these ofx files with MsMoney
      6) the script asks whether i should remove the downloaded files from the desktop
      7) The end.

      If you want the script I can post it somewhere? It's really basic, i wrote it in 10 minutes but so far it gets the job done. Unclear if I will encounter other issues going forward with Ally/Money combo. Ally is the only institution that I use which doesn't support direct OFX connection and for which I cannot use pocketsense. So I have to improvise.

      Delete
    3. I would be interested in the script. I'm having the same issue with the new ALLY qfx statements.

      Delete
    4. Here is the script. It's not optimal, but it works for me. Just paste this into a file with .py extension (i named it qfxmoney.py). Python is space sensitive so the numbers at the begining of each line specify how many spaces the line needs to start with. The numbers need to be removed and replaced with the actual spaces. So number 12 means press the spacebar 12 times before the rest of the text starts.
      *******

      import os, subprocess, sys, re

      if __name__=="__main__":

      4 for filename in os.listdir(os.path.dirname(os.path.abspath(__file__))):
      8 base_file, ext = os.path.splitext(filename)
      8 if ext == ".qfx":
      12 f = open(filename, 'r')
      12 filedata = f.read()
      12 f.close()
      12 newdata = re.sub("[0-9]+", "",filedata)
      12 f = open(filename, 'w')
      12 f.write(newdata)
      12 f.close()
      12 os.rename(filename, base_file + ".ofx")

      4 for filename in os.listdir(os.path.dirname(os.path.abspath(__file__))):
      8 base_file, ext = os.path.splitext(filename)
      8 if ext == ".ofx":
      12 os.startfile(filename)

      4 response = raw_input("Delete qfx/ofx files [Y/N]: ")
      4 if response in ['Y', 'y']:
      8 for filename in os.listdir(os.path.dirname(os.path.abspath(__file__))):
      12 base_file, ext = os.path.splitext(filename)
      12 if ext == ".ofx":
      16 os.remove(filename)

      Delete
    5. sorry this is not 100% right because the script contains characters which html interprets certain way so the website doesn't display it properly. The newdata = line needs to be

      newdata = re.sub("#CHECKNUM@|[0-9]+#/CHECKNUM@", "",filedata).

      # needs to be replaced with < and @ needs to be replaced with >. Again one could not type < and > because the website interpets these as HTML tags within the context.

      Delete
    6. thanks for posting this workaround for Ally downloads. I created the script (including edits recommended by anonymous2 ), ran it but now have a couple of issues. The script successfully edits and converts the data file to ofx, but it doesn't automatically open it. Instead, the Import Handler found an older Discover download. I then moved that file to a different folder, but Import Handler still only wanted to open that file and not the converted Ally file. I know it successfully converted the file because then I successfully used Money's import command. Does anyone have any thoughts on other script changes that might be needed?

      Delete
    7. Seems to me that, given Robert's posting on 12/18/14 (regarding QFX files), Pocketsense should be able to download and open Ally data, if it can find where to go. But I couldn't find Ally's Sites info at either OFX Home Directory or Moneydance data dictionary. Does anyone know if it exists? Is it possible that it goes under a different bank's name?

      Delete
    8. For those who were having issues with Ally, their update yesterday has fixed the issue. The CHECKNUM statement no longer appears as the last statement in the transaction.

      Delete
  12. Hi, Robert!

    First – a repeated thank you for doing the Pocketsense subsystem. I use it every day, and it is great.

    Now a setup question: My wife and I both work the same employer, and we both have 410K plans with Fidelity. The way that Fidelity works, the account number is derived from the company name. So both my wife and I have the same Fidelity Netbenefits account number.

    So my first problem was that Pocketsense wouldn’t let me add two different setup entries for the same “bank” with the same account number.

    So I created a second entry in sites.dat for Fidelity Netbenefits, giving it a different name but all the rest of the same information.

    What has me stymied now is that Money automatically links both of these account downloads to the same Money account (the one I told it to use for the first download). How can I get it to know that they are going to two separate Money accounts?

    Harold

    ReplyDelete
    Replies
    1. If there’s no way this can easily be done, I have a passing acquaintance with Python, and if you can tell me what criteria Money uses from the downloaded file to match to a Money account, I can put in customized code somewhere to bastardize the file I get back from Fidelity to make it unique. For example, I can put in code to check the filename I am processing, and if it’s my wife’s Fidelity file, I can change the account number.

      If you think this approach makes sense, I’d greatly appreciate it if you could point me to where in the Pocketsense code I should look to insert this code and what it might look like. And how to find the Windows filename of the file that is being processed at that point. That would give me a great headstart!

      Harold

      Delete
    2. I looked at your scrubber.py module. I do see where I can pick up the filename the module is working with (which is the only way I can see to determine if this is my wife's or my Fidelity account). But my knowledge of Python fails miserably when it comes to changing the account number, so if you have a thought on that, I'd greatly appreciate it.

      Harold

      Delete
  13. I found this same issue reported for a Quicken user.

    "This is a known issue with Fidelity. It comes about because Fidelity uses account number AND customer ID to uniquely identify an account, whereas Quicken uses only account number. It is possible that you have the same account number for both accounts (such as two family members with 401k at the same company).

    A while ago, another community member suggested the folllowing WORKAROUND. I apologize that I cannot find the original post to give credit where credit is due. I use this procedure so I know if works.

    On a permanent basis:
    1. One Step Update settings, uncheck the two NetBenefit accounts. You will be updating those one at a time.
    2. In each Fidelity account, change the Account number. For instance, if the account number was 123456, change one to 123456x and the other to 123456y.

    Now, when you want to update those accounts:
    1. In the account that now has account number 123456x, change it back to 123456
    2. Update transactions in THIS ACCOUNT ONLY
    3. Change the account number back to 123456x.
    4. Repeat steps 1 to 3 for account 123456y.


    This is a bit of a pain, but if the accounts have enough activity it is much better than manual entry."

    You could accomplish this using Pocketsense by creating a separate sites.dat file for your Fidelity accounts and using the Test your accounts option without sending to Money. You would then manually edit each OFX file by making the account numbers unique to match those in your Money program.

    Does the customer ID appear anywhere in the OFX file? If so a scrubber subroutine would be easy to add by appending it to the account number.

    ReplyDelete
    Replies
    1. Thank you so much!

      The customer ID does not appear in the OFX results structure. But I figure that I can tell whose account it is by setting up two entries for Fidelty in site.dat, and using one for my data and one for my wife's data. The site name specified in site.dat becomes part of the Windows filename, so I can look at that to figure out whose data it is.

      My problem is that I come from a heritage of procedural computer languages. So I can code enough Python to see if it's the filename I want, but I am out of scope when it comes to checking if there is an ACCTID field that has the value I expect, and if so, changing it to the account number I have told Money to expect for my wife's account.

      Is this something simple to do in Python? It seems like it should take only a few lines of code, but it's certainly not instinctive for me to write them.

      Delete
  14. Borrowing from the January 21st comments above, you could process your wife's account outside of Pocketsense like the user is doing for his Ally account (skipping the qfx to ofx portion) and for the newdata line...

    newdata = re.sub("#ACCTID@|[123456]+#/ACCTID@", "#ACCTID@|[123456x]+#/ACCTID@",filedata)

    where 123456 is the common account number and 123456x is the account number you wish Money to use.
    (Because this blog interprets < and > as HTML tags, # needs to be replaced with < and @ needs to be replaced with >)

    ReplyDelete
    Replies
    1. Thank you so much.... I see how that would work.

      In the meantime, I asked Robert for advice on this, and he was nice enough to come up with what I thought was an elegant solution within Pocketsense. He is truly remarkable!

      I'll wait for Robert to post how he solved this problem when he eventually posts the update, but if anyone else is having the problem, ask!

      Harold

      Delete
  15. The issue with "account numbers being used for multiple accounts by the same provider" has been resolved (I hope). See revision history for a brief description of the update. New version posted. Harold has tested, and it appears to solve the issue.

    ReplyDelete
  16. "To handle the condition, the account number assigned in Setup can now have a unique suffix, separated by a colon (e.g., 123456789:A)"

    Brilliant solution Robert!

    -ameridan

    ReplyDelete
    Replies
    1. Good to hear from you Dan. Hope all is well!

      Delete
  17. Robert,
    I'd like to make two changes to the scripts (which I'll gladly contribute back). First is to get rid of the pesky $0.00 entries that Discover will sometimes download. The other is a fix for T.Rowe Price retirement accounts downloads. They are missing a - sign in the entry for dividends. (I need to read a little into the OFX standard to see why that is the case, but I know that if I add the - sign Money process is it fine.)
    Can you point me to where in the code it would be easiest to implement these changes?
    Thanks
    Carlos

    ReplyDelete
    Replies
    1. Hi Carlos: The place for such things is the scrubber.py module. These aren't terribly difficult to implement, and I considered including the "zero entries" before, but decided against it at the time. The reason was that those 0.00 entries usually contain additional info related to the transaction, and it's easy enough to "accept" them w/o issue. That said, if we add a filter, we'll need an option in sites.dat to enable/disable.

      Re: dividend sign: There's a similar issue for malformed buy/sell transactions from Fidelity. I have a routine to fix that, and I can probably use it as a pattern for the dividend issue. If you can send me a neutered version of a recent (errant) ofx download, I can give it a look to verify. The filter should probably apply to all sources, not just TRP.

      As to why? I mucho dislike accounting lingo, since they apply 'credit' and 'debit' differently by type of transaction. From what I've read, a dividend is "generally" a 'debit' when it increases the account, and a 'credit' when it decreases... exactly backwards from my English translation. Money strongly enforces parity when accepting ofx data, so if the type is 'DIV', then the sign must be negative (debit). That's my understanding of it.

      Delete
  18. Is anyone downloading data from Merrill Lynch Edge? If so, please share your settings. Did you need to contact them to "activate" OFX downloads for your account, and/or obtain separate credentials for the connection? Thanks!

    ReplyDelete
  19. I don't use Merrill Lynch but these are the latest settings for Moneydance to try if you haven't already: { "access_type" = "OFX" "id" = "md:1185" "broker_id" = "www.mldirect.ml.com" "bootstrap_url" = "https://taxcert.mlol.ml.com/eftxweb/access.ofx" "fi_name" = "Merrill Lynch Investments" "fi_org" = "Merrill Lynch & Co., Inc." "fi_id" = "5550" "uses_fi_tag" = "y" "app_id" = "QWIN" "app_ver" = "2300" }

    - Dan (ameridan)

    ReplyDelete
  20. Thanks, I had tried various settings from around the web. I tried those settings, and still get same result, status code 2000: Please attempt your login again. If you continue to have difficulty, please call 1.800.MERRILL (637.7455).
    SiteName : MERRILL LYNCH
    AcctType : INVSTMT #investment
    finame : Merrill Lynch Investments
    fiorg : Merrill Lynch & Co., Inc.
    fid : 5550
    url : https://taxcert.mlol.ml.com/eftxweb/access.ofx
    bankid :
    brokerid : www.mldirect.ml.com
    appid : QWIN
    appver : 2300
    mininterval: 30

    I am able to connect with Mint and Personal Capital, so not sure where the problem is. I've triple-checked my username/password.

    ReplyDelete
    Replies
    1. Angelo: I'm not familiar w/ Merrill connections, but Mint and others don't typically use OFX directly. They'll use an "aggregator" such as Yodlee to get data from sites (even MS Money did this for banks that didn't support ofx). It's possible that you need to have ofx connections enabled ('direct connect' in their lingo), or that the username/pw follows a different format than your web login.

      Delete
  21. Thanks for the info Robert. No luck with Merrill support, they claim Quicken Direct Connect doesn't support the online investing side (Merrill Edge). I verified with bank search on intuit, it doesn't list Direct Connect for "Merrill Lynch & Co.,Inc.", only for "Merrill Lynch Investments". Odd, hard to believe a major site like Merrill (aka BofA) doesn't support Direct Connect.

    ReplyDelete
  22. It is possible to download Money OFX files manually from the Merrill Edge website (I just did this today). So that is something --- but clearly not as nice as Direct Connect working.

    ReplyDelete
  23. I've been using TheFinanceBuff''s Python script to download stock quotes into Money for about 2 years now. As of 4-29-15 the script stopped working. It seems that the Yahoo Finance API has changed such that the script no longer works as before. This has launched me into the effort to convert my quoting routine into PocketSense. I'm wondering if your scripts have encountered similar problems with the Yahoo site? Do you have any insight into what's going on with the Yahoo site?

    ReplyDelete
  24. I am trying to get stock quotes going first so I deleted all site definitions. I setup the sites.dat data file with the pertinent stock/fund data, ran setup and tested the quote downloading and all seems to be working fine. However the quotes that were downloaded today (5-4-15) are being imported by money as dated 5-1-15 such that my data is offset by 3 days. What could be causing this and how do I fix it?

    ReplyDelete
  25. You get Friday's prices until Monday's data is available, which for funds is after 5 pm Eastern or so. For stocks it would be around a half-hour after opening. So there is nothing to fix...

    ReplyDelete
    Replies
    1. Yes indeed, thank you for your insight. One curious thing, however. I ran the script this morning (5-5) after the Markets had opened and saw a mix of quote time stamps. All the indexes and stocks seem to be quoting real time, whereas the funds had yesterday's time stamp (5-4) However, the import into money was dated as yesterday (5-4) and all the prices were dated as 5-4. That leads me to wonder what the script uses to peg the time stamp for the .ofx file?

      Delete
    2. Money only accepts a single date for a "ofx statement import", and only updates quote values from OFX once per "statement date". If an update is attempted again with the same quote date, Money will skip it. This is a limitation of forcing quotes through the OFX interface. Re: the timestamp passed to Money, the "latest" date/time found for the included quotes is used as the statement date/time.

      Delete
  26. PS, I just saw the discussion about banks that discontinued OFX downloads and will be trying soon to update my program accordingly. Thanks!

    ReplyDelete
  27. Is it possible to suppress the "Download transactions? Y/N/I" interactive query in Getdata such that it always downloads without any interaction?

    ReplyDelete
    Replies
    1. The scripts are written to be run interactively. It would be easy to disable the prompt, but silent error handling (logging) would need to be added throughout to deal with problems.

      Delete
  28. robert
    having troble with get data. it runs but half way complete it quits and disappears.
    seem if i shut down and restart my computer it works for awhile.
    any ideas?

    ReplyDelete
    Replies
    1. Sounds like an issue w/ Python. Check the version, and maybe reinstall (be sure to use the ActiveState version 2.7.x).

      Delete