Friday, November 10, 2017

New Version Available: Includes Yahoo Quotes

A new release is available that addresses two recent issues:

Last Updated: 01-Jan-2018

1. Yahoo has disabled their csv quote interface, and their web site has changed significantly, such that neither of the previous quote methods used in the scripts (for Yahoo) were valid.   A new Yahoo Finance quote interface has been added, using their json data service.  Time will tell how it fares, and I may add a backup "site parser", if warranted.  The csv interface was actually much more limited than their web site, but I haven't tested the json api to determine the breadth of market coverage.  

Google Finance is still available, and either Google or Yahoo quotes can be enabled (disabled) in sites.dat using the EnableYahooFinance and EnableGoogleFinance options.  If both are enabled, Yahoo will be tried first and, if it fails, Google will be tried.  There's no harm in having both enabled, except that quotes may be a little slower if you're using a symbol known only to Google Finance, since it will try Yahoo before going to Google.
Note:  The EnableYahooScrape option has been removed, and is no longer used.  It will be ignored if found in sites.dat.
2. A patch to the Discover scrub routine to address how Discover Bank encodes transaction FITID values.  They just can't seem to leave well enough alone :(

Update 06-Dec-2017:  Google Finance changed their site structure a bit, so the scripts were updated to correct the quote issue folks were seeing (I hope).

Update 01-Jan-2018:  Discover Bank reverted their FITID formatting to the previous pattern, so the scripts were updated to match.

- Robert

185 comments:

  1. Hi!

    Firstly - thanks very much for these scripts, I've been using them for ages now and they've worked flawlessly!

    ... until now ;-)

    Yahoo stopped working for stock quote price updates, so I updated to the latest version of the scripts. I see the 'enableYahooScrape' option is now deprecated, but when I run the getdata.py script or test the process with setup.py, I get an error related to this param:

    Test account #: [0] 1
    Test Stock/Fund Pricing Updates (Y/N)? y
    Traceback (most recent call last):
    File "Z:\Dropbox\pocketsensescripts\Setup.py", line 313, in
    test_quotes()
    File "Z:\Dropbox\pocketsensescripts\Setup.py", line 145, in test_quotes
    status, ofxFile1, ofxFile2, htmFile = quotes.getQuotes()
    File "Z:\Dropbox\pocketsensescripts\quotes.py", line 449, in getQuotes
    eYScrape = userdat.enableYahooScrape
    AttributeError: site_cfg instance has no attribute 'enableYahooScrape'

    Z:\Dropbox\pocketsensescripts>

    Any suggestions?

    Thanks!

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

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  3. Oops. I removed the yahooScrape option from the config reader at the very last, and forgot to test. Just updated.

    ReplyDelete
  4. Robert,

    I love your scripts and have been using them forever. Thanks for sharing!

    However, with this update I'm seeing a problem with symbols whose company names contain a comma. For example:

    Amazon.com, Inc.,1125.35,11/10/2017,04:00PM,1129.13,-0.33%

    The CSV parser treats " Inc." as a separate column, and then the date conversion fails. Here's another symbol that has the same problem, but with " Series 1":

    PowerShares QQQ Trust, Series 1,153.68,11/10/2017,04:00PM,153.69,-0.0065%

    My very hacky workaround for this is to modify csvparse in quote.py:

    def csvparse(self, csvtxt):
    quote=[]
    self.status=True
    csvlst = [csvtxt] # csv.reader reads lists the same as files
    reader = csv.reader(csvlst)
    for row in reader: # we only have one row... so read it into a quote list
    quote = row # quote[]= [name, price, quoteTime, pclose, pchange], all as strings

    # LAPADULA: BEGIN HACK
    # Some symbols' names have a comma in them, e.g.:
    # Amazon.com, Inc.,1125.35,11/10/2017,04:00PM,1129.13,-0.33%
    # This extra comma screws up the CSV parser, so delete
    # common items that can follow the comma
    if Debug: print "quote[1]: '" + quote[1] + "'"
    if quote[1] == " Inc." or quote[1] == " Series 1":
    print "Quote before pop: "
    print quote
    quote.pop(1)
    print "Quote after pop:"
    print quote
    # LAPADULA: END HACK
    # (rest of the method is unchanged)

    But I'd imagine you can think of a better fix that isn't so symbol-dependent. Maybe seeing if there's an extra field such that the date is pushed out one column further than expected? Or is there some way to make the Yahoo/Google services return a valid (quoted) CSV string?

    Let me know if you want me to test any fixes you might come up with, but AMZN, QQQ, and SRC are the only ones I've run across.

    Thanks again.

    ReplyDelete
    Replies
    1. Fixed. Grab the latest version... again.

      Delete
    2. Looks great -- thanks!

      One other question. I only use the scripts to download current pricing data, not to handle full accounts.

      In Getdata.py, "pwkey, getquotes, AcctArray = get_cfg()" is returning False for getquotes, so I added a hack (getquotes = True) to force them to download.

      Did I do something wrong in my setup? Is there a better way to fix this?

      Delete
    3. Run Setup and enable quotes via option 6.

      Delete
    4. Oh, duh. That's right in the instructions....

      Thanks again. Everything is working great now.

      And also thanks for putting up a PayPal donation link on https://sites.google.com/site/pocketsense/aboutme-1.

      Delete
  5. I'm a new user and just downloaded and tried to set up Citi and Amex. When I run through Setup and try to setup the account for either of those, I get this error (for Citi):

    ** An ERROR occurred sending POST request to www.accountonline.com
    Exception type :
    Exception val : endheaders() takes exactly 1 argument (2 given)
    An online error occurred while testing the new account.

    I'm running Python 2.7.14. What am I doing wrong?

    ReplyDelete
    Replies
    1. Post your sites.dat settings for Citi.

      Delete

    2. SiteName : CitiCard
      AcctType : CCSTMT #credit card
      fiorg : Citigroup
      fid : 24909
      url : https://www.accountonline.com/cards/svc/CitiOfxManager.do
      bankid :
      brokerid :
      ofxVer :
      appid :
      appver :
      mininterval:
      timeOffset :

      Delete
    3. Those look correct, unless it's a Citi Business Card, then the fid is 26389. Citi works for me.

      Delete
    4. Robert, one thing I just noticed is that even though I set the period for 7 days, the ofx file has 12 months worth of transactions.

      Delete
    5. CH: Next check is to verify that you have the scripts in a folder where you have full read/write/del privilege? Specifically, they're *not* installed in \program files\ or another system folder that prevents full user control?

      Dan: Is that happening w/ all accounts? Mine is working fine. There are two settings that affect download intervals. The global setting of defaultInterval: 7, and an account-specific one named minInterval. If you enable Debug, test the account, and look at the request, is DTSTART correct?

      Delete
    6. Hi Robert - they were in my Documents folder.
      I moved them to C:\pocketsense and retried setting up Citi and it still gave me the error I described.

      Delete
    7. CH: Your Documents folder would be fine. I can't replicate the error, so send me your sites.dat file (pocketsense1 at gmail dot com) and I'll see if something is obvious.

      Delete
    8. Citi Credit Cards worked for me for a long time then it stopped and was giving errors. I downloaded the updated pocketsense a few months ago and it started working again. Then about a month ago it died again. I downloaded the new pocketsense yesterday, but Citi still bombed out. I just downloaded the latest python.org files and now it works. They're obviously constantly tweaking something on their side so you need the latest and greatest for it to work.

      Delete
  6. When i tested Citi it worked properly, and the other accts work properly. Apparently, the bank server goofed, because when I look at the start date, it was 7 days ago, but as you see it downloaded a years worth anyways...

    {BANKTRANLIST}

    {DTSTART}20171105120000

    {DTEND}20171110120000

    {STMTTRN}

    {TRNTYPE}CREDIT

    {DTPOSTED}20160408180000.000

    {TRNAMT}15.31

    {FITID}1663160990001

    {SIC}00000

    {NAME}PAYMENT - THANK YOU...

    ReplyDelete
  7. I just looked at Citicard....ofx in my xfr folder. I have {DTSTART}20171026120000 but have transaction with {DTPOSTED}20151028180000.000 as my oldest. I had never noticed.

    CL

    ReplyDelete
  8. Citi seems to be downloading ALL transactions, and as CL stated they go back even more than a year. Money handles it fine though.

    ReplyDelete
    Replies
    1. I use a Citi issued card, but very little. I looked, and see transactions before the cutoff, but only by a few days. Go figure...

      Delete
  9. Robert,
    Thanks a lot for this new version.
    I'm french and use your scripts to get fund quotes on the french market.
    With this version, an error occurred on several funds like FR0010287094.PA and FR0010177378.PA.
    It's seems to be problem with accent.

    --------------------------------------------------------
    Traceback (most recent call last):
    File "Setup.py", line 313,
    in
    test_quotes()
    File "Setup.py", line 145,
    in test_quotes
    status, ofxFile1, ofxFile2, htmFile = quotes.getQuotes()
    File "quotes.py", line 469,
    in getQuotes
    sec.getQuote()
    File "quotes.py", line 119,
    in getQuote
    quote = self.csvparse(csvtxt)
    File "quotes.py", line 169,
    in csvparse
    for row in reader: # we only have one row... so read it into a quote lis
    t
    UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 17:
    ordinal not in range(128)
    --------------------------------------------------------

    Thanks

    ReplyDelete
  10. Robert,
    When getting a price quote from Google Finance, NYSEARCA is not recognized. Example ETF: NYSEARCA:VNQ gives an error message when running "Getdata.py". Others like nyse:bac and nasdaq:csco work fine. Any thoughts?

    ReplyDelete
    Replies
    1. Interesting. I tested a couple others on NYSEARCA, and they were recognized. If you enable Yahoo quotes and just use the VNQ symbol, without the exchange prefix, Yahoo gets it.

      If you lookup a symbol on Google, but don't get a full quote page with all the details, then the script won't get it either. Same for Yahoo.

      Delete
    2. With Yahoo yes and Google no, vnq gets quote. With Yahoo no and Google yes, vnq gives error. With Yahoo yes and Google yes, vnq gets quote because it is probably using Yahoo. I believe Google is looking for NYSEARCA:VNQ, but not sure. I guess I will be using Yahoo while its working. All testing was done using the 2017-11-10.

      Delete
  11. Unfortunately the new package is not working with Google. Yahoo does not collect as Brazilian stock quotes using the item as below
    {
    #--- stocks ---
    BVMF:ABEV3
    BVMF:BBAS3

    {
    . Until yesterday, Google is working very well, but since today the search returns the following message:
    {
    ** BVMF:BBSE3 : invalid quote response. Skipping...
    ** error reading http://www.google.com/finance?q=BVMF:BCRI11

    }

    How to proceed?
    thankful

    ReplyDelete
    Replies
    1. Looks like they changed their site encoding. I captured some keys a couple days ago, but need to check a couple more times to see if they're predictable. If so, I'll update the script to match.

      Delete
  12. Today when I tried to retrieve my account info from my Citicards, I get this message.

    ** An ERROR occurred sending POST request to www.accountonline.com
    Exception typer :
    Exception val : [Errno 1] _ssl.c:499: error:1407742ESSL routines:SSL23_GET
    _SERVER_HELLO:tlsv1 alert protocol version

    It has worked before and I haven't changed anything. I did download the newest version just now and set it up, but I get the same message every time I run it. Discover has not worked for quite a while, it quit some time ago and I installed your beta version and it worked again, until it stopped again. I just quit using my Discover Card and moved to my Citi MC. This is the error message I get with Discover.

    Invalid OFX statement received



    ReplyDelete
    Replies
    1. Sorry I entered the error message incorrectly just now.

      It should say Exception type : class 'ssl.SSLERROR'

      It should be enclosed by < and > but those don't seem to work.

      Delete
    2. Update to the latest Python version 2.7.x.

      Delete
  13. Thank you thank you thank you a thousand times for enabling Money users to continue getting online quotes. Years of watching portfolios can continue!!!!

    ReplyDelete
  14. Hey Robert, thanks so much for continuing this work! Just applied the latest update and that fixed the bulk of the problems I just noticed today :-$

    What seems to still not be working is Canadian mutual funds:
    ** error reading http://www.google.com/finance?q=MUTF_CA:RBF460

    ** MUTF_CA:RBF460 : invalid quote response. Skipping...
    ** error reading http://www.google.com/finance?q=MUTF_CA:AIM1571

    ** MUTF_CA:AIM1571 : invalid quote response. Skipping...
    ** error reading http://www.google.com/finance?q=MUTF_CA:CIF833

    ** MUTF_CA:CIF833 : invalid quote response. Skipping...
    ** error reading http://www.google.com/finance?q=MUTF_CA:RBF1340

    ** MUTF_CA:RBF1340 : invalid quote response. Skipping...

    ReplyDelete
    Replies
    1. I have the same issue with cdn mutual fund quotes.

      ** error reading http://www.google.com/finance?q=MUTF_CA:AIM1651

      ** MUTF_CA:AIM1651 : invalid quote response. Skipping...

      But I can see the quote via Chrome: https://finance.google.com/finance?q=MUTF_CA:AIM1651

      Delete
  15. I just now posted an update that addresses the Google Finance issue (I hope).

    ReplyDelete
    Replies
    1. Resolved my issue, thanks!

      Delete
    2. Works like a charm here too. Very thanks!

      Delete
    3. That's done it for me, too. Thanks Robert!

      Delete
  16. Well, Discover is at it again. Discover Bank has reverted the FITID to SDF###### with no sequence appended. This is the way it was prior to their last revision. NOTE: This is for bank accounts only. Discover Card is so far unaffected.

    The change is trivial to implement. It is basically reverting one function _scrubDiscover_r1() back to the 8/14/2016 version. Note, it NOT changing the entire file scrubber.py to an older version.

    In file scrubber.py, function _scrubDiscover_r1(). In the if-else statement "if accType=='CCSTMT':", under "else:", change "bx = len(fitid) - 8" to "bx = len(fitid) - 0". That is ZERO, meaning do not strip any trailing characters.

    Unless you have a pressing need to download your account immediately, I would hold off until Robert tests and makes an official release. Discover is undergoing system maintenance this weekend, and may break this yet again.

    ReplyDelete
  17. Hi Robert,

    // Cannot thank you enough as you have saved me tremendously. I've tried many pkgs available from GnuCash thru Qkn, but nothing like an old friend (MSMoney);
    // A recent couple changes to OFX server locations has me slightly stumped; the last item for this challenge is receiving a "No (null) statement received ...." --any ideas (or anyone for that matter?

    Enormous thanks for your time & sharing this information.
    Jeff

    ReplyDelete
  18. I am using version 2.7.1.3 as I always have. Nothing changed until all of a sudden noting worked;

    ReplyDelete
    Replies
    1. I use version 2.7.12, which seems to be new enough, but 2.7.14 is the newest.

      Your version was released 2012-04-09.

      CL

      Delete
  19. I've been using 2.7.13 and have not changed nor had any issues in over a year. You may want to check if your bank had changed its' OFX servers; in my case the bank did; I went as far as using someone's working Quicken log file to see what the banks' new OFX parameters are (a friend, we have the same bank, though he still uses Quicken).

    Unfortunately from my bank (BASTMT) I only receive a "No (null) statement received" msg & the 0k .ofx file doesn't give me any clues.

    Go to OFX Home directory or MoneyDance's data dictionary and compare to your sites.dat file to see if there's any changes.

    Or better yet, run GetData.py in interactive mode with the i switch to see what is actually all-of-a-sudden happening or not, then post here.

    Perhaps someone can chime in.

    JM

    ReplyDelete
  20. It's definitely important to update to a recent python 2.7.xx version. There's no reason not to move to 2.7.13 or 14. What's happening, is that servers are updating their software, with the latest updates for ssl connections. Older python libraries don't include the updates, so they will fail to connect. The server error will usually be something nondescript or null, to give no clue what happened.

    ReplyDelete
  21. Hi Robert,
    Thanks for that information, it is very helpful. Though my post contains not-so-good information, I'm not able to find anything later than 2.7.13.2716 on ActiveState which is what I'm running. Unless perhaps another site is hosting 2.7.14, I'm not sure what to troubleshoot @ this point for the null results.
    I'll keep checking.
    Much Thanks! JM

    ReplyDelete
  22. I did try 2.7.14 from Python.org (previously followed your instructs explicitly as to ActiveState; sorry, exNavy); however receive the same results of NUL on statement from one bank, though other 3 banks are fine;
    JM

    ReplyDelete
    Replies
    1. I've been on travel, so may have missed a detail. What bank is causing the issue? Perhaps someone has experience that can help.

      Delete
  23. Centric Bank (in Pa); they've recently changed OFX urls (prev directline2.netteller.com now www.netteller.com/centricbank);
    additionally I've updated FI_id; and also tried old and new values of appver (2400 to 2600 -Quickn 2018) and blanked out brokerid however receive same "No (null) statement received" msg & the 0k .ofx file (from a friends Qkn log file @ the same bank).
    Though the Python.org was a smaller install (130m less), I followed explicitly (ActiveState) as not knowing Python and how libraries are affected in Win10 (but perhaps I'll give it a whirl soon);
    JM

    ReplyDelete
  24. Hi All,

    Windows 7 Home Premium 32-bit
    ActivePython 2.7.13 build 2713
    Scripts Ver: 06-Dec-2017

    Is anyone else having a problem with connecting to Regions Bank?

    I'm getting the following error when testing with Setup.py.

    ** An ERROR occurred retrieving POST response from onlinebanking.regions.com
    Exception type : < class 'ssl.SSLError' >
    Exception val : ('The read operation timed out',)
    An online error occurred while testing the new account.

    Sites.dat setting:
    < site >
    SiteName : Regions Bank
    AcctType : BASTMT
    fiorg : Identity-RFG
    fid : 13131
    url : https://onlinebanking.regions.com/Ofx/process.ofx
    bankid : 261170740
    ofxVer :
    appid :
    appver :
    mininterval:
    timeOffset :
    < /site >

    The connection was working up until this morning.

    -Kevin N.

    ReplyDelete
    Replies
    1. Spotted this on the Quicken Boards...
      https://getsatisfaction.com/quickencommunity/topics/direct-connection-to-regions-bank-quit-working

      It looks to be a Regions Bank problem.

      -Kevin N.

      Delete
  25. I have used Pocket Sense for many years now with no problems other than Discover. A month or so ago it stopped working, giving some long explanation. So today I had time so I started over, I uninstalled Python, reinstalled it (2.7.13.2716) 64 bit.Then I downloaded latest PS version and set it up the same way I always have. But nothing runs, all I get for all three of my credit cards is "Invalid OXF Received" and to check something that means nothing to me. I only use two Citi credit cards and Discover. I am stumped.

    ReplyDelete
    Replies
    1. I'll be out of pocket for a few days, but wanted to make a couple suggestions. First, I recently had a case where a new user installed a new version of Python, but later realized that there was an older version installed too (w/ the older copy being the "active" version). So, first verify that Python 2.7.13 is active by opening a command prompt and running python (type "python "), verify the version. Next, when you say "setup the same way", did you start from scratch? The normal way to "upgrade" is to extract the new PS files into your existing folder, overwriting the old versions. It won't touch your settings, sites.dat, etc. Both Citi and Discover should work fine, so something's not quite right.

      Delete
    2. Thanks for the quick reply. I uninstalled Python using Revo Pro, then installed newer version. Then I deleted PS folder from my Documents. I downloaded newest version and started from scratch. Only thing I did different is changed the ask for time interval to yes. However it must have been user error, because finally I deleted new PS folder and restored my deleted one. And that one worked. So it must have been the python all along. And the PS I deleted and restored is also newest version, I had worked on this before, but never thought of python until you mentioned it in an earlier post to me. Discover of course never works for me anymore.

      Thanks so much for this program, it is literally the best thing on my computer. I found it when they discontinued Money and have used it ever since. And I can always count on you to fix it when the banks etc mess sit up.

      Delete
  26. Discover works fine, but you have to use the account ID they assign you rather than the credit card number.

    ReplyDelete
    Replies
    1. See my article on the subject: https://microsoftmoneyoffline.wordpress.com/2017/05/12/discover-has-changed-your-account-id/

      Delete
    2. Thank you for this. I had no idea.

      Delete
  27. From what I can see online, I assume that Capital One is no longer usable via Pocketsense? They seem to have disconnected their OFX server completely... if I ping ofx.capitalone.com I am unable to get to it.

    Harold

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

    ReplyDelete
  29. I am hitting the same problem. Until yesterday I was able to use Pocketsense script to connect to Capital One Bank (Credit card has never worked) and download. Even a manual download from the web-site seems impossible as the file produced can only be a CSV, QFX or QBO type.

    ReplyDelete
  30. CaptialOne360 still works. It used to be ING Direct. I don't know if this is relevant or helpful. I was wondering if they might have consolodated the bank OFX operation???

    I do get a scrubber message " +Scrubber: Fixing missing {DTEND} field".

    Anyway, here is the site descriptor that I use, with the curly braces used instead of pointy brackets:

    {site}
    SiteName : CAPITALONE360
    AcctType : BASTMT
    fiorg : ING DIRECT
    fid : 31176110
    url : https://ofx.capitalone360.com/OFX/ofx.html
    bankid : 031176110
    brokerid :
    appid :
    appver :
    mininterval: 180
    timeOffset :
    {/site}

    CL

    ReplyDelete
    Replies
    1. I tried those settings for Capital One and got "Customer needs an Access Code from Capital One 360 (along with your Customer Number or Saver ID (Username)) in order to pull Capital One 360 info into this tool." in my OFX reply message. It seems like they simply pulled support for automated OFX for regular Capital One accounts. Utterly clueless, IMO.

      Delete
  31. Well, it's month-end, and DiscoverBank (NOT DiscoverCard) seems to be broken. No matter what I do, it seems to not want to automatically match the month-end interest transaction. I tried making the change that Andrew suggested above (12/6/17), and it didn't help. Either way (with the scrubber.py dated 11/7/17 or with that one and Andrew's change), Money keeps on thinking the interest transaction is new. Repeatedly.

    Interestingly, when I ran my normal stream of all my accounts tonight, Money entirely skipped updating three of my five DiscoverBank accounts. That is, it showed the balance DiscoverBank sent, but it simply didn't include the 12/29/17 interest transaction at all. When I then ran those three accounts using Pocketsense's Setup program's test function, I got the 12/29/17 interest transactions, but then when I run them again using Setup/Test, I keep on getting them and the 11/30/17 interest transaction again (Setup uses a fixed 30 day interval, I think, which is why I am getting the 11/29 transactions again).

    When I subsequently run Setup/Test on one of the two accounts that originally updated correctly, I do NOT get a duplicate 12/29/17 interest transaction, but I get a duplicate of the 11/30/17 interest transaction.

    Hopefully, somebody can make some sense of what I am getting. I have never followed what the scrubber routine was doing, but if it helps, when running with Robert's 11/7 version of the scrubber, I seem to be getting an FITID value of S1, S2, S3, etc. in the files going to Money. When running with Andrew's modification, I get SF7087123, SF7087122, SF7087121, etc. in the files going to Money.

    As a final test, I tried one account with Robert's 11/7 scrubber, and first deleted both the 11/30/17 and 12/29/17 interest transactions in Money, and then downloaded using Pocketsense Setup/Test twice. Both times, the FITID was S6 (for 11/30) and S7 (for 12/29), but Money did not match the transactions the second time.

    I'll try again tomorrow... in my previous experience, waiting a day with DiscoverBank sometimes changes things.

    ReplyDelete
    Replies
    1. 12/31/16 -- I did just my usual download (asking DiscoverBank for 15 days of data). Four out of five downloads worked correctly (even though the 12/29/17 interest payments were in the download files, the transaction was matched to what downloaded yesterday and was therefore ignored). On one account (the one I was using to play around with yesterday), it did not match the 12/19 interest transaction and asked me to add it.

      If I just clicked on that file and fed it to Money again, it did not ask me to add the interest transaction again. I did not try downloading via Setup/test to see what happens.

      The FID values were S0, S1, S2, S3, and S4. S2 was the one that "failed".

      Delete
  32. Regarding Discover Bank, I haven't yet looked at the most recent changes. My intent has been to revert the scrub routine as mentioned above, but was waiting to see if Discover actually stuck with it. There's one part that will be the same, regardless. Any transaction downloaded using the current version will have to either be deleted in Money, *or* the download interval will need to be changed to only download transactions since the change. The two versions for Discover Bank (and the next) will not produce the same FITID values for transactions, and therefore cannot overlap w/ regard to download intervals.

    ReplyDelete
    Replies
    1. 1/1/18 -- I did my "routine" download, and it worked perfectly this morning. The interest transactions for 12/29/17 were in the downloaded files, but Money didn't mention them when they were imported.

      Delete
    2. Oh. And the FITID values on 1/1/18 were S0, S1, S2, S3, and S4.

      Delete
    3. I was confused by the S0, S1, etc., but see now what's happening. The recommended change that Andrew made above isn't the same as the original Discover Bank scrub routine that I used (both work, but are different). I'm going to revert to the same as before for consistency, but that will mean that you'll need to delete recent downloads and grab them again, or set the download interval such that it doesn't overlap transactions between the two versions. Stay tuned...

      Delete
    4. Now that I've actually made the change, the above comment isn't exactly right. Andrews' edit gives a slightly different script, but produces the same result. So... something in your personal edit that's different to produce FITID values of S0, S1, S2, etc (?).

      Delete
  33. A new version of the scripts have been posted to address the recent change at Discover Bank. Please note that any downloads that overlap with transactions from the previous version may not match in Money, since they may have different FITID values. The correct way to handle this will either be to delete the original transaction(s) in Money and accept the new, or set the download interval such that there is no overlap.

    ReplyDelete
  34. Robert,
    When listing multiple Chase accounts I'm setting up, I see one common ConnectKey for all of the accounts. So naturally I get errors on the newest additions. Did you ever add logic for ofx 102 accounts to get unique codes, or do I have to add the siteclientUID logic that Dave Reiser had previously suggested?

    ReplyDelete
    Replies
    1. I guess it was John that came up with the siteclientUID logic, not Dave. Anyways, I see nothing in the site.dat template to suggest how to handle unique ConnectKeys.

      Delete
    2. With Chase, the account numbers differ, but the username and passwords are the same.

      Delete
    3. I meant ofx 103 by the way.

      Delete
    4. Hi NxtTek,
      Per sites.dat:
      # 28Aug2012*rlc: -Added support for OFX version 103 (for sites that require ClientUID)
      # Default Version = 102

      Here is my sites.dat setting for Chase Visa:
      < site >
      SiteName : Chase VISA
      AcctType : CCSTMT
      fiorg : B1
      fid : 10898
      url : https://ofx.chase.com
      bankid :
      brokerid :
      ofxVer : 103
      appid :
      appver :
      mininterval:
      timeOffset :
      < /site >

      (spaces deliberately added before and after < > signs)

      You'll likely be notified by Chase to verify your identity via email after attempting to connect.
      You also have to log onto the Chase website and allow access for PFM programs. The option to do so is rather hidden. If you need a hand finding it, post back and I'll try to walk you through it.

      -Kevin N.

      Delete
    5. Thanks for the reply Kevin. I think I worked with you back when they switched. My Chase downloads work, but only for the first 3 accounts. The last 2 don't, and as I said, when I list the accounts, the same ConnectKey shows up for all of the accounts. I'm assuming that Pocketsense should have come up with unique codes when the ofx=103 was encountered.

      Delete
    6. Keven, Here is the article I wrote:

      https://microsoftmoneyoffline.wordpress.com/2016/04/26/resolving-errors-for-those-having-multiple-accounts-at-chase-and-other-ofx-103-banks/#more-1474

      that you verified. I guess my question is, did Robert implement this into Pocketsense (or something similar, or do I have to implement this on my own to accommodate multiple accounts?

      Delete
    7. Hi NxtTek,

      I'm pretty sure that Robert *did* make the necessary changes.

      If I recall correctly, I used to have to have a 'His' and 'Hers' < site > entry for mine and the wife's Chase cards. One used a ClientUID as part of the < site > entry and one used a global ClientUID listed at the bottom of sites.dat (which doesn't seem to be there anymore)

      We only have two accounts in total, each have separate acct #'s and log ons.

      Are you using the latest build of the scripts? I see a new one is available. 01-Jan-2018.

      Are you using Python 2.7xx?

      -Kevin N.

      Delete
    8. Yes to all. I tried Resetting the connection for the last 2 accounts, yet when I list them afterwards, they still all show the same ConnectKey. I hope Robert will pop in.

      Delete
    9. Robert, is this the expected result using ofxver = 103?

      Site Account Type UserName
      ----------------------------------------------------------------------
      1. CITICARD xxxxxxxxxxxxxx CREDIT CARD xxxxxxxxxxxx
      ConnectKey: 77e38627-719c-4a95-a059-52b67752e1ea
      2. CHASE xxxxxxxxxxxxxx BASTMT xxxxxxxxxxxxxx
      ConnectKey: 4bb68a56-03f0-4a0d-aebe-5701df2effc4
      3. CHASE xxxxxxxxxxxxxx BASTMT xxxxxxxxxxxxxx
      ConnectKey: 4bb68a56-03f0-4a0d-aebe-5701df2effc4
      4. CHASE xxxxxxxxxxxxxx BASTMT xxxxxxxxxxxxxx
      ConnectKey: 4bb68a56-03f0-4a0d-aebe-5701df2effc4
      5. CHASE xxxxxxxxxxxxxx BASTMT xxxxxxxxxxxxxx
      ConnectKey: 4bb68a56-03f0-4a0d-aebe-5701df2effc4
      6. CHASE xxxxxxxxxxxxxx BASTMT xxxxxxxxxxxxxx
      ConnectKey: 4bb68a56-03f0-4a0d-aebe-5701df2effc4

      Delete
    10. Hi Dan: The ConnectKey is unique for each Site and UserName combo, and will use an existing key if it already exists for another account. Do all accounts use the same username and Site code? More importantly, what is different about the three that work, vs the two that don't?

      Delete
  35. the first 3 are in the name of a family trust checking, a joint checking and a joint savings. The last 2 are also joint savings accounts. I wish it would trigger off account numbers instead. The client has the same web logon to access all 5 accounts.

    ReplyDelete
    Replies
    1. The connection key (ClientUID) should be unique "per user". It serves as a multi-factor authentication token in addition to username and password.

      If you want to test it w/ a different key, you can edit the connect.key file. The script reuses a key for a given user@site.url basis, but doesn't check or update the key once saved. username@site is encrypted, but not the keys. Use Setup->View Accounts to verify a change. Perhaps change the key for one account only, to see if it's really the issue?

      Delete
  36. Hi, Robert...

    Thanks for dealing with Discover Bank. Naturally, once I got over the month-end changes, it has been working flawlessly every since :)

    As to the S0 etc., I don't have any local edits in the scrubber.py module, and am running the one dated 11/7/17.

    Notwithstanding all of that, I would like to upgrade to your latest scrubber. But I have made changes to ofx.py a while ago which, if possible, I'd like to keep without putting them into your newer version. So do you think I can just download your newest set of modules and just copy scrubber.py over and use it? (If not, I'll just bite the bullet and redo my ofx.py changes)

    ReplyDelete
    Replies
    1. Should work fine, but I'm curious what you needed in your ofx routine that was different?

      Delete
    2. First: The new scrubber routines worked perfectly for Discover Bank. The FITID values were SDF4782190 through SDF4782194. Thank you very much!!!

      Second: My change was to the ofx.py DoQuery routine to allow for five retries when getting a bad HTTP response code from the server, waiting a second between retries. This was to handle occasional hiccups on the Chase and DiscoverBank servers. Nowadays, they don't hiccup often, but it still happens. I originally had it set to 30 retries, but the DiscoverBank servers sometimes give a bad HTTP response code when they are not in service, and since I have 5 DiscoverBank accounts, it ended up taking too long... five seems to catch all temporary hiccups. If you would dream of including it or something like it, I can send you my code (which is quite simple and probably reflects my almost-complete lack of knowledge of Python :) ).

      Delete
  37. So here's a weird but extremely good thing that has happened to my system that I wanted to report. I have about fifteen years of data on Money, but Money runs reasonably quickly. One of the things that took a relatively long time was the importing of downloaded data. BUT, sometime is December, it just started running MUCH more quickly!!!

    I did not upgrade my hardware, so I can think of four possibilities that caused it to happen.

    One: Robert's new scrubber routines somehow made Money happier.
    Two: I got an update for Money's underlying database system from Windows Update.
    Three: The data files structure used might have self-optimized itself in some way.
    Four: Magic [wink]

    I wanted to thank Robert if it was the scrubber, and was curious if anybody else was seeing this.

    ReplyDelete
  38. In terms of Money's matching of downloaded transactions, I wanted to report a longstanding situation. With my Chase checking account, it happens that someone does not deposit a check in a reasonable time period. Accordingly, I have set my Money to match transactions for a 360 day period. But when Money matches a downloaded transaction to a manually entered check that is old (I have never determined exactly how old is "old"), then Money keeps on presenting that match for my Accept click (with each download) until I no longer get that transaction on my download.

    This happens periodically, and is really not a bother. But I am mentioning it in case it ties with something you are thinking of doing somewhere.

    ReplyDelete
  39. Harrold said ". But when Money matches a downloaded transaction to a manually entered check that is old (I have never determined exactly how old is "old"), then Money keeps on presenting that match for my Accept click (with each download) until I no longer get that transaction on my download."

    Here is a work-around when you want a transaction to not bother you again: accept the transaction as a new transaction. Then in the register, mark that transaction as Void. If you like, for neatness, optionally delete the Void transactions once they are old enough.

    CL

    ReplyDelete
    Replies
    1. I'll have to try that! So I would end up with two transactions for the same check number, with one marked void? Interesting....

      Thanks!

      Delete
  40. That's actually quite common. Some Payment for Invoice, Xfr and some Payroll transactions don't match up with the downloads and therefore one of the repeats has to be voided and can later be deleted after it is outside the download date range.

    By the way, all of my Chase accounts work now. Apparently, the ofx servers aren't updated with account permissions as quickly as the others.

    ReplyDelete
    Replies
    1. NxtTek, when you say "all of my Chase accounts work now", are those all credit cards, or are you saying Chase Checking works with PocketSense?

      I know that Citi cards works nicely, but you have to go to the website for Citi OFX.

      CL

      Delete
    2. I meant to say
      I know that Citi cards works nicely with PocketSense, but you have to go to the website for Citi checking OFX.

      CL

      Delete
    3. they are various checking and savings accounts. I thought the connectkey (clientUID) had to be different for multiple accts, but I see now that they differ by userid instead.

      Delete
  41. Dear Sir,
    I have used your wonderful code for a number of years with a sunset edition. I just tried your newest version and get a new message saying the import file is invalid or corrupt for the stock tickers. Do you have any insights on what I might have done wrong?

    Thank you again.

    George

    ReplyDelete
  42. I did a quick check of the Moneydance settings, and see a couple entries. The second one looks interesting.

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


    "id" = "md:2578"
    "uses_fi_tag" = "y"
    "access_type" = "OFX"
    "fi_name" = "Delta Community CU QFX-QBO"
    "bootstrap_url" = "https://ofxdi.diginsite.com/cmr/cmr.ofx"
    "fi_id" = "261071315"
    "app_id" = "QWIN"
    "fi_org" = "DI"
    "app_ver" = "2400"

    ReplyDelete
  43. Using PocketSense for years. Today had problem with USAA Credit Card account, but no problem with USAA Banking accounts. In the .ofx file I see: "We're sorry USAA could not find the account requested." The account number has not changed, and the account is accessible via the WEB UI. Thoughts as to how to troubleshoot this would be appreciated.

    ReplyDelete
  44. Hmmm. Must have been a transient problem with USAA, as the download worked normally today, with no changes made at this end.

    ReplyDelete
  45. Hi, my quotes lookup seems to have broken "invalid quote response", I was using the google urls (not Yahoo), wondering if they have changed something, is it just me seeing this?

    ReplyDelete
    Replies
    1. HereHere I am having the same kind of problem! It started a few days ago, and I have not gotten better since then!

      Delete
    2. I'm seeing the issue as well. Started earlier this week.

      Delete
    3. I haven't had a chance to really focus on it, but it appears that Google no longer provides a json quote packet in the response. Not sure if it's available elsewhere, but will check around. Yahoo quotes are functioning.

      Delete
    4. Hello Robert!
      Thank you for the wonderful work you are doing!
      However, my Yahoo is not working either! Now I have no option to take stock quotes from the Brazilian stock exchange.
      I use the following setting:

      #--------------------------------------------------------------------------------
      # Stock quote options
      #--------------------------------------------------------------------------------

      EnableYahooFinance: Yes # Enable quote lookup on Yahoo Finance
      EnableGoogleFinance: Yes # Enable quote lookup on Google Finance
      YahooTimeZone: -5:EST # Timezone rule for the Yahoo quote server (default = -5:EST).
      ShowQuoteHTM: Yes # Always show quotes.htm from Getdata
      AskQuoteHTM: Yes # Ask to show quotes.htm from Getdata (overrides ShowQuoteHTM)
      ForceQuotes: No # Force Money to record a transaction when importing quotes*

      YahooURL: http://query1.finance.yahoo.com/v7/finance/quote
      #GoogleURL: https://finance.google.com/finance
      QuoteCurrency: BRL # Currency for quotes. Default = USD
      #QuoteAccount: 0123456789USD # Custom account number for Quotes. Default = 0123456789
      # Account number can contain alpha-numeric (e.g., 123456789USD is valid)


      And the answer I've got is:

      ** BVMF:IRDM11 : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=BVMF:BCIA11

      ** BVMF:BCIA11 : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=BVMF:SMSL3

      ** BVMF:SMSL3 : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=BVMF:MPLU3

      ** BVMF:MPLU3 : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=BVMF:BRFS3

      ** BVMF:BRFS3 : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=FRA:QDVB

      ** FRA:QDVB : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=FRA:ZPRS

      ** FRA:ZPRS : invalid quote response. Skipping...
      ** error reading http://finance.google.com/finance?q=AMS:IWDA

      ** AMS:IWDA : invalid quote response. Skipping...
      Y: USDBRL=X 3.3031 3/23/2018 09:24AM -0.11%
      Y: EURBRL=X 4.083447 3/23/2018 09:24AM 0.3%
      Y: CHFBRL=X 3.4879255 3/23/2018 09:24AM 0.066%

      Any help is welcome!
      Thank you!

      Delete
    5. Those are "google specific" ticker symbols. See if you can find their equivalent on Yahoo. For example, the first one (BVMF:IRDM11) is IRDM11.SA on Yahoo. If you need or want to match updates in Money using the Google symbol, you can set a matching symbol in sites.dat using the s: option:

      Example:

      IRDM11.SA s:BVMF:IRDM11

      Delete
    6. That didn't show very clear. There's a space before the s: option.

      Delete
  46. When viewing stock/mutual fund quotes in the default browser, some of the displayed names are not correct. Here's an example line (Y for Yahoo): Y VHCOX Vanguard Horizon Fund, Vanguard 66.79 3/23/2018 06:45PM -2.5%
    The correct name for YHCOX on Yahoo is Vanguard Capital Opportunity Inv.
    Thank you

    ReplyDelete
    Replies
    1. Interesting. Yahoo is using the "fund family name" for the "short" name parameter. I modified the script to use the "long name" instead. New version posted.

      Delete
    2. Tested your new script. It corrected the name issue, but created an error for Index Quotes from Yahoo. Here's an example: ** ^DJI : invalid quote response. Skipping...
      Sorry for the trouble.
      Thank you

      Delete
  47. Also currencies are giving the invalid quote response. ** EURUSD=X : invalid quote response. Skipping...

    ReplyDelete
    Replies
    1. Script updated. New version posted for testing.

      Delete
  48. Google quotes for US mutual funds aren't being parsed correctly for ~1 month. I switched to Yahoo and that works fine.

    ReplyDelete
  49. I upgraded to the latest Pocket Sense and am having some problems. If I edit the sites.dat file, Setup.py will not run properly. I opened a cmd console to attempt to troubleshoot and found the following: List Accounts does not list the accounts, does ask if I want to show connection keys. If I choose Save and Exit I get the following message: Traceback (most recent call last):
    File "C:\Program Files (x86)\Pocket Sense\PocketSense 180324\Setup.py", line 338, in
    f = open(cfgFile, 'wb')
    IOError: [Errno 13] Permission denied: 'ofx_config.cfg'

    I changed the permissions in the Pocket Sense folder to match those of previous versions of Pocket Sense but still get the same message. Do you have any suggestions on what to try to resolve this? Thank you in advance.

    ReplyDelete
  50. DDE12, I don't put my PocketSense files into the Program Files. Anything inside that folder is treated specially, and a user cannot write to files. There is a thing called the Virtual File Store that can come into play, and that can confuse things.

    I would move/copy my PocketSense files to some sub-folder inside of your Documents folder or other place you will have full privilege on. Alternatively, you could start your command window by right-clicking the launch icon, and choosing RunAsAdministrator.

    CL

    ReplyDelete
  51. I ended up starting with a clean sites.dat and that solved some of the issues. I have also moved it to the root directory. I am still having a problem with PNC and Janus. The PNC ofx does not have any transaction data (occurred with previous PocketSense version) and the Janus ofx indicates that I am locked out (I have to go to the website and unlock account after the PocketSense attempt). The Janus lockout is a new problem? Do you think this is a PNC and Janus problem or an issue with PocketSense?

    ReplyDelete
    Replies
    1. Janus works for me with PocketSense. I am not clear if your Janus problem was a one-time problem that you fixed by going to the website, or if PocketSense is not working for Janus at all.

      Here is my descriptor:

      SiteName : Janus_Inv
      AcctType : INVSTMT
      fiorg : Janus
      fid : 6115
      url : https://ofx3.financialtrans.com/tf/OFXServer?tx=OFXController&cz=702110804131918&cl=50900132018
      bankid :
      brokerid : dstsystems.com
      appid : Money
      appver :
      mininterval:
      timeOffset :


      CL

      Delete
    2. This lockout was occurring every time I tried to download statements with PocketSense so I repeatedly had to go to Janus website and unlock account. In case anyone else is having the same issue I had w/ Janus, I was able to get it resolved with the following changes. Added 6115 for fid. Removed 1900 for appver. I took the connection key for Janus from the previous version of PocketSense and replaced it in the connect.key file for the current version of PocketSense. (I have no idea what the connection keys are for and might not have anything to do with the financial institutions.)

      If anyone has advice on what to try for PNC, I would greatly appreciate it.

      Delete
  52. Connection key is the verification step from your initial logon. I updated my settings page for Janus. Thank you CL. The PNC settings on that page are current and work for me everyday.
    https://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/

    -ameridan

    ReplyDelete
  53. Anyone experiencing issues with BofA savings accounts? Checking account works fine with the settings listed on ofxhome (http://www.ofxhome.com/index.php/institution/view/639) and ofxver=103 and appver=2400 , but when downloading from savings, I get the following error even though I copied the account number directly from the website: "The account you have entered is incorrect. Please verify that the entire account number has been entered, including any leading zeros."

    ReplyDelete
  54. Just a quick reminder, but be sure you're running the latest version of Python 2.7.x when trying to debug a new issue. If you happen to be running an outdated ssl library, and the bank upgrades their server, you may get a misleading error message :(

    ReplyDelete
  55. If your credit union stopped downloading around March 30, and the url in sites.dat is
    https://ofxdi.diginsite.com/cmr/cmr.ofx
    you might try changing this to
    https://ofxdc.prd1.ncr.com/cmr/cmr.ofx

    This info is gleaned from the moneydance discussion at http://help.infinitekind.com/discussions/problems/43717-wont-connect-to-my-bank-anymore
    and the moneydance dictionary (equivalent to a global sites.dat) at
    http://moneydance.com/synch/moneydance/fi2004.dict

    In the discussion, moneydance support states that Firefighters First Credit Union and SAFE Credit Union are now working. I copied the new url for Firefighters First Credit Union from fi2004.dict and applied it to Affinity FCU, and I am now back in business.

    ReplyDelete
  56. Hi, All of a sudden I'm getting the following errors when i run Setup.py

    line 166 in module of Setup.py
    NameError: name 'get_cfg' is not defined

    I have the very latest Active Python and the latest March 24 2018 script versions. IT was occuring on previous versions too, so I decided to udpate just to see if it will fix the issue - and it didn't. Any hints?

    ReplyDelete
    Replies
    1. By "all of a sudden", did anything change prior?

      Delete
    2. Nothing changed...it used to work, and then all of a sudden i started getting this error message. i reinstalled the latest 2.7 branch of active python and updated script to the latest version, the error message is identical.

      Delete
  57. I solved the issue, not sure what the root cause was. Basically If I ran Setup.py it worked. IF I ran the shortcut i created for Setup.py many years ago i got the error message about get_cfg (I've created an html file with the link to the shortcut to Setup.py so that can integrate with the GUI via favorite websites). I had to delete the link file and recreate a new one and now the link works too. Very strange given that the link file never changed for years and it always worked. But i did notice that creating the link file now in Windows 10 - the file size is different than it used to be (old file was created years ago probably in Windows 7).

    ReplyDelete
  58. I run a batch file which runs getdata.py
    I have the batch file cd to the right folder first.

    CL

    ReplyDelete
  59. Hello!

    I was able to successfully setup my USAA banking and investment accounts. My banking accounts download fine and my regular brokerage account download fine. My Roth IRA account generates a "The file you attempted to import appears to be invalid or contains corrupt data. Please download the file again. If this problem continues, contact your financial institution."

    The ofx file looks normal and contains transaction data but I don't know what part of it is throwing off PocketSense (well...MS Money). Can anyone help?

    Thanks,
    Andrew

    ReplyDelete
  60. Andrew, consider posting the OFX from the XFR file that does not work. You can change the account number to 123456 or some such.

    CL

    ReplyDelete
  61. Andrew, on second thought, this is not the best place to post OFX. It will mess up the tags.

    You could post on a different site, including the Microsoft Money forum. That takes pasting OFX contents into a post OK.

    CL

    ReplyDelete
    Replies
    1. I figured out the issue with some trial and error by editing the OFX file. The offending entries are my holdings in IVV and IJR, the "ISHARES CORE S&P 500 ETF" and "ISHARES CORE S&P SMALL-CAP ETF". The ampersand symbol is choking MS Money. It worked correctly when I replaced it with "&". How do I get the scrubber to do that automatically?

      Delete
    2. Ooops, I replaced it with " & a m p ; " (remove spaces).

      Delete
    3. I'll modify the script to replace the ampersand. To clarify, this is in the SECNAME field?

      Delete
    4. Yes, here's the example (I put spaces around the tags, let me see if it publishes correctly):

      < STOCKINFO >< SECINFO >< SECID >< UNIQUEID >464287200< UNIQUEIDTYPE >CUSIP< / SECID >< SECNAME >ISHARES CORE S&P 500 ETF< TICKER >IVV< UNITPRICE >266.97< DTASOF >20180410120000< CURRENCY >< CURRATE >1.0< CURSYM >USD< / CURRENCY >< / SECINFO >< STOCKTYPE >COMMON< / STOCKINFO >

      In one of the transactions before that section, the MEMO field correctly uses " & a m p ; ".
      This code worked for me:
      http://pocketsense.blogspot.com/2011/04/new-production-version-of-money-scripts.html?showComment=1327441570847#c3013650454195662035

      Delete
    5. The code you referenced is equivalent to a global replace. I don't recall seeing an ampersand used elsewhere in ofx for an actual purpose, so I'm guessing it's ok (i.e., rather than only replacing in name and comment fields). How does Money handle the amp coding? Does it show the escaped code (i.e, &), or does it replace w/ an actual ; char? If it keeps the code, may as well just strip it (i.e., s&p 500 becomes sp 500, or maybe a + symbol as s+p 500)?

      Delete
    6. Hi Robert,
      Money appears to correctly import it as "&".
      1) The "correct" way would be for my bank to properly escape the character in SECNAME fields. Not likely.
      2) The "safe" way would be to update the scrubber routine to find "&" between SECNAME tags and make sure it is properly escaped.
      3) The "it works" way is to properly escape all "&"; which is what the above referenced code does(?). The current code, however, does not tell you when it has done a replacement (like when the scrubber fixes an improper negative sign or something).
      Thanks!
      -Andrew

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

    ReplyDelete
  63. Here is a line that can be added to scrubber.py to change all non-escaped ampersands with an escaped ampersand, which OFX needs:

    From scrubber.py of "Updated: 24-Mar-2018 20:50 EST"
    line 73: ofx = f.read() #as-found ofx message
    line 74: #print ofx
    New 74A: ofx=re.sub(r"&(?!\w+;)", r"&",ofx) # escape singular ampersand

    There are 4 spaces before the first "ofx". If the first space is position 1, the last ")" is position 42


    Here is line 74A with space after the 2 ampersands:
    ofx=re.sub(r"& (?!\w+;)", r"& amp;",ofx) # escape singular
    Here is the part before the comment with space replaced by _ and ampersand with @.
    ____ofx=re.sub(r"@(?!\w+;)",_r"@amp;",ofx)

    Here is the line in hex for the ASCII up to the #
    Hex: 20 20 20 20 6F 66 78 3D 72 65
    Hex: 2E 73 75 62 28 72 22 26 28 3F
    Hex: 21 5C 77 2B 3B 29 22 2C 20 72
    Hex: 22 26 61 6D 70 3B 22 2C 6F 66
    Hex: 78 29 20 23

    CL

    ReplyDelete
  64. 1. I was surprised the line was not mangled, other than the leading spaces, so my extra stuff was unnneeded.

    2. Experimenting, I see Money Plus accepts escape codes in the numeric forms. Adding in the "#?" into the string would pass these through.


    New improved 74A:
    ofx=re.sub(r"&(?!#?\w+;)", r"&",ofx) # escape singular ampersand



    CL

    ReplyDelete
  65. Thanks Cal. I updated the script to include the substitution. For others, the following logic is being applied: Replace ampersands '&' with the escape code for an ampersand, *except* if it's already part of a valid escape code.

    New version posted.

    ReplyDelete
  66. Hi.... is anybody having trouble with Fidelity Netbenefits (the 401K side of Fidelity)? The last few days I have been getting an error code 15500 along with a message "error logging in".

    ReplyDelete
    Replies
    1. Hi Harold,

      I'm not a NetBenefits client but there are reports over on the Moneydance forums that there are indeed issues connecting to the NetBenefits server.

      -Kevin N.

      Delete
    2. Thanks, Kevin...

      I see the discussion. I'm hoping that Fidelity can fix it on their side, but in my experience, this often degenerates into some kind of security tweak that becomes necessary.

      Delete
    3. Hi Harold,

      Fidelity Netbenefits has worked flawlessly for me for a long time. Just checked again, and no issue whatsoever with my connection through pocketsense. If you are still experiencing problems I'll be happy to help troubleshoot.

      Delete
  67. I see that my prevous attempt did lose 'amp;'. Here is the one-liner I had intended (at least I think this will come through):
    ofx=re.sub(r"&(?!#?\w+;)", r"&amp;",ofx)

    Robert expanded the obscure line to be more readable and to print out the fact, during execution, that a substitution had been needed.

    For those reading along, that is the section starting at line 407 of the April 15 2018 scubber.py.

    CL

    ReplyDelete
  68. Robert - I have been trying to get to download Merril Lynch for a while now. Here is where i am and what I have done:

    Turned on debug (true) in control2.
    modified the code below in ofx.py and i can see the ofxqqf files

    qqfFileName = ofxFileName + “qqf”
    qqf = open(qqfFileName,’w’)
    qqf.write(query)
    qqf.close()

    When i test, with Merrill Lynch, I get an error line 412 in getofx in the ofx.py. Looking at the debug info on the terminal, i see
    1. The OFX statements are built and a “post” command is sent to server. Connection request is set to Keep-Alive
    2. Reply comes back as HTTP 1/1 200 OK\r\n plus bunch of header info and set cookie info. But connection is "Close."
    3. User terminal attempts a second (with connection Keep-Alive) and third time (i guess you added this for the discover card problem??) before i get the exception error message in line 412.

    I tested another investment account and i noticed that the “header: Connection: comes back as Keep-Alive and the keep alive timer. For merril Lynch it is always closed.

    How do I figure out why connection is closed? OR does this mean the server is refusing the connection? Am I looking at this all wrong?

    I downloaded fiddler to see if I could get additional details on the response but could not get that to work.

    ReplyDelete
    Replies
    1. Hi Sam. I don't use Merril, so can't test connections there. Anyone else using Merril?

      Did you review the Merril entry @ http://moneydance.com/synch/moneydance/fi2004.dict, to verify settings?

      Delete
    2. Yes I did. The only thing i was not clear on is how to use the field "uses_fi_tag" = "y"

      Delete
    3. Hi Sam/Robert; I don't know if this is of any use, but i noticed that the error reply file from ML when using pocketsense has the header entry "SECURITY:NONE", whereas a successful download statement using the Merrill Edge website has "SECURITY:TYPE1". Just in case that sparks any ideas...

      Delete
  69. Hi, Jon...

    The problem seems to have resolved itself on the Fidelity end yesterday.

    Thanks for the help offer, though!

    ReplyDelete
  70. Sam A said: 'The only thing i was not clear on is how to use the field "uses_fi_tag" = "y" '

    I think that states whether the server cares about an "fi_id" in MoneyDance or an "fid" in PocketSense.


    That field is a Quicken thing and not part of the OFX specifications. Clearly Quicken had a lot to do with setting up most of the servers.

    CL

    ReplyDelete
  71. I'm having an issue with Fidelity. I'm trying to download & import transactions from their Cash Management account, which is their "pseudo" checking account (pseudo in that it has checking account features but it's not from a bank).

    I've set up my pocketsense sites.dat to look like:

    SiteName : Fidelity Investments
    AcctType : BASTMT
    fiorg : fidelity.com
    fid : 7776
    url : https://ofx.fidelity.com/ftgw/OFX/clients/download
    bankid : 1117
    brokerid :
    ofxVer : 103
    appid :
    appver :
    mininterval:
    timeOffset :


    This allows me to download the transactions successfully, but I'm running into a problem with extra transactions being downloaded. For example, I took $20 out of an ATM this week. OFX includes a transaction showing this debit:
    DEBIT2018041912000020180419120000-23.00000[redatcted]CASH ADVANCE ATMD301 460 8TH AVCASH ADVANCE ATMD301 460 8TH AV NEW YORK NY: ($20 debit + $3 ATM fee)

    But it's also downloading an unnecessary credit transaction for the "sale":
    CREDIT201804191200002018041912000020.00000[redacted]YOU SOLD @ 1:SYM=QUSBQ SHR=-20.YOU SOLD @ 1:SYM=QUSBQ SHR=-20.00000 PRC=1.00000 COMM=0.00000 DESC=FDIC INSURED DEPOSIT AT US BANK NOT COVERED BY SIPC

    Is this something that could be fixed in the scrubber? If someone gave me a tip where to start, I'd be happy to take a shot at it.

    ReplyDelete
    Replies
    1. Oops, sorry, Blogger stripped all the XML markup from my post. See https://pastebin.com/7W8m3nRB for the transaction with markup.

      Delete
  72. Larry, you should try changing from Bank Statement to Investment in sites.dat. That is the way I handle my Schwab acct with checking. Then the QUSBQ downloads should be handled properly, as they are really transfers from the investment acct to the cash account.

    -ameridan

    ReplyDelete
    Replies
    1. @Nxttek, if I change from Bank Statement to Investment, I can't import the transactions into a checking account in my finance app (Moneydance)

      Delete
  73. I suspect QUSBQ represents your sweep money market fund.

    In that case, it is not so bad to have those transactions, in the sense that that is really what is happening... although I would not want them either. However having them would have the advantage of being able to track the return on the money market. For me, I get interest paid, but no buys and sells. So the reports think the MMF gets great returns. If you track them, the cash transactions register will normally show a zero balance. It's a mixed plus and minus.

    I use the regular Fidelity account. I am not sure what differences apply. If you go to your Fidelity web page, is there a tab for "Activity"? In a regular account, there is "Activity & Orders", Under that is History, and there is a check-box called "View settlements in core". Ticking and unticking shows or hides the sweep transactions for me. Try that box. At least it will show what is going on. I would hope that you have that box ticked, and somehow, if you untick it, it will not put those transactions into the OFX/QFX file.

    If you were sufficiently motivated, you could make a scrubber item to remove buys and sells of QUSBQ. I think you should try to embrace it or at least tolerate it.

    CL

    ReplyDelete
    Replies
    1. @CL, the checkbox for "View settlements in code" was indeed unticked. (Not sure this setting makes a difference with what is exported to the OFX file)

      Delete
  74. Ameridan, I had not noticed the BASTMT.

    It makes me wonder how the account is set up in Money -- as a bank account or an investment account.

    I also now see that Larry C has "bankid : 1117". I suspect Fidelity is ignoring that field. 1117 I think is a MoneyDance code not used by the institution.

    CL

    ReplyDelete
  75. After download transactions not automatically imported into Money.

    Previously when i ran getdata to download account statements, it would automatically be imported into Money. Now each time, the statement is opened (in notepad) and i have to close it before the next account is opened. Then I have to manually import it. It feels almost like i kicked off an interactive process.

    I will confess I was making changes to my system. So i reinstalled both pocketsense and Money but i still have this phenomenon. Any suggestions on what maybe causing this? I use WIndows 10.

    ReplyDelete
  76. Sam A, your .ofx file association somehow got switched to Notepad. I would have expected the Money reinstall to get that fixed. The association should be for the Money Import Handler.

    Here is an export of a key that may be the right one if using Money Plus... You can compare vs yours.

    ==============================================
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ofx.Document\Shell\Open\Command]
    @="\"C:\\Program Files\\Microsoft Money Plus\\MNYCoreFiles\\mnyimprt.exe\" %1"
    ==================================================

    Note the last line starts with [HKEY and ends with 1". So watch for word wrap.

    I would maybe reinstall Money, but run the install file by right-clicking it, and choosing RunAsAdministrator.

    ReplyDelete
    Replies
    1. Thx. The registry file is exactly as you have listed. I installed Money by choosing RunAsAdmin and still the same. Any other suggestions?

      Delete
    2. Anonymous - Thanks for the guidance. I fixed it by going into explorer and reassociating the file with Microsoft Money. Seems to ahve worked..checking other accounts!!

      Delete
  77. I am posting about a solution to a problem I had that may help somebody else.

    Way back when Money itself did what Pocketsense does, I recall that you set up online banking with an *institution*, and it gave you a list of accounts you had with them. You'd then associate one of their accounts with your Money account. Apparently, Quicken has something like this capability, too.

    I was wondering if there were any way to use Pocketsense to get this "list of accounts" for a particular institution.

    Background: I use a site for some of retirement funds. For reasons of their own, they set up a second account on their system for me. I don't know the internal number for that account, and neither do their customer service people (it shows on their screens with only a name, not a number). But in order to use Pocketsense, I need to log on with an account number. So how to get the account number?

    It turns out that if you leave the account number blank in the OFX transaction, this site (and I am guessing others) respond with your account numbers. So I set up an entry in Pocketsense for this site and my userid and password with a blank account number and let Pocketsense "test" it, and did not send the response to Money. Using Notepad, I then looked at the file I got back, and found my account number.

    Everybody probably already knew this, but this capability was news to me.

    Harold

    ReplyDelete
    Replies
    1. I had forgotten this. I was playing with it at some point, and planned to add a menu item in setup to do this for any account, but let it slip. As it is now, it's more-or-less a place holder. If you pass a null account to the ofx request it will do an "account query" of the site entry for the username/pw provided.

      I think I'll add a "get account list" option in Setup to make it easy. The script is already written, and has now been tested by more than just me ;) Another option would be to insert this into the "add account" script, so that it shows the list during setup. Opinions?

      Delete
    2. I like the 2nd idea Robert. That is when you are most likely to use this option and even though you could know your account number, you might not know how many leading zeroes, for instance, that financial institution is expecting in order to recognize the entry.
      - ameridan

      Delete
    3. Hi Robert,

      I agree with NxtTek -ameridan, that your 2nd option could provide the most advantage.

      In MS Money (in the account settings) the account number is partially redacted so there is no easy means of gathering a user's various account numbers.

      -Kevin N.

      Delete
    4. Kevin, be aware that you can select "Account balances with details" in Reports to see full account numbers.

      Delete
    5. It sounds potentially useful, especially to those getting started. It could be expanded to create the resulting entries with the same username and password. Not everybody knows how to copy and paste from/to command windows. Still, just getting the account numbers could be useful, especially for FIs who insist on leading zeros.

      Speaking of potential features, I invoke my Pocketsense by having the Windows scheduler launch a batch file daily. I tell the batch file to only go out for data at about 5 AM on Tuesday...Saturday. This is to avoid duplicate stock price entries because the markets are closed Saturday and Sunday. I could have two different PocketSense folders with the added one not going to those who deliver pricing info. I have not done that. If there was something my batch file could do to select which FIs to check, I could check credit card info on the other two days. Not a big deal.

      CL

      Delete
    6. This helped me too with my BofA issue where it wasn't downloading the savings account. Thank you!

      CH

      Delete
  78. Harold, I tried what you describe for Fidelity Investments. It worked. For each account, there was an ACCTID, containing an account number, in the OFX file.

    CL

    ReplyDelete
  79. A while back somebody suggested that if somebody had multiple accounts at an FI, PocketSense be able to make a single request for all of the data. That would prevent a change of password at the FI, but forgetting to change PocketSense, from locking the account because of multiple failed attempts.

    I thought of related idea. How about the ability to pause the operation sequence if the login came back with a bad password indication in the response packet. That would prevent the multiple failed attempts. The command window could stay open with a "press any key to ..." message.

    CL

    ReplyDelete
  80. Not sure I understand your question, but you are supposed to use the setup routine anytime your access is in question, so that you can test individually.

    ReplyDelete
  81. I think that was my original question. Normally, when setting up you WOULD use the setup routine. The issue I had was that I changed the password directly at the financial institution. I forgot to change it in PocketSense. Ran PocketSense - boom 5 accounts = 5 attempted logins with the wrong password. Account locked.

    ReplyDelete
  82. I haven't played with the "bad password" idea, but what specific phrase would qualify for prompting a user? I'm guessing the literal string "password" only shows when the server is notifying something about a password, which (I'm pretty sure) won't show up otherwise? Any other (unique) keywords that would trigger the same check?

    ReplyDelete
  83. I did a test with 4 FIs.
    For 3 I got code 15500. For one I got an HTML message.
    Comments prefaced by #
    OFX error 15500 is "The user entered an invalid userID or password."

    #curly brackets substituted for pointy.

    #fidelity
    {STATUS}
    {CODE}15500
    {SEVERITY}ERROR
    {MESSAGE}Error occurred logging in
    {/STATUS}

    #citicard
    {HTML}
    {HEAD}

    {TITLE}Access Denied
    {/TITLE}

    {/HEAD}
    {BODY}

    {H1}Access Denied
    {/H1}

    You don't have permission to access **********
    {P}
    #etc.....

    #vanguard
    {STATUS}
    {COD}}15500
    {SEVERITY}ERROR
    {MESSAGE}Invalid signon
    {/STATUS}


    #Janus

    {STATUS}
    {CODE}15500
    {SEVERITY}ERROR
    {MESSAGE}SIGNON INVALID
    {/STATUS}

    fake acct number used on this one only
    {STATUS}
    {CODE}15500
    {SEVERITY}ERROR
    {/STATUS}


    # CL

    ReplyDelete
  84. I was getting an ssl error with fidelity brokerage

    Message was
    ** An ERROR occurred sending POST request to ofx.fidelity.com
    Exception type :
    Exception val : [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:661)

    To get around it I had to tell the https connection to trust unverified certificates with context=ssl.create_default_context() in ofx.py. This was done due to a change in python 2.7.9 where it now fully validates certificates.

    https://docs.python.org/2/library/httplib.html#httplib.HTTPSConnection




    ReplyDelete
  85. I am running Active State Python 2.7.12.

    I just re-tested with Fidelity Investments. No problem. My ofx.py does not contain "context"

    Here is my sites.dat entry with curly braces instead of pointed:
    {site}
    SiteName : FIDELITY
    AcctType : INVSTMT
    fiorg : fidelity.com
    url : https://ofx.fidelity.com/ftgw/OFX/clients/download
    fid : 7776
    bankid :
    brokerid : fidelity.com
    appid :
    appver :
    mininterval :
    timeOffset:
    {/site}


    CL

    ReplyDelete
    Replies
    1. Thanks CL,

      I tried just tried that with my current version of python - 2.7.13 and then again with the latest - 2.7.15. Both failed on the code without my modification. I guess I could try downgrading the 2.7.12 and see what happens. The ofx.fidelity.com python appears valid, so I am not sure why it is failing.

      BK

      Delete
    2. Hmmm. Are you calling ssl.create_default_context() with default parameters, so that it uses the default system "certificate authority"? I'll need to read a bit more, but I was of the impression that HTTPSConnection() automatically validated certificates & hosts. What version of Windows and Python distribution?

      Delete
  86. BK, the latest version of ActiveState Python is 2.7.14. You might try the ActiveState Python. That is the one Robert develops with and recommends.

    CL

    ReplyDelete
  87. I was looking at the https://www.activestate.com/activepython/downloads site. I run 32-bit Windows (x86). Dang. It looks like ActiveState no longer has an easy-to-find 2.7.12 download for me. I suspect they are hiding it somewhere.

    My version is "Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:19:22) [MSC v.1500 32 bit (Intel)] on win32:

    Looking through the files, license.txt does reference activestate in the copyright.

    In retrospect, should have kept the installer package for my version.

    CL

    ReplyDelete
    Replies
    1. Well, I'm not sure why, but it is working now. Maybe it was transitory at Fidelity or maybe my python update needed a reboot.

      By the way I run the anaconda distribution on 64-bit due to using python for other projects.

      Thank you for your help

      Delete
  88. I just uploaded a beta version for testing, and started a new post/thread, so I'm closing out the comments on this one.

    ReplyDelete