Blogger Ron said... Cal (or Robert or other knowledgeable Python programmer)
Taking your and NxTk's advice, I wrote a scrubber that changes the last digit of the FITID to a "1".
It seems to work and be restricted to only my savings account at TD Bank.
However, this is the first time I've programmed in Python. Would you mind taking a look and seeing if I did something that might cause a problem down the road?
``` #TD Bank: Remove last digit of FITID from Savings Transactions #29 Jan 2024
from control2 import * from scrubber import scrubPrint import re
def scrub(ofx, siteURL='', accType=''):
if 'eftxweb/td.ofx' in siteURL.lower() and accType=='BASTMT': global stat stat=False
#Check that we are dealing with the correct account if re.search("ACCTID>##############", ofx): scrubPrint('TD Bank Matched Savings Account')
#We are, so change the last digit to a "1" ofx = re.sub(r"(?PFITID>\d{19})\d", r"\g1", ofx) scrubPrint('Last FITID Digit changed to "1"')
I don't follow ofx = re.sub(r"(?PFITID>\d{19})\d", r"\g1", ofx)
I don't know r"\g1". I am not familiar with a \g metacharacter.
And whatever you had in your code, I suspect you has a > character that you did not replace with these four characters: >
Does your code work, or have you not tested it yet? The string I posted in post 200 of http://pocketsense.blogspot.com/2023/07/new-version-available.html should work.
Also, have you checked that all non-duplicated FITIDs end in a "1"? That still might work if the rest of the characters are unique.
The match string is using a Named matching group (FIT). In the replace string the \g says to use everything that was matched by FIT.
For numbered matching groups, one could use \g1, but if one wants to add a 1, the construct becomes \g11 which is confusing to the regex engine because it starts looking for group 11. So I used a named group to avoid that problem.
Does seem to work.
In the savings account, there have been NO non-unique entries. In the checking account, (which this scrubber does not process as I check for the account number before running it), the last digit seems to be anything. I don't have enough data to be sure that the first 19 digits will always uniquely identify any transaction.
And also, at least so far, those non-unique entries have all had the first 19 digits the same, and they have all started with `1`. But I only have a database of two entries -- one deposit transaction and one interest transaction. That'll be a good thing to look for going forward. And should be easy to detect if there is an anomaly. Thanks for pointing that out.
dammit... I meant to write And whatever you had in your code, I suspect you has a < character that you did not replace with these four characters: <
If you want to know how to use pdb to test little code snippets with pdb, let us know. But first tell us that you are able to copy and paste from/to a command window. You can Google how to do that that.
In an earlier post I showed how I tested my string in pdb. The "(Pdb)" is a prompt from pdb -- Python debugger.
NxtTek: Looking at the code as posted here more closely, it seems it did not post as it really exists. It shouldn't run at all unless it matches the bank and account number (and it doesn't here). However, the code as posted is missing indents under both the first and the second nested IF statement. I wonder if that could be your problem. I'll see if I can get it to post correctly.
NxtTek: I couldn't figure out how to post it here properly formatted. Here's a link to a text file containing the code which should be properly formatted: https://1drv.ms/t/s!Aml6o6HIc9uOy28nDi-NrRdzURAI?e=tuaOMJ
It's been mentioned before, but it does look like it is only a matter of time before Fidelity access closes. I found the following text on their web site: "To enhance the protection of your account data, Fidelity has established a secure connection that better controls how third-party websites and apps that you've authorized, and the data aggregators they use, connect to your accounts. Fidelity is requiring these data aggregators to transition to this secure connection. Fidelity users of some third-party websites and apps may experience a disruption in the link between those websites and apps and their Fidelity accounts. "
I'm going to be optimistic, thinking that's in reference to web scraping ;) Fidelity saved the day from Schwab killing their ofx server, but I don't think there are any more saviors out there, if Fidelity does the same. - ameridan
The current situation lets your program (PocketSense in our case) access your transactions and positions directly without involving Yodlee, Quicken etc handling your data. Their announced intention is to require you to go via a third party that they work with to then provide the data to your computer or to a cloud app.
See the official response in https://www.reddit.com/r/fidelityinvestments/comments/17tpgfr/why_doesnt_fidelity_support_ofx_downloads_anymore/?rdt=47495
So it would be more precise and robust than screen scraping, but would still require that your info go to a third party. It would be safer in that the third party would not have your Fidelity credentials. It also would provide an income stream to Fidelity. I don't begrudge them that, but I like to minimize the sharing of my financial data.
Thus the urgency to be able to generate OFX files, that can feed into PocketSense, from the history and positions CSV files that you can download from the Fidelity website. This is extra work over how PocketSense now works with Fidelity, but worth it IMO. I don't know if Vanguard will continue to offer OFX directly,
A free Fidelity to OFX converter from Cal Learner (fidcsv3 or F2O name subject to change):
Ameridan's blog nicely documents where this program/project stands and explains the methodology.
See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/
While the current main program is named fidcsv3.py, it will probably be renamed to something like F2O. py or f20.py at some point. Since this runs on windows, the filename will not be case-sensitive.
Advantages:
o open source o developed on Python 2.7 as PocketSense uses free o can be used with PocketSense or without (I do recommend PocketSense) o puts both transactions and positions and price info into the OFX files, just as with the OFX files that currently come in from Fidelity via PocketSense o still being improved, so any bugs bug reports or suggestions posted here will be reviewed and probably acted upon o the program finds your latest Fidelity positions and history CSV files in your Downloads folder without further interaction needed.
Disadvantages:
o not professionally coded. o lots of global variables, o not modular. o the coding style evolved since being started March 2022, so method of doing the same thing is not constant thru the program(s) o the code is not already openly published, but that will change if there is any interest. human interface not polished, but will probably improve o user must download the positions and history CSV files from the website for each run, but the program does find those in your Downloads folder without further interaction needed. Download from web is easy enough.
Questions and comments are welcome. I expect interest to pick up after the Fidelity OFX server disappears. But by that time, it would be too late for some things that you would miss out on that would would have made the operation initially easier.
I have decided to open the Fidelity CSV to OFX (F2O) beta to all now. I have a folder that should be accessible given the link. This is a folder rather than a file: https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing
In the folder is f2o_beta3.zip which has several files. One file is F2Obeta.doc in Word 97 format, and F2Obeta.txt which is is the text-only version.
Feel free to post comments, bugs, or why did you do something the way you did. The code evolved starting in March 2023.
That worked. I have downloaded the zip file and extracted it but I didn't find any python files and the .doc file seems to be a binary file of some sort???
First, yes I did miss important files. The .doc file did work for me when I just tested. It is intended to be opened by Microsoft Word or other program that can read Word .doc files. I have added a *.txt version too.
New method. This is a folder rather than a file: https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing
I will delete the post with the old link. The new method should mean that I don't have to create a new link to change content.
It contains f2o_beta2p.zip and a simple txt file that does nothing.
That worked. I'll try out the F2O_DBgather.py program along with the bat files. The doc file will open in ms word. On my system the .doc extension automatically opens in a text editor. Thanks
The F2O_DBgather.py program worked on my data except for one ofx file (one generated from a cash management account which is just a checking account from Fidelity). It errored because the split_ofx list has only 1 entry in it. ( the ofx does not have any since it only contains a cash entry). Since the file F2O.py was not in the files shared with me, I can't proceed any further.
I will need to deal with that. Currently I have all of my cash in my cash management account used to purchase SPRXX. I should sell some of my SPRXX to generate an entry for the core position for testing. I had intended for that F2O.py file to be there. It is there now with the same zip filename, but a little bigger.
Both programs (F20.py and F20_DBGather.py) worked well with my data other than the cash management account mentioned previously. Thanks, I think these will be useful in the future.
dealt with it. f2o_beta3p.zip contains a fixed version of F2O_DBgather.py It also has a copy of F2O_DBgather.bat.bk1 which is my zip file, but is intended to be modified for your file structure. It is not called F2O_DBgather.bat in the zip file to avoid accidentally overwriting a batch file you already modified.
Besides the bug fix, F2O_DBgather.py accepts an optional second parameter (integer) that would set the DEBUG level. Default is zero.
I have installed Anaconda, downloaded the scripts, and not having any luck getting started. When I double-click Setup.py, nothing happens. Next, I ran the Anaconda Prompt and typed in Setup.py. This gave me a couple of errors, one on import urllib2 and another on pyDes.py (print 'Invalid Password' - SyntaxError: missing parentheses). Any suggestions on how to get started? Thank you.
In setup.py, doing the "2. List Accounts" choice, I do have ConnectKey: listed for each of two accounts. I did not do anything to set up that. When I do the rare move of my PocketSense to another folder, I copy my connect.key and ofx_config.cfg files over to the new folder.
While I have a Citi bank account, I don't think they provide an OFX server for that. I use their web interface to get a qfx file. I do that very rarely because it is a bit tedious. I make sure the dates I download do not overlap with the transactions in Money because they don't/didn't have the thing about having the FITID the same each time working.
Thank you gents. My sites.dat setup matches yours Cal (and yes, i should have specified that this is indeed for a credit card which has worked beautifully up until now). It appears to be a credential issue, however. I do get a response from the ofx server with the following:
ERROR Information does not match with our record. Please make sure you select the correct Financial Institution and enter correct username/password.
I decided to nuke the connection and set it up again...and yeah, it chokes on my credentials (the same ones that work perfectly when logging in on the citi website). I am a bit stumped on this one if others are not seeing the same... oh well... I do have other ways of getting my citi transactions so it is not a big deal. But this means my pocketsense direct connections are down to just Fidelity and Amex... :S
Unless something has changed, I believe you need to enable direc access on the Citi site. Not at computer now, and don’t recall the details, but you add a new connection from an account menu and *then* add the account in PS during the ~ 10 min window. -Robert
Thanks Robert; I suspected that may be the case, but I looked high and low on the citi website and could not find such a feature... No rush, but if you do find that menu option on your end please do report back. i did find some references to Quicken, but that is all in context of Web connect which of course is different.
Oooo... Now that you say that, I kinda remember that discussion.
In http://pocketsense.blogspot.com/2019/08/comments.html see "After making the sites.dat changes, I did not need to use the Setup.py script, but I did need to use the Citi 10-minute process to relink the PocketSense scripts to my Citi account. The easiest way to do that is to log in to https://citi.com/datamanagement. "
Thank you Cal; that did the trick and I am reconnected through Pocketsense now. Maybe my authorization (prior one evidently from 2022) expired, so i wouldn't be surprised if others find that the connection stops working at some point. How one can find that critical page without knowing the direct link is still a mystery to me. Will make sure to save the link for posterity. Thanks again!
The actual procedure has been on my settings page all along (for posterity) @ https://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/
Anyone use the PocketSense scripts to download Regions Bank account data? I've used them for years without issue. My downloads stopped working this past weekend and are still not working. It would appear to me that Regions as shut down their OFX server much like USAA did earlier this year. Just trying to verify that that's the case.
Hi John, Here in Georgia, using the PS scripts, Regions Bank is working as expected with both BASTMT and CCSTMT type accounts. In sites.dat I set userAgent: none Also delay: 3 I set the delay because I was getting bounced out midway through the downloads. HTH -Kevin N.
I started getting an internal server error this past week. I don't receive an OFX response back when I try to download transactions or when I try to set up a new account. It's really strange.
Hi John, Check to make sure your Profile at Regions has not changed. Log on to Regions. Hover over 'Customer Service', select 'Settings'. Click the 'Manage Profiles' link. Here, make sure the options for 'Online' and 'Quickbooks' are checked. Click 'Save'.
Kevin - None of my profile info has changed. I tried doing a completely clean install on another machine to see if I could run the setup script to download just my Regions accounts. The script fails on that machine as well. I suppose Regions could have blacklisted my IP address as one of my OFX account requests for a new account that was setup in January was routinely failing. If they had done that though, I shouldn't be able to log into regions.com either.
I added kevin's recommended changes to my settings page. thx https://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/
Hi Dan, Thank you for maintaining your site. It is a trove of valuable information. Just to clarify, my suggested 'delay' setting for Regions Bank and Regions Credit Card is 3 seconds. It was inadvertently entered as 30 seconds on your site.
F2O, a Fidelity-specific CSV to OFX converter project is actively in progress. See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/
The user could select to have the generated Fid*.ofx files go to the PocketSense import folder, but that would best not be done until the Fidelity OFX server shuts down.
PocketSense would still consolidate the OFX files I think, but I don't think the scrubbers get run on the import folder files.
Regarding a csv converter integrated w/ the existing ofx scripts, I don't currently have such plans. I did write a simple conversion script for personal use last year, but it's limited to banks & credit cards and doesn't currently have a "configuration interface". More precisely, I wrote & use it only for Discover.
More broadly, I see "csv to ofx" as a different animal than "fetching statements via ofx services". It's easier than ofx in many ways for banks/credit but seems tedious for investments, since there's no "standard" for how a firm may report transactions and/or positions. The F2O mentioned above sounds like Fidelity is providing a normalized interface to work with, but I'm not a Fidelity customer so haven't tested.
That's a long-winded answer vs a simple "no", but my thoughts at the moment :|
Is anyone having a problem with downloading transactions from Capital One credit card? I've never been able to use Pocketsense with this account but until today I have been able to manually download them and import the OFX file into Money. Now I'm getting a message from Money about an Invalid format. When I look at the file with Notepad I see this strange sequence of messages:-
I am glad they fixed it. It was clear from that nullnullnullnullnull that the OFX file was not right.
FYI, if you put < symbols into posts, the blogspot software thinks those are the start of tags, and removes them up to the following > symbol.
If you replace all < symbols with these 4 characters < then it should post properly. I think it is not necessary to replace > symbols with > as long as this sentence makes sense (it says "replace > symbols).
I couldn't reply to your original post as comments there are no longer permitted.
I wanted to thank you for your work on porting Pocketsense the Python 3. https://github.com/magnuspyke/ofxscripts
Due to Microsoft ending support for Win 10, I figured that I'd give Linux Mint a try.
After a few newbie failures, I was able to get Pocketsense working as expected in Mint. Failures = not running Setup.py in Terminal, typing Python3 instead of python3 (cAsE sEnSiTiVe) and typing setup.py instead of Setup.py (cAsE sEnSiTiVe)
I know that I won't be able to run MS Money there, but I can and do run Moneydance there.
By any chance, do you have a large number of securities that you are trying to update quotes on?
It is my understanding that Yahoo has imposed a limit on the number of quotes that can be obtained in a given session using PFM software and / or scripts e.g. Pocketsense.
There apparently is a lock-out period when you exceed the limit, but I don't know how long the lock-out duration is.
If you do have a large number of securities, you may need to limit the number of securities that you update in a each session.
Not sure. A few have reported a similar issue which was traced to it being blocked by a DNS filtering agent (e.g., router, antivirus or DNS host). To verify... you're running the latest version?
EnableYahooFinance: Yes # Enable quote lookup on Yahoo Finance YahooTimeZone: -5:EST # Timezone rule for the Yahoo quote server (default = -5:EST). ShowQuoteHTM: No # 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*
My pihole DNS has blocking turned off and it has been working without issue until recently. Are the results of my "nslookup query1.finance.yahoo.com" different than what you are seeing? Can you send me what you get when you perform an nslookup?
Looking at the sites.dat info above, it doesn't look like you're using the latest scripts (?). The older version(s) started failing w/ yahoo finance. By default, the YahooURL option is disabled in the latest and uses a different format. I'd suggest simply installing the latest and copying your quote symbols into the new sites.dat.
Tim: Fidelity, Vanguard, Janus, and I suspect others bring in quotes when PocketSense fetches the QFX/OFX files. Those are based on the previous close, so some adjustment may be needed to get the right date recorded.
Also, MSMoneyQuotes https://gaiersoftware.com/Money/ downloads quotes, and unlike other methods, will bring in intraday quotes that can be superseded.
Not sure if I am the only one with the following error:
requests.exceptions.ConnectionError: HTTPSConnectionPool(host='fc.yahoo.com', port=443): Max retries exceeded with url: / (Caused by NewConnectionError(': Failed to establish a new connection: [Errno 11001] getaddrinfo failed',))
But it is crashing python whenever I try to get Quotes.
Hi Ray, You may have bumped up against the limit that Yahoo has placed on the number of securities that you can quote in a given instance. I mentioned it here to another PS user. https://pocketsense.blogspot.com/2024/01/comments.html?showComment=1718756137751#c2695800591031072756
I don't think its the same. I have less than 20 quotes per session and never get a response from yahoo. The error message is from requests in python about a connection failure and it is not handled properly so the function fails and crashes.
If you can get quotes from yahoo than I will look elsewhere for my problem. It seems to not reach fc.yahoo.com to get the crumb it needs but does not handle the failure gracefully.
To verify... you're running the latest version and don't have a custom YahooURL defined/enabled in sites.dat? A few have reported a similar issue which was traced to it being blocked by a DNS filtering agent (e.g., router, antivirus or DNS host). One of the culprits for blocking the yahoo site has been a local instance of pi-hole .
Thanks for the hint Robert. I by-passed the router and was able to get quotes. This led me to change the "Diversion" program in the router by adding "fc.yahoo.com" to the allow/whitelist. I should have known better! Thanks again for the help. Ray
Did we lose American Express access? Amex seemed to transmit a null file when I used Pocketsense. When I went to the Amex site and downloaded manually specifying "Quicken", and looked at the file, it seemed to have sent me only my balance. When I specified Quickbooks, I got a file with a QBO suffix, which seemed to contain transactions. When I fed that file into Money, it didn't object, but since I didn't have any new transactions, I couldn't tell whether it was really processing transactions at all.
I ran into the same issue last night, and the problem persists tonight. At this time I guess it is unknown if this is intentional or a temporary disruption. I attempted to set up a new connection through Pocketsense and the amex server actively refused my username/password combo (which is not a good sign)... I guess we'll have to give it a few days. If the website generator is having issues too, then hopefully this will get fixed in the near future, but I am not particularly optimistic at this point...
I believe that qbo (quickbooks) used a very similar format to qfx, so I'd guess it will work fine for basic transactions. The import routine doesn't use the extension, it looks at the files and if it "looks like" ofx... attempts to import.
I am actually amazed at the hypocrisy inherent in cutting off direct access. In a time where scams are plentiful, making it harder to easily get access to your account information sounds self-serving. Just saying....
You might try getting a *.qbo file to import into Money by default by the method described in https://microsoftmoneyoffline.wordpress.com/reg-file-to-import-quicken-qfx-files-automatically-into-money/ -- but change occurrences of qfx into qbo
Opening the Amex download URL (https://online.americanexpress.com/myca/ofxdl/desktop/desktopDownload.do?request_type=nl_ofxdownload), I got the following msg "We're sorry. We are momentarily experiencing technical difficulties. We are working on bringing our services back online." So finger crossed this is just a temporary outage.
Looks like Amex changed something on their end, I see this notice when manually downloading qfx:
--- Instructions for Intuit Customers
We recently made system updates to our connection to Intuit's Quickbooks and Quicken software. To resume .QFX and .QBO downloads, please complete a Quickbooks and/or Quicken account deactivation and reactivation process.
Please review the following instructions to complete the process, or contact Intuit Customer Support at Quicken.com/support or Quickbooks.com/support for help.
You can also use Quicken Direct Connect.
If you're a Small Business Card Member and have Quickbooks Online, you can enroll in Connect to Quickbooks. Your Business Card transactions will then be automatically synced with Quickbooks daily. ---
Same. Not only that, but the links are to articles on the Quicken/Quickbooks web site (the Quickbooks links work), so they're about connecting through their software, which wouldn't seem to help us.
I have not been able to reconnect. I consider it just another example of the degradation in customer service at American Express during the last while. After all, their logon experience seems like it was designed to be an awful user experience. At this point, I don't see them as being responsive at all.
Ughh ughh ughh. I think AmexEx is dead based on what I am reading in other groups also. I moved everything to them when discover stopped OFX just a few years ago. I am running out of options! Citi Card and that is it now. It does work to log in and manually download and import, but what a pain with multiple cards. I love MicrosoftMoney/pocketsense, been using it since 1999. What can be done? Can something like Plaid be integrated (I know it would cost money and I know lots of people worry about privacy with it)? I've looked at other software but none can compare with the UI and ease of use of MSMoney. If Fidelity goes I'm hosed as I have so many account with them I could not imagine manually doing it all. Ughhh ughh ughh!!!
Personally... I find that downloading an ofx or csv file and importing via getdata is very easy. I have all the passwords, etc. in a password manager and can do it w/ one hand while sipping coffee w/ the other :) While you could download each ofx and load directly to Money, that bypasses the scrub routines, which leads to issues w/ some banks. There's no direct csv import to Money, so I wrote a script that handles Discover nicely, and could be pretty easily applied to others. I haven't gone the path of implementing a general use csv import feature since a) it's not related to ofx connections, and b) not sure how many people would even use it. It's a fair bit of work to implement for general use, whereas not too bad for "just me" (meaning all scripted w/ minimal "per user config".
All that to say, that the future of using Money will be downloading files and importing... but the getdata import feature makes it very easy (imho).
Yes getdata works great, if the bank is online and allows OFX. But discover/chase/amex (and a few others) now no longer have that option so I can't use it. Works for Citi, Fidelity, and Vanguard (and stock quotes) like a charm. Maybe I'm spoiled from being able to get like 10 banks in 5 seconds in its hayday, but logging into each of these web sites to download a file then importing into money every day really does add up for time. If I download the quicken file I can go to the file menu and do import and that works. How do I run stuff through the getdata routine if it's not going out to a website that I setup in sites.dat?
There's an "import" folder where the scripts reside. For accounts that need manual download, I first download them to the import subfolder and then run getdata. Any file found in the import folder that "looks like ofx" gets aggregated into the single import file along with whatever came via direct ofx (assuming CombineOFX=Yes in sites.dat). That allows manual downloads to be processed for issues (scrubbed) and generates a single import for everything. I only do it ~ weekly though...
Robert, I don't suppose you would be willing to share your Discover scrubber, would you? I was about to embark on writing it, but figured why invent the wheel.
Sorry for the late reply. I wrote a csv-2-ofx script w/ Discover stopped ofx downloads, but did so in a way that I thought could be applied to any bank/credit-card csv download. I feel that sharing will require that I spend some time unpacking it a bit though... meaning, writing a description of how it works and how to use it. There's no "configuration interface" or "settings", but I did package it such that a separate "run.py" script is managed separate from the csv2ofx.py script and defines maps/options for a specific user/instance and accounts. Probably fine for a programmer type but otherwise maybe not. If there's interest, I can try to find time to share along w/ a brief summary of how to use it... but it's not something I intended to do, since it was written *quick* and I haven't really tested beyond my own need.
For interested parties Ameridan has a list of workarounds that could be of interest to people in this situation. Personally I use Bank of America's aggregator for bank and credit card transactions and then feed that though Pocketsense using the import folder Robert talks about above. I just added Amex to that routine and i am back up and running. Check it out here if interested: https://microsoftmoneyoffline.wordpress.com/2024/01/19/using-boas-aggregator/
Ok did not know about the import folder option. Since Amex is already in a format it can process is there an easy tool (selenium?) that you know of that can quickly script logging in and downloading the file without the need for the CSV conversion Ameridan did?
Agree. If it takes a selenium script to download ofx from BOA, it should be possible to do the same with Amex/Chase directly. I don't see why we need the BOA aggregator.
You don't, but 1) many banks don't provide ofx-formatted downloads on their websites, 2) if you deal with many banks, that can be a lot of sites to visit (and selenium often fails mid-script), and 3) your average msft money user is probably not going to venture into programming against selenium on their own.
If you only have one or two banks to download from and you know your way around selenium, then I fully agree with you.
I recently got an Ally Bank money market account and I've had a hard time importing the downloads to money. I was wondering if someone can help. Apparently my problem is with checks. If I delete the below from the qfx file, it imports just fine.
Anyone having yahoo finance connection issues should: 1. Verify you're using the latest scripts 2. Check that any type of antivirus/firewall/etc isn't blocking fc.yahoo.com. This could be on the computer itself, a router running some type of "protection", browser add-in, etc.
Since this morning (11/19/2024), I have similar issues with Yahoo finance. All return "invalid quote responses." 2023-10-31 09:30:56,506 - INFO - Getting security and fund quotes 2023-10-31 09:30:56,506 - INFO - Getting quote for: ^GSPC 2023-10-31 09:30:57,384 - INFO - ** ^GSPC: invalid quote response. Skipping. No changes in config with OS/router/or other network security tools. Based on error, I don't think it's a connection issue.
Just tested and works fine here. Maybe try renaming or deleting cookies.dat, in case it's not auto-refreshing after expiration? If so, it should grab a new one.
Anyone having issues with the BOA selenium workaround? It seems to be bombing out when trying to download the ExportData.csv file (the 2nd file that gets downloaded). I'm trying to figure out if maybe the element names on the page have changed or something else. Just curious if anyone else is having problems. The only issue I've had up until now was keeping Chrome from automatically updating so that it didn't kill the process completely.
If you are referring to the BoAMFP scripts, that is not part of Pocketsense whatsoever. Send an email to the support email referenced in the documentation and I'll do my best to help out.
Yes. I last got a good OFX file 12/25/2024 08:32 AM, and my next try at 12/27/2024 06:21 AM got a zero-byte file. I tried testing with setup.py. I got this error message:
Test VANGUARD | 1234567 (Y/N)? y VANGUARD : 1234567 : Getting records since: 20241128 ** An ERROR occurred sending POST request to https://vesnc.vanguard.com/us/OfxDirectConnectServlet Exception type : Exception val : 'ascii' codec can't encode character u'\ufffd' in position 3015: ordinal not in range(128) An online error occurred while testing the new account.
Same here. Looking around, it seems that the old service may no longer be available. However, there does appear to be a new one (settings below). If I test w/ new url, it connects but gives a message to "contact them to enable access". I guess I'll try "contacting them" during the week, as I don't see anything obvious on their site. For now, I downloaded the qfx file from their portal ~ easily via their "download center".
p.s. more specifically, grabbed the "combined" qfx file from the download center w/ all accounts in one file and dropped into the import folder before running getdata. Worked fine.
Looking a bit further in their "help" section, "direct connect" doesn't seem promising. For "quicken support", they only show downloading via their download center. For myself, that's fine since I only have transactions ~ once a month and can update prices via quotes if I want to track more frequently.
Robert, I tried with your new settings, and got the message "Please contact the financial institution for the enrollment Process." Have you contacted Vanguard? I'd like to know if enrollment really is possible, and what magic words I should say so that the agent knows what I am talking about.
I have not. The thought of waiting on a phone to talk to someone about "something out of the ordinary" is worse to me than logging into my account and downloading an aggregate ofx statement once a month :|
I feel the same way. Maybe I'll try sending them an email about it. But I don't really understand what your script is doing if they have questions. I've just happily used it for many years.
I finally got a response from Vanguard, but it looks like a pre-written answer telling me to deactivate/reactivate my Vanguard account in Quicken or contact Quicken support. I can't tell if they understood my question or even read it.
Thank you for posting back David. I'm personally just going to do the aggregate statement download as needed, and use quotes to fill in. I don't really care about frequent market changes, so updating quotes even weekly is overkill. Vanguard was the *last* institution I had that still supported direct ofx, so it's now all manual downloads here.
Hi Robert, you mentioned manually downloading an aggregate OFX statement from Vanguard. I tried to do when PocketSense stopped being able to download my Vanguard statements a few weeks ago, but I only found a way to download QFX or CSV files on Vanguard's site. Unfortunately, neither of those works with Microsoft Money.
Could you point me in the direction of the manual OFX downloads from Vanguard? Thanks!
I didn't read the link above... but my workflow is to download statements to the import folder of the PS scripts (multiple institutions at this point). I don't rename qfx or anything like that... just save them to the import folder. I then run getdata and let the scripts parse/scrub and combine into one import... so a single "accept" in Money after importing. Most "downloads" are qfx, but the script ignores file names and instead peeks at the content to decide if it "looks like" ofx.
FYI integrating vanguard into my Selenium (using c# library, but there are others if you do not use c#) software that I run before the getdata script worked great. So right now I am at Amex (2 accounts), my local bank and Vanguard running first with Selenium to get the statements and then Fidelity, Etrade, Citi card, and stock quotes direct. Sad another one has gone away from direct but glad I can use other tools to download and then just place in the import folder and it gets processed fine.
So I just ran PocketSense again tonight and it appears that my automatic Vanguard OFX downloads are working again. Have they started working for others, too?
Regardless, thanks for the tips regarding renaming QFX files to OFX files and the various script/registry hacks to automate it. It will be good to know if Vanguard's automatic downloads stop working again.
I have a question need help. Few years ago, I added "SPY" as mutual fund instead of index (bad mistake). When I realize it recently, I tried to change it from "mutual fund" to "index". I was unsuccessful, end up taking openai advice to rename the "mutual fund" - SPY to "SPY_temp" and edit/update all transactions to new ticker "SPY" - index. After all that, the historical price is no longer there, and I end up having to combine both together. Now, the biggest headache I got is MS Money won't update SPY ticker daily price. Everything else works. Any suggestion on how to fix this issue?
Note: I did have a backup file (2 days old data) without combining (SPY_temp and SPY). That version will take the SPY ticker update from python code.
Blogger Ron said...
ReplyDeleteCal (or Robert or other knowledgeable Python programmer)
Taking your and NxTk's advice, I wrote a scrubber that changes the last digit of the FITID to a "1".
It seems to work and be restricted to only my savings account at TD Bank.
However, this is the first time I've programmed in Python. Would you mind taking a look and seeing if I did something that might cause a problem down the road?
```
#TD Bank: Remove last digit of FITID from Savings Transactions
#29 Jan 2024
from control2 import *
from scrubber import scrubPrint
import re
def scrub(ofx, siteURL='', accType=''):
if 'eftxweb/td.ofx' in siteURL.lower() and accType=='BASTMT':
global stat
stat=False
#Check that we are dealing with the correct account
if re.search("ACCTID>##############", ofx):
scrubPrint('TD Bank Matched Savings Account')
#We are, so change the last digit to a "1"
ofx = re.sub(r"(?PFITID>\d{19})\d", r"\g1", ofx)
scrubPrint('Last FITID Digit changed to "1"')
return ofx
```
I like it! These scrubbers are nice to save as a library, even if you don't have an account, for when an occasion arises.
ReplyDeleteI don't follow
ReplyDeleteofx = re.sub(r"(?PFITID>\d{19})\d", r"\g1", ofx)
I don't know r"\g1". I am not familiar with a \g metacharacter.
And whatever you had in your code, I suspect you has a > character that you did not replace with these four characters: >
Does your code work, or have you not tested it yet? The string I posted in post 200 of http://pocketsense.blogspot.com/2023/07/new-version-available.html should work.
Also, have you checked that all non-duplicated FITIDs end in a "1"? That still might work if the rest of the characters are unique.
Cal Learner.
Cal,
DeleteYes, your correct about those missing brackets.
And yes, I tried the code and it does seem to work
let me repost the regex
re.sub(r"(?P<FIT>FITID>\d{19})\d", r"\g<FIT>1", ofx)
The match string is using a Named matching group (FIT).
In the replace string the \g says to use everything that was matched by FIT.
For numbered matching groups, one could use \g1, but if one wants to add a 1, the construct becomes \g11 which is confusing to the regex engine because it starts looking for group 11. So I used a named group to avoid that problem.
Does seem to work.
In the savings account, there have been NO non-unique entries.
In the checking account, (which this scrubber does not process as I check for the account number before running it), the last digit seems to be anything. I don't have enough data to be sure that the first 19 digits will always uniquely identify any transaction.
For the savings account, that should read there have been NO unique entries.
DeleteAnd also, at least so far, those non-unique entries have all had the first 19 digits the same, and they have all started with `1`. But I only have a database of two entries -- one deposit transaction and one interest transaction. That'll be a good thing to look for going forward. And should be easy to detect if there is an anomaly. Thanks for pointing that out.
Deletedammit... I meant to write And whatever you had in your code, I suspect you has a < character that you did not replace with these four characters: <
ReplyDeleteIf you want to know how to use pdb to test little code snippets with pdb, let us know. But first tell us that you are able to copy and paste from/to a command window. You can Google how to do that that.
In an earlier post I showed how I tested my string in pdb. The "(Pdb)" is a prompt from pdb -- Python debugger.
Cal Learner.
This comment has been removed by the author.
ReplyDeleteWell that scrubber messes up all of my other downloads, so I guess the tdbank filter isn't kosher.
ReplyDeleteNxtTek: Looking at the code as posted here more closely, it seems it did not post as it really exists. It shouldn't run at all unless it matches the bank and account number (and it doesn't here). However, the code as posted is missing indents under both the first and the second nested IF statement. I wonder if that could be your problem. I'll see if I can get it to post correctly.
DeleteNxtTek: I couldn't figure out how to post it here properly formatted. Here's a link to a text file containing the code which should be properly formatted: https://1drv.ms/t/s!Aml6o6HIc9uOy28nDi-NrRdzURAI?e=tuaOMJ
DeleteThanks Ron. that works.
DeleteIt's been mentioned before, but it does look like it is only a matter of time before Fidelity access closes. I found the following text on their web site:
ReplyDelete"To enhance the protection of your account data, Fidelity has established a secure connection that better controls how third-party websites and apps that you've authorized, and the data aggregators they use, connect to your accounts. Fidelity is requiring these data aggregators to transition to this secure connection. Fidelity users of some third-party websites and apps may experience a disruption in the link between those websites and apps and their Fidelity accounts. "
I'm going to be optimistic, thinking that's in reference to web scraping ;)
DeleteFidelity saved the day from Schwab killing their ofx server, but I don't think there are any more saviors out there, if Fidelity does the same.
- ameridan
The current situation lets your program (PocketSense in our case) access your transactions and positions directly without involving Yodlee, Quicken etc handling your data. Their announced intention is to require you to go via a third party that they work with to then provide the data to your computer or to a cloud app.
DeleteSee the official response in https://www.reddit.com/r/fidelityinvestments/comments/17tpgfr/why_doesnt_fidelity_support_ofx_downloads_anymore/?rdt=47495
So it would be more precise and robust than screen scraping, but would still require that your info go to a third party. It would be safer in that the third party would not have your Fidelity credentials. It also would provide an income stream to Fidelity. I don't begrudge them that, but I like to minimize the sharing of my financial data.
Thus the urgency to be able to generate OFX files, that can feed into PocketSense, from the history and positions CSV files that you can download from the Fidelity website. This is extra work over how PocketSense now works with Fidelity, but worth it IMO. I don't know if Vanguard will continue to offer OFX directly,
Cal Learner
A free Fidelity to OFX converter from Cal Learner (fidcsv3 or F2O name subject to change):
ReplyDeleteAmeridan's blog nicely documents where this program/project stands and explains the methodology.
See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/
While the current main program is named fidcsv3.py, it will probably be renamed to something like F2O. py or f20.py at some point. Since this runs on windows, the filename will not be case-sensitive.
Advantages:
o open source
o developed on Python 2.7 as PocketSense uses
free
o can be used with PocketSense or without (I do recommend PocketSense)
o puts both transactions and positions and price info into the OFX files, just as with the OFX files that currently come in from Fidelity via PocketSense
o still being improved, so any bugs bug reports or suggestions posted here will be reviewed and probably acted upon
o the program finds your latest Fidelity positions and history CSV files in your Downloads folder without further interaction needed.
Disadvantages:
o not professionally coded.
o lots of global variables,
o not modular.
o the coding style evolved since being started March 2022, so method of doing the same thing is not constant thru the program(s)
o the code is not already openly published, but that will change if there is any interest.
human interface not polished, but will probably improve
o user must download the positions and history CSV files from the website for each run, but the program does find those in your Downloads folder without further interaction needed. Download from web is easy enough.
Questions and comments are welcome. I expect interest to pick up after the Fidelity OFX server disappears. But by that time, it would be too late for some things that you would miss out on that would would have made the operation initially easier.
Cal Learner
This is something that would be of interest to me. If I am willing to help if you need some.
DeleteJoe Quaranta
This comment has been removed by the author.
DeleteI have decided to open the Fidelity CSV to OFX (F2O) beta to all now. I
Deletehave a folder that should be accessible given the link. This is a folder
rather than a file:
https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing
In the folder is f2o_beta3.zip which has several files. One file is
F2Obeta.doc
in Word 97 format, and F2Obeta.txt which is is the text-only version.
Feel free to post comments, bugs, or why did you do something the way
you did. The code evolved starting in March 2023.
This comment has been removed by the author.
ReplyDeleteThat worked. I have downloaded the zip file and extracted it but I didn't find any python files and the .doc file seems to be a binary file of some sort???
DeleteFirst, yes I did miss important files. The .doc file did work for me when I just tested. It is intended to be opened by Microsoft Word or other program that can read Word .doc files. I have added a *.txt version too.
DeleteNew method. This is a folder rather than a file:
https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing
I will delete the post with the old link. The new method should mean that I don't have to create a new link to change content.
It contains f2o_beta2p.zip and a simple txt file that does nothing.
That worked. I'll try out the F2O_DBgather.py program along with the bat files. The doc file will open in ms word. On my system the .doc extension automatically opens in a text editor. Thanks
DeleteThe F2O_DBgather.py program worked on my data except for one ofx file (one generated from a cash management account which is just a checking account from Fidelity). It errored because the split_ofx list has only 1 entry in it. ( the ofx does not have any since it only contains a cash entry). Since the file F2O.py was not in the files shared with me, I can't proceed any further.
DeleteI will need to deal with that. Currently I have all of my cash in my cash management account used to purchase SPRXX. I should sell some of my SPRXX to generate an entry for the core position for testing.
DeleteI had intended for that F2O.py file to be there. It is there now with the same zip filename, but a little bigger.
Both programs (F20.py and F20_DBGather.py) worked well with my data other than the cash management account mentioned previously. Thanks, I think these will be useful in the future.
Deletedealt with it. f2o_beta3p.zip contains a fixed version of F2O_DBgather.py
ReplyDeleteIt also has a copy of F2O_DBgather.bat.bk1 which is my zip file, but is intended to be modified for your file structure. It is not called F2O_DBgather.bat in the zip file to avoid accidentally overwriting a batch file you already modified.
Besides the bug fix, F2O_DBgather.py accepts an optional second parameter (integer) that would set the DEBUG level. Default is zero.
I expect to open the beta pretty soon.
I have installed Anaconda, downloaded the scripts, and not having any luck getting started. When I double-click Setup.py, nothing happens. Next, I ran the Anaconda Prompt and typed in Setup.py. This gave me a couple of errors, one on import urllib2 and another on pyDes.py (print 'Invalid Password' - SyntaxError: missing parentheses). Any suggestions on how to get started? Thank you.
ReplyDeleteI am not familiar with Anaconda. I would start with getting the version. If you launch Python with the --version switch, it should give the version.
DeleteIf you launch python so that you get the Anaconda Prompt, I expect the sign-on message included the version.
If the version works out ( maybe 2.7.10 to 2.7.18), then I guess you may want to get a different distribution.
http://pocketsense.blogspot.com/2021/02/dev-testing.html does mention Anaconda, but it also includes a link for ActiveState.
Cal Learner
Has anybody else lost ofx server access to citi? My last successful Pocketsense call seems to have been Feb 9th....
ReplyDeleteCiti credit card worked fine this morning, but I don't have any bank accts with them.
DeleteSame here. Citicard OFX via PocketSense worked this morning. It has been nicely reliable for me.
DeleteThis is my sites.dat citicard descriptor:
<site>
SiteName : CitiCard
AcctType : CCSTMT #credit card
fiorg : Citigroup
fid : 24909
url : https://mobilesoa.citi.com/CitiOFXInterface
bankid :
brokerid :
ofxVer : 103
appid :
appver :
mininterval:
timeOffset :
</site>
In setup.py, doing the "2. List Accounts" choice, I do have ConnectKey: listed for each of two accounts. I did not do anything to set up that. When I do the rare move of my PocketSense to another folder, I copy my connect.key and ofx_config.cfg files over to the new folder.
While I have a Citi bank account, I don't think they provide an OFX server for that. I use their web interface to get a qfx file. I do that very rarely because it is a bit tedious. I make sure the dates I download do not overlap with the transactions in Money because they don't/didn't have the thing about having the FITID the same each time working.
Cal Learner
Thank you gents. My sites.dat setup matches yours Cal (and yes, i should have specified that this is indeed for a credit card which has worked beautifully up until now). It appears to be a credential issue, however. I do get a response from the ofx server with the following:
DeleteERROR
Information does not match with our record. Please make sure you select the correct Financial Institution and enter correct username/password.
I decided to nuke the connection and set it up again...and yeah, it chokes on my credentials (the same ones that work perfectly when logging in on the citi website). I am a bit stumped on this one if others are not seeing the same... oh well... I do have other ways of getting my citi transactions so it is not a big deal. But this means my pocketsense direct connections are down to just Fidelity and Amex... :S
Unless something has changed, I believe you need to enable direc access on the Citi site. Not at computer now, and don’t recall the details, but you add a new connection from an account menu and *then* add the account in PS during the ~ 10 min window. -Robert
DeleteThanks Robert; I suspected that may be the case, but I looked high and low on the citi website and could not find such a feature... No rush, but if you do find that menu option on your end please do report back. i did find some references to Quicken, but that is all in context of Web connect which of course is different.
DeleteOooo... Now that you say that, I kinda remember that discussion.
DeleteIn http://pocketsense.blogspot.com/2019/08/comments.html see "After making the sites.dat changes, I did not need to use the Setup.py script, but I did need to use the Citi 10-minute process to relink the PocketSense scripts to my Citi account. The easiest way to do that is to log in to https://citi.com/datamanagement. "
I cannot log into the website at the moment.
Cal Learner
Thank you Cal; that did the trick and I am reconnected through Pocketsense now. Maybe my authorization (prior one evidently from 2022) expired, so i wouldn't be surprised if others find that the connection stops working at some point. How one can find that critical page without knowing the direct link is still a mystery to me. Will make sure to save the link for posterity. Thanks again!
Deletedjevel, go to https://online.citi.com/US/ag/ofxConsentPage and authorize "Quicken Windows".
ReplyDelete-ameridan
Thanks Ameridan; I can confirm that your link goes to the same place as Cal's!
DeleteThe actual procedure has been on my settings page all along (for posterity) @ https://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/
ReplyDeleteAnyone use the PocketSense scripts to download Regions Bank account data? I've used them for years without issue. My downloads stopped working this past weekend and are still not working. It would appear to me that Regions as shut down their OFX server much like USAA did earlier this year. Just trying to verify that that's the case.
ReplyDeleteHi John,
DeleteHere in Georgia, using the PS scripts, Regions Bank is working as expected with both BASTMT and CCSTMT type accounts.
In sites.dat I set userAgent: none
Also delay: 3
I set the delay because I was getting bounced out midway through the downloads.
HTH -Kevin N.
I started getting an internal server error this past week. I don't receive an OFX response back when I try to download transactions or when I try to set up a new account. It's really strange.
DeleteHi John,
DeleteCheck to make sure your Profile at Regions has not changed.
Log on to Regions. Hover over 'Customer Service', select 'Settings'.
Click the 'Manage Profiles' link. Here, make sure the options for 'Online' and 'Quickbooks' are checked.
Click 'Save'.
Kevin - None of my profile info has changed. I tried doing a completely clean install on another machine to see if I could run the setup script to download just my Regions accounts. The script fails on that machine as well. I suppose Regions could have blacklisted my IP address as one of my OFX account requests for a new account that was setup in January was routinely failing. If they had done that though, I shouldn't be able to log into regions.com either.
DeleteKevin - Adding userAgent as "None" and adding a delay of 3 seconds seems to have resolved the issue.
DeleteI'm glad to hear that you were able to get this working again.
DeleteThank you for reporting back with your results.
I added kevin's recommended changes to my settings page. thx
ReplyDeletehttps://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/
Hi Dan,
DeleteThank you for maintaining your site. It is a trove of valuable information.
Just to clarify, my suggested 'delay' setting for Regions Bank and Regions Credit Card is 3 seconds. It was inadvertently entered as 30 seconds on your site.
Robert, any plans to publish a csv-2-ofx converter for PocketSense fans?
ReplyDeleteF2O, a Fidelity-specific CSV to OFX converter project is actively in progress. See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/
DeleteThe user could select to have the generated Fid*.ofx files go to the PocketSense import folder, but that would best not be done until the Fidelity OFX server shuts down.
PocketSense would still consolidate the OFX files I think, but I don't think the scrubbers get run on the import folder files.
Cal Learner.
Regarding a csv converter integrated w/ the existing ofx scripts, I don't currently have such plans. I did write a simple conversion script for personal use last year, but it's limited to banks & credit cards and doesn't currently have a "configuration interface". More precisely, I wrote & use it only for Discover.
DeleteMore broadly, I see "csv to ofx" as a different animal than "fetching statements via ofx services". It's easier than ofx in many ways for banks/credit but seems tedious for investments, since there's no "standard" for how a firm may report transactions and/or positions. The F2O mentioned above sounds like Fidelity is providing a normalized interface to work with, but I'm not a Fidelity customer so haven't tested.
That's a long-winded answer vs a simple "no", but my thoughts at the moment :|
Is anyone having a problem with downloading transactions from Capital One credit card? I've never been able to use Pocketsense with this account but until today I have been able to manually download them and import the OFX file into Money. Now I'm getting a message from Money about an Invalid format. When I look at the file with Notepad I see this strange sequence of messages:-
ReplyDelete20240404000000.000
20240411000000.000
nullnullnullnullnull
I'm wondring if Capital One is the latest financial institution preventing customers getting their own transaction data?
Sorry , when I copied that data in all the OFX parameters disappeared. I'll try again:-
Delete"BANKTRANLIST"
"DTSTART 20240404000000.000 /DTSTART"
"DTEND 20240411000000.000 /DTEND"
"nullnullnullnullnull /BANKTRANLIST"
Today the problem fixed itself or maybe Capital One did something.
DeleteI am glad they fixed it. It was clear from that nullnullnullnullnull that the OFX file was not right.
DeleteFYI, if you put < symbols into posts, the blogspot software thinks those are the start of tags, and removes them up to the following > symbol.
If you replace all < symbols with these 4 characters < then it should post properly. I think it is not necessary to replace > symbols with > as long as this sentence makes sense (it says "replace > symbols).
Cal Learner.
@Matt,
ReplyDeleteI couldn't reply to your original post as comments there are no longer permitted.
I wanted to thank you for your work on porting Pocketsense the Python 3.
https://github.com/magnuspyke/ofxscripts
Due to Microsoft ending support for Win 10, I figured that I'd give Linux Mint a try.
After a few newbie failures, I was able to get Pocketsense working as expected in Mint.
Failures = not running Setup.py in Terminal, typing Python3 instead of python3 (cAsE sEnSiTiVe) and typing setup.py instead of Setup.py (cAsE sEnSiTiVe)
I know that I won't be able to run MS Money there, but I can and do run Moneydance there.
Thanks again.
-Kevin N.
Hi there, I didn't know how to start a new post... This morning I'm getting "invalid quote response" for everything. Is anyone else having problems?
ReplyDeleteI was away yesterday... but tested just now and quotes worked as usual.
DeleteOops... wasn't signed in. Quotes working fine this morning.
DeleteHi Tim,
DeleteBy any chance, do you have a large number of securities that you are trying to update quotes on?
It is my understanding that Yahoo has imposed a limit on the number of quotes that can be obtained in a given session using PFM software and / or scripts e.g. Pocketsense.
There apparently is a lock-out period when you exceed the limit, but I don't know how long the lock-out duration is.
If you do have a large number of securities, you may need to limit the number of securities that you update in a each session.
-Kevin N.
GOOD CATCH!!!! I trimmed off a couple of old securities and that seems to have fixed the problem! Thanks Kevin!!!
DeleteHi Tim,
DeleteYou're welcome. I'm glad to hear that it worked.
Thank you for reporting back with your results.
-Kevin N.
I'm still seeing " invalid quote response. Skipping...", but nothing has changed on my side?
ReplyDeleteNot sure. A few have reported a similar issue which was traced to it being blocked by a DNS filtering agent (e.g., router, antivirus or DNS host). To verify... you're running the latest version?
DeleteTo be clear, I'm only using PS to get stock quotes from Yahoo.
ReplyDelete#--------------------------------------------------------------------------------
# Stock quote options
#--------------------------------------------------------------------------------
EnableYahooFinance: Yes # Enable quote lookup on Yahoo Finance
YahooTimeZone: -5:EST # Timezone rule for the Yahoo quote server (default = -5:EST).
ShowQuoteHTM: No # 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
Tim: To find what version of PocketSense files you are running, you can run
ReplyDeleteGetData.py
See the resulting sign-on message Ver:
Then ctrl+C will terminate.
This line should tell you if your DNS provides an IP, but I don't know if that would show if blocking by a DNS filtering agent.
nslookup query1.finance.yahoo.com
Cal Learner
Cal, thanks for the reply... I checked DNS, seems to be correct:
ReplyDeletenslookup query1.finance.yahoo.com
Server: pi.hole
Address: 192.168.1.2
Non-authoritative answer:
Name: edge.gycpi.b.yahoodns.net
Addresses: 2001:4998:28:800::4000
2001:4998:28:800::4001
209.73.190.11
209.73.190.12
Aliases: query1.finance.yahoo.com
Do you have a sample URL I can call to see if I can get a better response using curl or a browser? Thanks!
Take a look at this comment and the discussion prior.
DeleteSpecifically, this comment:
DeleteAnonymous --- November 6, 2023 at 12:11 PM
I was also seeing the same error messages and found it was due to the ad blocker (pi-hole) I was running.
My pihole DNS has blocking turned off and it has been working without issue until recently. Are the results of my "nslookup query1.finance.yahoo.com" different than what you are seeing? Can you send me what you get when you perform an nslookup?
ReplyDeleteThanks!
Tim
Looking at the sites.dat info above, it doesn't look like you're using the latest scripts (?). The older version(s) started failing w/ yahoo finance. By default, the YahooURL option is disabled in the latest and uses a different format. I'd suggest simply installing the latest and copying your quote symbols into the new sites.dat.
DeleteThanks!!! You said: "However I get my prices from when I download OFX, rather than the Yahoo"
ReplyDeleteIs there a way to get stock quotes without using Yahoo?
Thanks, I'll try a later version and come back to you! Appreciate your help here!
ReplyDeleteTim: Fidelity, Vanguard, Janus, and I suspect others bring in quotes when PocketSense fetches the QFX/OFX files. Those are based on the previous close, so some adjustment may be needed to get the right date recorded.
ReplyDeleteAlso, MSMoneyQuotes https://gaiersoftware.com/Money/ downloads quotes, and unlike other methods, will bring in intraday quotes that can be superseded.
Cal Learner.
Not sure if I am the only one with the following error:
ReplyDeleterequests.exceptions.ConnectionError: HTTPSConnectionPool(host='fc.yahoo.com', port=443): Max retries exceeded with url: / (Caused by NewConnectionError(': Failed to establish a new connection: [Errno 11001] getaddrinfo failed',))
But it is crashing python whenever I try to get Quotes.
This started to happen a week ago Friday.
Ray
Hi Ray,
DeleteYou may have bumped up against the limit that Yahoo has placed on the number of securities that you can quote in a given instance. I mentioned it here to another PS user.
https://pocketsense.blogspot.com/2024/01/comments.html?showComment=1718756137751#c2695800591031072756
-Kevin N.
I don't think its the same. I have less than 20 quotes per session and never get a response from yahoo. The error message is from requests in python about a connection failure and it is not handled properly so the function fails and crashes.
DeleteIf you can get quotes from yahoo than I will look elsewhere for my problem. It seems to not reach fc.yahoo.com to get the crumb it needs but does not handle the failure gracefully.
Thanks for the input.
Ray
To verify... you're running the latest version and don't have a custom YahooURL defined/enabled in sites.dat? A few have reported a similar issue which was traced to it being blocked by a DNS filtering agent (e.g., router, antivirus or DNS host). One of the culprits for blocking the yahoo site has been a local instance of pi-hole .
DeleteThanks for the hint Robert. I by-passed the router and was able to get quotes. This led me to change the "Diversion" program in the router by adding "fc.yahoo.com" to the allow/whitelist. I should have known better! Thanks again for the help.
DeleteRay
I was running into the same error message. I also added fc.yahoo.com to my pihole whitelist and it started working again.
DeleteDid we lose American Express access? Amex seemed to transmit a null file when I used Pocketsense. When I went to the Amex site and downloaded manually specifying "Quicken", and looked at the file, it seemed to have sent me only my balance. When I specified Quickbooks, I got a file with a QBO suffix, which seemed to contain transactions. When I fed that file into Money, it didn't object, but since I didn't have any new transactions, I couldn't tell whether it was really processing transactions at all.
ReplyDeleteAny thoughts?
I ran into the same issue last night, and the problem persists tonight. At this time I guess it is unknown if this is intentional or a temporary disruption. I attempted to set up a new connection through Pocketsense and the amex server actively refused my username/password combo (which is not a good sign)... I guess we'll have to give it a few days. If the website generator is having issues too, then hopefully this will get fixed in the near future, but I am not particularly optimistic at this point...
DeleteI believe that qbo (quickbooks) used a very similar format to qfx, so I'd guess it will work fine for basic transactions. The import routine doesn't use the extension, it looks at the files and if it "looks like" ofx... attempts to import.
DeleteYes, I noticed the null statement a few days ago. Hopefully, AMEX has not killed direct access.
DeleteThanks, I verified that the QBO file imports transactions into MS Money with no issues.
DeleteI am actually amazed at the hypocrisy inherent in cutting off direct access. In a time where scams are plentiful, making it harder to easily get access to your account information sounds self-serving. Just saying....
DeleteStill grabbing a null file as of today!
DeleteYou might try getting a *.qbo file to import into Money by default by the method described in https://microsoftmoneyoffline.wordpress.com/reg-file-to-import-quicken-qfx-files-automatically-into-money/ -- but change occurrences of qfx into qbo
DeleteCal Learner
Opening the Amex download URL (https://online.americanexpress.com/myca/ofxdl/desktop/desktopDownload.do?request_type=nl_ofxdownload), I got the following msg "We're sorry. We are momentarily experiencing technical difficulties. We are working on bringing our services back online." So finger crossed this is just a temporary outage.
DeleteLooks like Amex changed something on their end, I see this notice when manually downloading qfx:
Delete---
Instructions for Intuit Customers
We recently made system updates to our connection to Intuit's Quickbooks and Quicken software. To resume .QFX and .QBO downloads, please complete a Quickbooks and/or Quicken account deactivation and reactivation process.
Please review the following instructions to complete the process, or contact Intuit Customer Support at Quicken.com/support or Quickbooks.com/support for help.
You can also use Quicken Direct Connect.
If you're a Small Business Card Member and have Quickbooks Online, you can enroll in Connect to Quickbooks. Your Business Card transactions will then be automatically synced with Quickbooks daily.
---
Have you been able to reconnect it? When I click on the instructions link that I see on the AMEX site it's a broken link.
DeleteSame. Not only that, but the links are to articles on the Quicken/Quickbooks web site (the Quickbooks links work), so they're about connecting through their software, which wouldn't seem to help us.
DeleteI have not been able to reconnect. I consider it just another example of the degradation in customer service at American Express during the last while. After all, their logon experience seems like it was designed to be an awful user experience. At this point, I don't see them as being responsive at all.
DeleteUghh ughh ughh.
DeleteI think AmexEx is dead based on what I am reading in other groups also. I moved everything to them when discover stopped OFX just a few years ago. I am running out of options! Citi Card and that is it now. It does work to log in and manually download and import, but what a pain with multiple cards.
I love MicrosoftMoney/pocketsense, been using it since 1999. What can be done? Can something like Plaid be integrated (I know it would cost money and I know lots of people worry about privacy with it)? I've looked at other software but none can compare with the UI and ease of use of MSMoney. If Fidelity goes I'm hosed as I have so many account with them I could not imagine manually doing it all.
Ughhh ughh ughh!!!
Personally... I find that downloading an ofx or csv file and importing via getdata is very easy. I have all the passwords, etc. in a password manager and can do it w/ one hand while sipping coffee w/ the other :) While you could download each ofx and load directly to Money, that bypasses the scrub routines, which leads to issues w/ some banks. There's no direct csv import to Money, so I wrote a script that handles Discover nicely, and could be pretty easily applied to others. I haven't gone the path of implementing a general use csv import feature since a) it's not related to ofx connections, and b) not sure how many people would even use it. It's a fair bit of work to implement for general use, whereas not too bad for "just me" (meaning all scripted w/ minimal "per user config".
DeleteAll that to say, that the future of using Money will be downloading files and importing... but the getdata import feature makes it very easy (imho).
Yes getdata works great, if the bank is online and allows OFX. But discover/chase/amex (and a few others) now no longer have that option so I can't use it. Works for Citi, Fidelity, and Vanguard (and stock quotes) like a charm. Maybe I'm spoiled from being able to get like 10 banks in 5 seconds in its hayday, but logging into each of these web sites to download a file then importing into money every day really does add up for time. If I download the quicken file I can go to the file menu and do import and that works.
DeleteHow do I run stuff through the getdata routine if it's not going out to a website that I setup in sites.dat?
There's an "import" folder where the scripts reside. For accounts that need manual download, I first download them to the import subfolder and then run getdata. Any file found in the import folder that "looks like ofx" gets aggregated into the single import file along with whatever came via direct ofx (assuming CombineOFX=Yes in sites.dat). That allows manual downloads to be processed for issues (scrubbed) and generates a single import for everything. I only do it ~ weekly though...
DeleteRobert, I don't suppose you would be willing to share your Discover scrubber, would you? I was about to embark on writing it, but figured why invent the wheel.
Deletehumblepie, scrub_discover.py is in the most recent distribution zip file.
DeleteCal Learner
Hi Cal. Thanks, but I was given to understand Robert had written a CSV to OFX converter for Discover. That's what I was looking for.
DeleteRegarding Discover CSV... Ah, yes. I forgot
DeleteSee Robert March 30, 2024 on this thread, and also
http://pocketsense.blogspot.com/2021/08/dev-testing-continued.html
See the October 15, 2022 Robert posts.
Cal Learner
Sorry for the late reply. I wrote a csv-2-ofx script w/ Discover stopped ofx downloads, but did so in a way that I thought could be applied to any bank/credit-card csv download. I feel that sharing will require that I spend some time unpacking it a bit though... meaning, writing a description of how it works and how to use it. There's no "configuration interface" or "settings", but I did package it such that a separate "run.py" script is managed separate from the csv2ofx.py script and defines maps/options for a specific user/instance and accounts. Probably fine for a programmer type but otherwise maybe not. If there's interest, I can try to find time to share along w/ a brief summary of how to use it... but it's not something I intended to do, since it was written *quick* and I haven't really tested beyond my own need.
DeleteFor interested parties Ameridan has a list of workarounds that could be of interest to people in this situation. Personally I use Bank of America's aggregator for bank and credit card transactions and then feed that though Pocketsense using the import folder Robert talks about above. I just added Amex to that routine and i am back up and running. Check it out here if interested: https://microsoftmoneyoffline.wordpress.com/2024/01/19/using-boas-aggregator/
ReplyDeleteOk did not know about the import folder option. Since Amex is already in a format it can process is there an easy tool (selenium?) that you know of that can quickly script logging in and downloading the file without the need for the CSV conversion Ameridan did?
ReplyDeleteSelenium should be able to do it fairly easily (assuming you know how to program against it of course).
DeleteAgree. If it takes a selenium script to download ofx from BOA, it should be possible to do the same with Amex/Chase directly. I don't see why we need the BOA aggregator.
DeleteYou don't, but 1) many banks don't provide ofx-formatted downloads on their websites, 2) if you deal with many banks, that can be a lot of sites to visit (and selenium often fails mid-script), and 3) your average msft money user is probably not going to venture into programming against selenium on their own.
DeleteIf you only have one or two banks to download from and you know your way around selenium, then I fully agree with you.
I recently got an Ally Bank money market account and I've had a hard time importing the downloads to money. I was wondering if someone can help. Apparently my problem is with checks. If I delete the below from the qfx file, it imports just fine.
ReplyDeleteCHECK
20241009231139
-100.00
XXXXXXXX
Check Paid #1002
1002
""
Delete"CHECK"
"20241009231139"
"-100.00"
"XXXXXXXX"
"Check Paid #1002"
"1002"
""
Apparently the tags don't show up. They are listed below. The have both start and end tags on each line.
DeleteSTMTTRN
TRNTYPE
DTPOSTED
TRNAMT
FITID
NAME
CHECKNUM
/STMTTRN
If you want to post contents of an OFX file or sites.dat, note that you should replace any < character with these four characters: <
DeleteI have done that to post the following transaction... lets see if it works:
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20241009231139
<TRNAMT>-100.00
<FITID>202410090002
<CHECKNUM>1002
<NAME>Check
<MEMO># 1002
</STMTTRN>
I suspect you are missing the TRNTYPE
Cal Learner
The TRNTYPE is "CHECK". Changing it to "DEBIT" fixes the issue. I guess I'll need to modify the scrubber to automate the process.
Delete
DeleteExample from OFX 1.6 specification:
<STMTTRN> <!-- First statement transaction -->
<TRNTYPE>CHECK <!--Check -->
<DTPOSTED>19961004 <!-- Posted on Oct. 4, 1996 -->
<TRNAMT>-200.00 <!-- $200.00 -->
<FITID>00002 <!-- Unique ID -->
<CHECKNUM>1000 <!-- Check number -->
</STMTTRN> <!-- End statement transaction -->
Example from OFX 2.1.1 specification:
<STMTTRN> <!-- First statement transaction -->
<TRNTYPE>CHECK</TRNTYPE><!--Check -->
<DTPOSTED>20051004</DTPOSTED><!-- Posted on Oct. 4, 2005 -->
<TRNAMT>-200.00</TRNAMT><!-- $200.00 -->
<FITID>00002</FITID><!-- Unique ID -->
<CHECKNUM>1000</CHECKNUM><!-- Check number -->
</STMTTRN> <!-- End statement transaction -->
I also have OFX files with DEBIT:
<STMTTRN>
<TRNTYPE>CHECK
<DTPOSTED>20230310120000
<TRNAMT>-00000000000225.0000
<FITID>123456789
<CHECKNUM>0000001253
<NAME>Check Paid #0000001253
<MEMO>Check Paid #0000001253
<CURRENCY>
<CURRATE>1.00
<CURSYM>USD
</CURRENCY>
</STMTTRN>
So it is not the simple case that <TRNTYPE>DEBIT is OK and <TRNTYPE>CHECK is not
Each of these three was within a BANKTRANLIST.
Cal Learner
I'm getting {"finance":{"result":null,"error":{"code":"Unauthorized","description":"Invalid Crumb"}}} to all quotes. Help?
ReplyDeleteJust tested and works fine for here. If it persists, take a look at a related comment here.
DeleteQuotes appear to be working again this morning.
DeleteMe Too.
ReplyDeleteMe three
ReplyDeletehttps://query2.finance.yahoo.com/v10/finance/quoteSummary/ARKK?modules=price
ReplyDeleteThat works for you Robert? Sure doesn't work, for me!
Via the script... yes, ARKK returns yesterdays price:
DeleteGetting quote for: ARKK
ARKK: 55.84 11/13/2024 16:00:00 -1.71%
It won't work in a browser, however, since it needs the yf credential (crumb).
Anyone having yahoo finance connection issues should:
ReplyDelete1. Verify you're using the latest scripts
2. Check that any type of antivirus/firewall/etc isn't blocking fc.yahoo.com. This could be on the computer itself, a router running some type of "protection", browser add-in, etc.
Correction... browser add-in shouldn't matter. OS level / router / or "network service".
DeleteSince this morning (11/19/2024), I have similar issues with Yahoo finance. All return "invalid quote responses."
Delete2023-10-31 09:30:56,506 - INFO - Getting security and fund quotes
2023-10-31 09:30:56,506 - INFO - Getting quote for: ^GSPC
2023-10-31 09:30:57,384 - INFO - ** ^GSPC: invalid quote response. Skipping.
No changes in config with OS/router/or other network security tools. Based on error, I don't think it's a connection issue.
Tested just now and worked fine here. The only other thing I know to check is to verify that YahooURL is disabled in sites.dat (# commented out).
DeleteFrom my sites.dat:
Delete#YahooURL: https://query2.finance.yahoo.com/v6/finance/quoteSummary/{ticker}?modules=price
I checked again, and same error - invalid quote response.
Is E*Trade still working? It looks like the last time I used it was August 2024, but it didn't work today when I tried it.
ReplyDeleteI'm having the same issue as MSMoney user. Started today. Python files are dated 2023-11-01
ReplyDeleteSorry - looks like the 2023-07 distro. I guess I downloaded and extracted Nov '23
DeleteSame here I'm also receiving invalid quote responses. Have the same #YahooURL. Is that API server down again?
DeleteConfirming YahooURL is commented out in sites.dat. Issue persists today
DeleteAlso tried OOTB sites.template. Same result. Python version 2.7.18
DeleteJust tested and works fine here. Maybe try renaming or deleting cookies.dat, in case it's not auto-refreshing after expiration? If so, it should grab a new one.
DeleteHas not worked for 2 days. Deleted cookies.dat and now it is working. Thanks Robert.
ReplyDeleteThanks Robert! Deleting cookies.dat did fix the issue. I will make a note of this should the problem re-occur. Thank you!!!!
ReplyDeleteDeleting cookies.dat worked for me, too. Thanks again Robert! (coffee inbound)
ReplyDeleteDeleting cookies.dat worked for me too! Thank you :)
ReplyDeleteThank you. Just rename it by adding 1 to the end of the filename. I have been using ofx for 5+ years, thank you!!!
ReplyDeleteAnyone having issues with the BOA selenium workaround? It seems to be bombing out when trying to download the ExportData.csv file (the 2nd file that gets downloaded). I'm trying to figure out if maybe the element names on the page have changed or something else. Just curious if anyone else is having problems. The only issue I've had up until now was keeping Chrome from automatically updating so that it didn't kill the process completely.
ReplyDeleteIf you are referring to the BoAMFP scripts, that is not part of Pocketsense whatsoever. Send an email to the support email referenced in the documentation and I'll do my best to help out.
DeleteAnyone having issues with Vanguard OFX servers? Stopped getting updates as of about 4 days ago.
ReplyDeleteYes. I last got a good OFX file 12/25/2024 08:32 AM, and my next try at 12/27/2024 06:21 AM got a zero-byte file.
DeleteI tried testing with setup.py. I got this error message:
Test VANGUARD | 1234567 (Y/N)? y
VANGUARD : 1234567 : Getting records since: 20241128
** An ERROR occurred sending POST request to https://vesnc.vanguard.com/us/OfxDirectConnectServlet
Exception type :
Exception val : 'ascii' codec can't encode character u'\ufffd' in position 3015: ordinal not in range(128)
An online error occurred while testing the new account.
The 1234567 represents my account number.
Cal Learner
Same here. Looking around, it seems that the old service may no longer be available. However, there does appear to be a new one (settings below). If I test w/ new url, it connects but gives a message to "contact them to enable access". I guess I'll try "contacting them" during the week, as I don't see anything obvious on their site. For now, I downloaded the qfx file from their portal ~ easily via their "download center".
DeleteSiteName : VANGUARD
AcctType : INVSTMT #investment
fiorg : Vanguard
fid : 15103
url : https://vesnc.vanguard.com/us/OfxProfileServlet
bankid :
brokerid : vanguard.com
ofxVer :
appid : QWIN
appver : 2600
ofxVer : 103
p.s. more specifically, grabbed the "combined" qfx file from the download center w/ all accounts in one file and dropped into the import folder before running getdata. Worked fine.
DeleteLooking a bit further in their "help" section, "direct connect" doesn't seem promising. For "quicken support", they only show downloading via their download center. For myself, that's fine since I only have transactions ~ once a month and can update prices via quotes if I want to track more frequently.
DeleteRobert, I tried with your new settings, and got the message "Please contact the financial institution for the enrollment Process." Have you contacted Vanguard? I'd like to know if enrollment really is possible, and what magic words I should say so that the agent knows what I am talking about.
ReplyDeleteI have not. The thought of waiting on a phone to talk to someone about "something out of the ordinary" is worse to me than logging into my account and downloading an aggregate ofx statement once a month :|
DeleteI feel the same way. Maybe I'll try sending them an email about it. But I don't really understand what your script is doing if they have questions. I've just happily used it for many years.
DeleteIf you decide to try... simply ask if they can enable "Quicken direct connect" for your account.
DeleteI sent Vanguard a message about this over a week ago, and have received no response so far.
DeleteI finally got a response from Vanguard, but it looks like a pre-written answer telling me to deactivate/reactivate my Vanguard account in Quicken or contact Quicken support. I can't tell if they understood my question or even read it.
DeleteThank you for posting back David. I'm personally just going to do the aggregate statement download as needed, and use quotes to fill in. I don't really care about frequent market changes, so updating quotes even weekly is overkill. Vanguard was the *last* institution I had that still supported direct ofx, so it's now all manual downloads here.
DeleteHi Robert, you mentioned manually downloading an aggregate OFX statement from Vanguard. I tried to do when PocketSense stopped being able to download my Vanguard statements a few weeks ago, but I only found a way to download QFX or CSV files on Vanguard's site. Unfortunately, neither of those works with Microsoft Money.
ReplyDeleteCould you point me in the direction of the manual OFX downloads from Vanguard? Thanks!
Bryan: See https://microsoftmoneyoffline.wordpress.com/reg-file-to-import-quicken-qfx-files-automatically-into-money/ parts with green background.
ReplyDeleteCal Learner
Thanks!
DeleteI didn't read the link above... but my workflow is to download statements to the import folder of the PS scripts (multiple institutions at this point). I don't rename qfx or anything like that... just save them to the import folder. I then run getdata and let the scripts parse/scrub and combine into one import... so a single "accept" in Money after importing. Most "downloads" are qfx, but the script ignores file names and instead peeks at the content to decide if it "looks like" ofx.
DeleteFYI integrating vanguard into my Selenium (using c# library, but there are others if you do not use c#) software that I run before the getdata script worked great. So right now I am at Amex (2 accounts), my local bank and Vanguard running first with Selenium to get the statements and then Fidelity, Etrade, Citi card, and stock quotes direct. Sad another one has gone away from direct but glad I can use other tools to download and then just place in the import folder and it gets processed fine.
ReplyDeleteSo I just ran PocketSense again tonight and it appears that my automatic Vanguard OFX downloads are working again. Have they started working for others, too?
ReplyDeleteRegardless, thanks for the tips regarding renaming QFX files to OFX files and the various script/registry hacks to automate it. It will be good to know if Vanguard's automatic downloads stop working again.
Bryan: I just tested Vanguard, and YES!
ReplyDeleteCal Learner
Second confirmation, Vanguard is working again!
ReplyDeleteVanguard working again here. Thanks for the heads up!
ReplyDeleteI have a question need help. Few years ago, I added "SPY" as mutual fund instead of index (bad mistake). When I realize it recently, I tried to change it from "mutual fund" to "index". I was unsuccessful, end up taking openai advice to rename the "mutual fund" - SPY to "SPY_temp" and edit/update all transactions to new ticker "SPY" - index.
ReplyDeleteAfter all that, the historical price is no longer there, and I end up having to combine both together.
Now, the biggest headache I got is MS Money won't update SPY ticker daily price. Everything else works.
Any suggestion on how to fix this issue?
Note: I did have a backup file (2 days old data) without combining (SPY_temp and SPY). That version will take the SPY ticker update from python code.