An updated version is available that uses the v10 Yahoo quote service, including support for session cookies. As before, comment (#) out the yahooURL option in sites.dat (i.e., do not use the custom yahooURL option... well, unless it matches the default v10 service, or alternatively the v6 service).
Download: ofxpy_pocketsense.zip [last updated 7/20/2023 4:48pm EST]
If you haven't updated in a while, be aware that the requests package is required. See step #1 in the Install & Setup instructions for more info.
NOTE: The quotes script now establishes a session with the Yahoo server and stores the security token (cookie) locally until it expires. Testing indicates that the method works with both the v10 (default) and v6 services, at least for now. You'll notice that a new file named cookies.dat is created. If this file is deleted, a new session token will be requested, otherwise it's reused until expiration.
-Robert
Thank you, Robert. Worked perfectly. Tried 2 times, just in case. As expected: the 1st run said "Fetching new Yahoo Finance cookie" and created cookies.dat. The 2nd run did not say anything about cookies (apparently used the state created on the 1st run). I am on 32-bit Miniconda.
ReplyDeletesubscribing
ReplyDeletesubscribing
ReplyDeleteThanks, Robert, it worked great. It also appeared to run much faster than earlier versions of PocketSense.
ReplyDeleteIt's definitely faster than before, I guess the cookie helps the Yahoo backend somehow. But the v6 YahooURL fails ("Invalid quote response. Skipping."). Your notes above seem to indicate it should work, for now?
ReplyDeleteForgot to mention. Changed YahooURL format. See sites.template for example, but hopefully no need to override for now.
DeleteAha - ok, %s is now "{ticker}". Yep, works with or without it - and I'm leaving it commented out. Thanks!
DeleteI don't know. My temporary fix before your update was to modify the site_cfg.py file to change the YahooURL to v6. With your latest update, I'm back to v10.
ReplyDeleteDon't know if this is just me but:
ReplyDeleteUsing Setup Option 7 Test Stock Fund Prices select the option for stocks, works fine the first and second time,
but if I get another item like a credit card its also okay, but if I get the stocks again I get an error and the program crashes:
File "C:\DOS\OFX\quotes.py", line 413, in getQuotes
yahooSession, yahooCrumb = yahooSession()
TypeError: 'Session' object is not callable
Ray
Doubt it's "just you", seeing that I didn't test that sequence. Stay tuned...
DeleteFixed and posted.
DeleteThat did it Thank you for all your hard work.
DeleteRay
Hello,
ReplyDeleteI'm having a problem with the new quotes feature. When I attempt to test stock quotes from setup I get this error:
Test Stock/Fund Pricing Updates (Y/N)? y
Fetching new Yahoo Finance cookie
Traceback (most recent call last):
File "C:\PocketSense\Setup.py", line 400, in
test_quotes()
File "C:\PocketSense\Setup.py", line 214, in test_quotes
status, ofxFile1, ofxFile2, htmFile = quotes.getQuotes()
File "C:\PocketSense\quotes.py", line 412, in getQuotes
yahooSession, yahooCrumb = getYahooSession()
File "C:\PocketSense\quotes.py", line 357, in getYahooSession
response = requests.get("https://fc.yahoo.com", headers=headers, allow_redirects=True)
File "C:\Python27\lib\site-packages\requests\api.py", line 72, in get
return request('get', url, params=params, **kwargs)
File "C:\Python27\lib\site-packages\requests\api.py", line 58, in request
return session.request(method=method, url=url, **kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 508, in request
resp = self.send(prep, **send_kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 618, in send
r = adapter.send(request, **kwargs)
File "C:\Python27\lib\site-packages\requests\adapters.py", line 508, in send
raise ConnectionError(e, request=request)
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 11004] getaddrinfo failed',))
I just now tested w/ a fresh install. Python version? Anything else special?
DeleteI just updated (overwrite previous files) and getting below error:
ReplyDeleteFetching new Yahoo Finance cookie
Traceback (most recent call last):
File "****\Money_Quotes\Getdata.py", line 195, in
status, quoteFile1, quoteFile2, htmFileName = quotes.getQuotes()
File "****\Money_Quotes\quotes.py", line 412, in getQuotes
yahooSession, yahooCrumb = getYahooSession()
File "****\Money_Quotes\quotes.py", line 357, in getYahooSession
response = requests.get("https://fc.yahoo.com", headers=headers, allow_redirects=True)
File "C:\Python27\lib\site-packages\requests\api.py", line 75, in get
return request('get', url, params=params, **kwargs)
File "C:\Python27\lib\site-packages\requests\api.py", line 61, in request
return session.request(method=method, url=url, **kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 529, in request
resp = self.send(prep, **send_kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 645, in send
r = adapter.send(request, **kwargs)
File "C:\Python27\lib\site-packages\requests\adapters.py", line 517, in send
raise SSLError(e, request=request)
requests.exceptions.SSLError: HTTPSConnectionPool(host='fc.yahoo.com', port=443): Max retries exceeded with url: / (Caused by SSLError(SSLError(1, u'[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:727)'),))
What python version are you using? I ask, because that error looks like it's using an older ssl library.
DeleteIt's 2.7.18.
DeletePython 2.7.18 (v2.7.18:8d21aa21f2, Apr 20 2020, 13:25:05) [MSC v.1500 64 bit (AMD64)] on win32
I will add that my company recently pushed Zscaler onto our laptops, maybe this is interfering with the session cookies? I had no issues with the prior release.
DeleteI reverted my files back to end of May, and used https://query2.finance.yahoo.com/v6/finance/quoteSummary/%s?modules=price, and it's working again. Looks to be tied to the new session cookie code.
DeleteI figured out part of it. I am using a DNS redirector and pihole on my network to block advertising sites. Bypassing the DNS redirector enabled the quote service to work. This bypass is only required to obtain the session cookie. Once that is obtained, quote lookups still work while connected to the DNS redirector.
DeleteNo further work required (unless you happen to know why Python/requests is not respecting the host's DNS settings.
I'm not familiar w/ pihole, but I do use OpenDNS to manage those kinds of things. I checked the pihole documentation and it looks like you can whitelist sites. If so, try whitelisting fc.yahoo.com . It's a non-entry point to yahoo but provides a session cookie that .finance needs. Just fyi, but openDNS works as-is.
DeleteCan I get some help? Have been unsing Pocketsense for years. It is great. Several months ago started getting "skipping invalid quote" messages. I've tried downloading new versions and tried installing requests w/ pip command - got message that I'm using old pip and that Python 2.7 is out of life. Installation notes say don't go to 3.0. Would love to get stock quotes working again. Advice? Thanks in advance.
ReplyDeleteInstall https://www.python.org/downloads/release/python-2718/ and use pip to install requests. Ignore the "end of life" message, which is true... but the scripts only work w/ v2.7.x.
DeleteI see two issues reported so far but need more info to evaluate. There are two likely scenarios. a) older version of python with outdated ssl library, or b) a VPN or other type of network redirection blocking the dns lookup.
ReplyDeleteFirst - thanks for responding. Can you direct me to a recommended newer python version? That would be easy for me to attempt. I'm using Python 2.17.18.
ReplyDelete2.7.18
ReplyDeletelet me know if this is aids in a diagnosis.
ReplyDeleteWhen I run pip from \python27\scripts directory I get:
PS C:\Python27\Scripts> .\pip install requests
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Requirement already satisfied: requests in c:\python27\lib\site-packages (2.27.1)
Requirement already satisfied: idna<3,>=2.5; python_version < "3" in c:\python27\lib\site-packages (from requests) (2.10)
Requirement already satisfied: chardet<5,>=3.0.2; python_version < "3" in c:\python27\lib\site-packages (from requests) (4.0.0)
Requirement already satisfied: certifi>=2017.4.17 in c:\python27\lib\site-packages (from requests) (2021.10.8)
Requirement already satisfied: urllib3<1.27,>=1.21.1 in c:\python27\lib\site-packages (from requests) (1.26.16)
WARNING: You are using pip version 19.2.3, however version 20.3.4 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.
Then I still get below when I run Pocketsense:
Searching .\import\ for statements to import
Getting security and fund quotes...
** VBIAX : invalid quote response. Skipping...
** VCADX : invalid quote response. Skipping...
Pocketsense successfully loads my brokerage account activity.
To verify, you're using the latest version (2023-Jul-20)? If so, verify that yahooURL is disabled (commented out) in sites.dat.
DeleteDownloaded latest version, again. Futzed around with settings but I think thing that did it was to convert the sites.template file to sites.dat and copy over my specifics. Maybe not that but working now. Thanks!
ReplyDeleteI know that Fidelity NetBenefits had some issues a few months ago that were resolved. It appears that I am not getting transactions in the ofx data the last few weeks. However, I am getting the current security units and dollar value. Maybe Fidelity has messed with the ofx, again? Thanks
ReplyDeleteHi David, our company is using Fidelity NetBenefits as well . Can you share how to get daily quotes? Thanks!
DeleteSure, The sites.dat file entry should be:
DeleteSiteName : Fidelity Netbenefits
AcctType : INVSTMT #investment
fiorg : nbofx.fidelity.com
fid : 8288
url : https://nbofx.fidelity.com/netbenefits/ofx/download
bankid :
brokerid :
appid :
appver :
mininterval: 30
userAgent: none
Good luck!
Here's the sites.dat entry that works for me, very similar:
DeleteSiteName : FIDELITY NET BENEFITS
AcctType : INVSTMT
fiorg : nbofx.fidelity.com
BrokerID : nbofx.fidelity.com
fid : 8288
url : https://nbofx.fidelity.com:443/netbenefits/ofx/download
appid : QWIN
appver : 2400
minInterval: 90
David, Andrew - thank you, but i am not sure that this is what i need. Our company 401(K) is using trust , so the funds on Fidelity site looks like that : 91324P300 - VANG INST 500 IDX TR .So , how can i update/download price ?
DeleteArkady:
Delete1. Have you attempted to download the transactions via PocketSense using Andrew Shafer's or David P's entry in the sites.dat? What came of that if you did. I would expect the closing prices to come in via that download along with the transactions. I think this should be your preferred method.
2. CUSIP 91324P300 has a symbol: VFFSX. So I would set the symbol in the Details for the investment. Then I would think you could use the quotes via Yahoo method to get the latest price. This method would be good to try if the OFX/QFX downloads via PocketSense did not.
I am not sure what "Our company 401(K) is using trust", but maybe that means that you tried, but your transactions are not available via PocketSense.
Cal Learner.
Institutional 500 Index Trust - that how it called and price on internet not the same as a real price on fund - today internet $250.01, when i login Fidelity show $167.84 . Link that i am using to see positions and prices : https://digital.fidelity.com/ftgw/digital/portfolio/positions. When i download transactions(https://retiretxn.fidelity.com/nbretail/savings2/transactionhistory/downloadchoose) as .gif file that what i have in it !Type:Invst
DeleteD08/04/2023
NBuy
YVANG INST 500 IDX TR(ON9W)
I167.84000
Q2.394
U401.83
T401.83
MContribution
Arkady: I find no symbol for a Vanguard 500 mutual fund or ETF that has a price near $167.84. I used the Yahoo mutual fund and ETF screeners. I suspect that your fund does not have a public symbol.
DeleteHave you attempted to download the transactions via PocketSense using Andrew Shafer's or David P's entry in the sites.dat? What came of that if you did. I would expect the closing prices to come in via that download along with the transactions.
Cal Learner
I hesitate to mention it, but if you look in sites.template for quotes, you'll see that there are two options: m and s. If these are used, a quote can be retrieved for a different symbol, multiplied by a constant, and sent to Money as different symbol. This could be used to create a "tracking match" between a public and private fund, such as a public S&P 500 index fund mapped to a similar fund inside a private 401k.
DeleteBut... there's a catch. Funds inside company retirement accounts are often "trusts", and won't track exactly over time. The biggest difference is that they incorporate dividends into the unit price, rather than "paying out and adding shares", as you see in public funds. The tracking multiplier (m) would therefore drift over time and need occasional adjustment (depending on dividend frequency and size vs account holdings).
Thank You, Robert !!! I was assuming something like that, but did not see any explanation on Fidelity.
DeleteWe have a 529 account at Vanguard that uses the internal Vanguard funds (no public ticker) that track the public funds very well. First, find the fund that tracks most closely based on the composition. For example, the 529 account has an S&P 500 Index that tracks vanguard's VIIIX fairly well. Determine the price ratio and then make an entry in sites.dat in the "" section with that multiplier and use a new ticker to keep it clean in your accounts.
DeleteMy example (I prepend "V529" to the actual ticker to create a unique ticker):
"VIIIX m:0.183972 s:V529VIIIX"
I have this setup in a Google sheet to automatically calculate the ratios and create the text string. I update the multipliers every few months because it will drift a couple percent over 8 months.
This line in Google sheets will import the price for the Vanguard internal fund (change the FundId to get different funds):
"=INDEX(IMPORTHTML("https://personal.vanguard.com/us/JSP/Funds/VGITab/VGIFundOverviewTabContent.jsf?FundIntExt=INT&FundId=4515","table",5),1,2)"
This line will import the public fund prices:
"=GOOGLEFINANCE("VIIIX", "price")"
The rest of the sheet is simple math and text conversion to generate the strings.
Hey Robert,
ReplyDeleteA feature request that I would like is a Setup option to "test imports". Sometimes I will run Getdata.py to download transactions from the banks that support it while I go to some bank websites to manually download OFX files (e.g. Wealthfront, Chase, etc.). To use the scrubber routines on these files I have to run Getdata.py again, which then downloads the bank transactions another time. I would like an option from Setup.py to test imports which would do all the normal processing of the imports folder without connecting to any online services.
Thanks,
Andrew
I've had similar experiences, but rather than adding to setup, using a prompt in getdata due to the way imports are (currently) handled. I'll give it more thought.
DeleteRobert: How about an argv software switch for special operations like this. I run getdata with a batch file, so having getdata default to getting it all is useful.
DeleteRP: You are not going to download exchange rates with PocketSense.
Cal Learner
I like the idea of command line arguments.
DeleteHi,
ReplyDeletecould I use the script to update exchange rates?
RP
anyone?
DeleteRP: You are not going to download exchange rates with PocketSense.
DeleteI posted that on July 29, 2023, but I consolidated that with another post, so as to slow exhausting the 200 post limit.
Cal Learner
can I use this script to update exchange rates as well, when Money goes offline? Again, it turns out you can by simply including Yahoo’s symbols for the exchange rates (hover over the currency pair here to see the symbols) in the STOCKS section of TFB’s Quotes.py [or Robert’s sites.dat] script:
DeleteEURUSD=X for Euro to US Dollar
USDJPY=X for USD to Japanese Yen
AUDUSD=X for Aussy Dollar to US Dollar
etc.
So we are off to a great start – you can modify TFB’s Quotes.py [or Bob’s Control.py] script to update all of the stock, mutual fund, exchange rates and market index values that aren’t part of your normal OFX downloads (like 401k accounts, DRIP accounts, etc.), or if you manually input all of your data, this script can be used to update all of your positions. The only negative is that Money won’t calculate your daily change value with downloaded quotes, as it does with the online updates.
Hi Folks!
ReplyDeleteI hope this is the right place for random off-topic questions (now that the most recent quotes debacle has been resolved ... thank you!).
Are there any workarounds for Money's 12 character limit in the Ticker field? I've recently moved my options trading portfolio from Fidelity to TD Ameritrade, and apparently I've just been very lucky to not have conflicting symbols on the Fidelity side. But mostly that's because Fidelity actually uses a real options symbol in the element of the OFX, like "AAPL230915C170" which still gets slightly truncated to "AAPL230915C1", but by pure chance has always ended up unique. But in the OFX from TD Ameritrade the is "AAPL Sep 15 2023 170 Call", which conflicts with the other side of the spread (at a different strike price) when it gets truncated to 12 characters (in this case "AAPL Sep 15 ").
I've considered using the "s:" notation in sites.dat to alias the ticker to something unique, but that would quickly become more trouble than it's worth considering I'd potentially be adding dozens of them every week, and I rarely hold them more than a few weeks.
Is there a way to change the field length in Money's database? Or is there some sort of custom scrubber routine I could use to map TDA's "ticker" (using the term loosely in their case) to something unique?
Thank you Robert for all you do to keep Money alive, and for any suggestions or insights!
Edit because tags were stripped: I'm referring to the TICKER element in the text above in spots where a word seems to be missing.
DeleteThe field length in Money cannot be changed. Are the "symbols" being imported with the ofx statement from TDA, or is it a lookup/import via Yahoo?
DeleteThe symbols are in the OFX from TDA, although they aren't symbols at all ... the TICKER element contains "AAPL Sep 15 2023 170 Call" rather than something like "AAPL230915C170" like they were from Fidelity (which had a much better chance of still being unique once Money truncated them to 12 characters).
DeleteI've tried looking at the scrubber routines thinking I could replace the ticker in the OFX, but I've never worked with Python and had trouble figuring out what the routines were doing. I was kind of hoping I wasn't alone in this boat and someone had already worked up something similar. If you're not aware of something that could be repurposed, perhaps a push in the right direction?
I'm not familiar enough with formatting to offer a solid suggestion. If there is a steady (exact) char pattern, then it may be pretty simple. Any variation would break it though. If the symbol could be something that makes little sense (e.g., AAPLx9b0d5k1), a reproducible (nearly certain) match could be more easily generated. Actually, if the symbol doesn't matter, a full 12 byte hash would be the most certain--yet the least sensical.
DeleteI'm not concerned about it being sensical, as long as a given string would produce the same hash. I was considering a key-value lookup file to reuse a symbol when the same "ticker" string came in the OFX, or generate new (random-ish) one to add to the dictionary file when a new ticker came in ... but then I got stuck figuring out the Python. :)
DeleteI think generating a hash would be a great approach, eliminating the need for the dictionary file. The "name" field would still be displayed most places in Money so it really doesn't matter what the symbol/ticker is, as long as subsequent transactions get lumped with the correct holding. E.g. I might open a quantity of contracts, then later close some of them, or possibly add more, and then finally close it all at some later date ... and I'll be happy if all of that gets matched to the same investment.
It sounds like you have an idea then? Please say you have an idea. :)
I believe so, but it may be a bit before I can focus on it. I want to add some cmd options sometime soon, so likely look at it then. I don't think it will be difficult but could be missing something. Is this the ofx server address https://ofxs.ameritrade.com ?
DeleteReally appreciate that, thank you! I'll just hold off on importing that account until then.
DeleteThe address that works for me is a bit different. I also had to fiddle with some of the settings to get it to work, but I don't remember which specifically. Here's the connection I use:
SiteName : TD Ameritrade
AcctType : INVSTMT
fiorg : ameritrade.com
fid : 5024
url : https://ofxs.ameritrade.com/cgi-bin/apps/OFX
bankid :
brokerid : ameritrade.com
appid : QWIN
appver : 2600
mininterval:
timeOffset :
ofxver : 103
Thanks again!
I have several accounts at TDAmeritrade also (using similar to the above settings). Please be aware that all TDAmeritrade accounts are being migrated to Schwab, which does not support OFX (either direct connection or via their website). I wouldn't spend too much time getting that to work.
DeleteDoes anyone have an investment company that supports direct connections and/or website downloads?
Andrew: Fidelity Investments, Vanguard, Janus have OFX/QFX servers that lets PocketSense feed transactions into Microsoft Money.
DeleteCal Learner
I knew about the transition to Schwab but had no idea they don't support OFX. That's a bummer. I'm still sticking with them though just for the ThinkOrSwim tools, which are also being migrated. I find them essential for the type of trading I do in this particular account, WAY better than Fidelity's tools. My account hasn't been migrated yet, but I'll keep my fingers crossed that the OFX servers will be part of the migration.
DeleteAndrew, has your account been migrated yet? In other words, do you know for sure that the OFX servers no longer have the data after the migration?
Yeah, I used to have investment accounts with USAA but that division moved to Schwab and they don't have OFX server or download options. My wife's TDAmeritrade account has been moved and they still don't have those options (I noticed the accounts moved specifically because her username and password stopped working with the TDAmeritrade server). My own accounts will be moved soon. I think I might check out Fidelity.
DeleteAnonymous Anonymous. I don't follow.
DeleteRegarding Schwab, it's interesting that they don't support ofx. My work savings plan is w/ Schwab but uses their "workplace" site, which *does* offer manual download. Go figure. That said, does the Schwab brokerage system offer csv download?
ReplyDeleteyes Robert, csv is the only export format offered.
DeleteDoes Schwab include only transactions in the csv, or do they also include positions?
DeleteLooking at a file, I would say only transactions. Here's the header line...
DeleteDate Action Symbol Description Quantity Price Fees & Comm Amount
I've just discovered that Schwab also offers csv export of account balances, as well as Positions data.
Delete- ameridan
Here is the header line for the Positions file...
DeleteSymbol Description Quantity Price Price Change % Price Change $ Market Value Day Change % Day Change $ Cost Basis Gain/Loss % Gain/Loss $ Ratings Reinvest Dividends? Capital Gains? % Of Account Security Type
The position and transaction csv downloads are separate files? I ask because Vanguard also provides both, but in a single csv. My HSA investment account only provides transactions (one file).
DeleteJust curious at this point as to what's possible.
yes, they are offered separately. one from the activity tab; the other from the positions tab.
DeleteIs anyone able to download OFX from Fidelity NetBenefits? I get error message:
ReplyDeleteAccess Denied
You don't have permission to access "http://nbofx.fidelity.com/netbenefits/ofx/download" on this server.
Robert, I'm not sure how to contact you other than this blog. Thank you so much for maintaining and publishing these scripts over the years. They have been invaluable to me for working with MS Money. Recently, however, I've been working on a project to replace MS Money and Excel worksheets for managing my money. As part of this project, I ported your scripts from using Python 2 to using Python 3 (no backward compatibility). I now have the current version (2023-Jul-20) working with Python 3 and tested with my own data and sources (only Vanguard and Fidelity). Please let me know if you'd like to get my version of your code for your own use. Regards, Matt
ReplyDeletepocketsense1 at gmail
DeleteHi Matt, would you mind publishing your python 3 port somewhere? GitHub?
DeleteYou can find it here - https://github.com/magnuspyke/ofxscripts. For full disclosure, I have not comprehensively tested this. It works well for my data, but you may find code paths that don't work if I have not exercised them myself. Also, I tried to keep the changes from Robert's code to a minimum so Robert could use them if he wished to. I just changed the scripts enough so they worked with Python 3.
DeleteEric: See Andrew ShaferJuly 28, 2023 if you have not already. We can infer he is successful. One thing I would do differently is to have appver: blank in sites.dat. That way, the default for appver comes from PocketSense, and I would not have to worry about updating the appver value, as long as I was updating PocketSense at times. The PocketSense version I am running defaults to appver: 2400.
ReplyDeleteYour error message makes me wonder if the username or password differs from the username and password that you use on the website. This next thing only works if you don't use a password on your PocketSense so there is no encryption. Suppose your username is "EricWXYZ", try this command in a Windows cmd window after CD to your Pocketsense folder:
find "EricWXYZ" ofx_config.cfg
Repeat that, but use the password as the string in quotes. If you don't find both, then you have probably mis-typed something.
I could have set up a fake NetBenefits account to test what comes back if you have a wrong username, but I did not.
Also, Eric, do you use PocketSense with a non-NetBenefits Fidelity account? If so, I wondered if that was successful for you. It works nicely for me.
Cal Learner
Hi Cal,
DeleteThank you for your response.
I did check the presence of the right passwords on the ofx_config.cfg file, and it's all good. I do also have a regular set of Fidelity accounts (accessible via the same credentials for NetBenefits and general Fidelity) and the OFX download from Fidelity continues to work properly. I.o.w., while credentials are the same, while properly configured in the config file, Netebefits reject the OFX download. I have tried to set accounts using the known good credentials ("set up new account") and it fails and Pocketsense setup reports invalid response from NetBenefits server.
I tested Fidelity Netbenefits just now and it worked fine for me.
DeleteThank you for contuniing to update this wonderful software. George M
ReplyDeleteHi Robert, does Yahoo allow you to ask for quotes from some historical date? I like to get stock prices at the end of each month, but i don't update my money file on last day of each month. I might do it a few weeks later, but at that time I'd like to get the price as of the end of the last month as opposed to prices on the day I'm running the scripts. Is there an easy way to accomplish this through your scripts? You could have some parameter that sets what date to use for pulling prices, if yahoo has that option. Thanks!
ReplyDeleteThe ps scripts only pull the latest quote data, not historical.
DeleteDo you know if yahoo interface allows historical query or not?
DeleteI am not familiar with it, but that doesn't mean they don't... just haven't looked into it myself.
DeleteAnyone else having an issue with their money file this morning? There may have been some automatic windows update and now money just shuts down when opened. Any idea on how to fix this?
ReplyDeleteMine works fine today. You might need to provide some more information (e.g. Windows version and build number). Have you tried to open any backup files? Have you checked your current file for corruption? Can you open a sample or empty Money file? Have you tried some of the troubleshooting steps found with a quick Google search (e.g. https://groups.google.com/g/microsoft-money/c/iiAdL6ots4k)
DeleteMy Windows version & Build #: MS Windows 11 Home 10.0.22621 Build 22621 (there's a hardware abstraction number: 10.0.22621.1413). I am able to open a new (blank) file. I have tried restoring the file and there are no errors. I'm still continuing to troubleshoot. It may have something to do with the modules I have opening on start page...not sure yet.
DeleteLooks like it is the "Investment Summary" module on the start page that is causing money to crash. I was able to open my file on an old computer, disable that on the homepage layout and reopen the file just fine on the computer I normally use. Hopefully this will help someone else.
DeleteThis comment has been removed by the author.
ReplyDeleteedit: Tried to fix the HTML to look correct.
ReplyDeleteI just had a new issue with Chase credit card imports today. It looks like they have updated their credit card (maybe bank accounts, but I don't have that type with them) OFX export with a malformed tag. Specifically, it looks like they removed the account number from the export.
This is the current tag: "<CCACCTFROM></CCACCTFROM>", but ACCTFROM transactions require a field.
Trying to import this file into Money gives an error message about a corrupted import file.
Running OFX Analyzer on the file gives this:
===
*** All line number references do not include header lines ***
***OFX 1.02 Add ~10
***OFX 2.0 Add ~2
Running Data Through Parser
HRESULT = 0
Error Code = 0
HRESULT = -2147450874
Error Code = 54
Severity = X
Type = C
Error Message = XC%s element ended prematurely; required subelement omitted
Other Info = MNYSGMPB: SGML error at (null), line 28 at ">":
XCCCACCTFROM element ended prematurely; required subelement omitted
===
To fix, I edited the above tag in the file to include an account number (this is what Money matches against):
"<CCACCTFROM><ACCTID>last4digitsofcardhere</CCACCTFROM>". Hopefully they change the server to at least put some type of unique identifier here.H
Andrew, good job on getting the pointy brackets escaped.
ReplyDeleteDo they have that ACCTID elsewhere in the file? I would think this will cause everybody problems, but if it persists, I could make a scrubber. To make it easier to write, I would probably have the credit card number hard coded, or else put some fixed string such as XXChasecCardXX. That would work if you only had one Chase card. But lets see if the problem gets fixed before trying to make a scrubber..
Is PocketSense getting the OFX/QFX file directly, or are you using a web interface to download that?
Cal Learner
Chase only offers web site qfx downloads. I import those files via pocketsense, but so far they need not be scrubbed, so they can be imported directly. I just imported a bank checking account, and it seems to be working fine.
ReplyDeleteI'm getting the same error with Chase. However, one of my two Chase CC accounts is not even downloading. There is a chance they are having server issues. Perhaps wait a day or two.
ReplyDeleteIn addition to the missing tag, OFX data analyzer is also saying there are duplicate FITIDs. So the whole thing may be messed up at the moment
After comparing older (prior to 10/5/2023) QFX file downloaded from Chase for my credit cards, yes the ACCTID is missing and FITID 0 for every transaction. I called Chase and supposedly will get a call-back Monday. After some experimentation, I see how these could be faked within the scripts. Using the last 4 digits of the account number in the file name, one could generate the ACCTID as "Chase|nnnn" where nnnn is the last 4 digits of the account number. In addition, FITID could be set to the date of the transaction followed by a unique serial number (unique within only the current QFX file) and look like "yyyymmddn" where n is that unique number. It's rather bizarre that Chase would suddenly mess with these two very important fields. Let's hope they didn't intend this and will fix soon, otherwise some scrubber scripting will be needed.
ReplyDeleteI have multiple Chase credit cards and each QFX file downloaded has the last 4 digits in the name. The ACCTID I toyed with was hand entering the full number and hand entering my example of Chase|nnnn. Perhaps just nnnn is fine. Of course without the full number one will have to tell MS-Money which account is associated with the new generated name on the first import, but not a big deal.
DeleteFor my 401k that only publishes CSV files, I created the following code for the FITID:
ReplyDeletetransaction['FITID'] = hashlib.md5(str(transaction).encode()).hexdigest()
In my case transaction was already preloaded with all the unique characteristics of the transaction, so the hash is guaranteed to be unique (I think). Only potential problem I see is if the credit card has two identical transactions (you buy a burger at McD's twice on the same date)
Excellent! Thank you for that. I just now made a copy of Robert's Discover scrubber routine, passed the filename into it so as to pick up the last 4 digits of acct number. Plus I used his code to sequence the FITID and this works, except of course the FITID bit needs to use your terrific hash trick. (And Robert has some code in there to flush out duplicates if your hash did happen to do create them.) Frankly, I'm hoping either Chase will fix this or Robert will something like what I'm doing, but I got too many transactions to let this slide for weeks.
DeleteWould you be willing to share your scrubber? My two main credit cards are Chase, so things are going to get ugly very quickly.
DeleteAbsolutely! I'm not a Python guy, but I used to write operating systems. So I'm learning on the fly here. Back to you when it works. May be a few days while I bang my head against a wall numerous times. So far it's only very minor changes to a copy of the Discover scrubber.
DeleteFinally got to spend some quality time with it today. (My hat's off to Robert for being clever.) I think I see how to get this done, though perhaps not as elegantly. Back soon, as I have 4 Chase cards.
DeleteGetting a test vehicle like VSCode or any other to work with Python 2.7 is damn near impossible. If I don't get any success, I may just switch to 4 other credit cards. Frustrating as I've pretty well figured the coding part out. Robert? Feel free to chime in at this point. BTW, Carlos and I the only 2 people out here still using MS-Money and Chase credit cards?
DeleteI use a Chase Amazon card and my wife uses a Chase United card. The FITID being 0 hasn't messed up my transactions yet. So far, I'm just manually updating the ACCTID in the manually downloaded QFX file prior to running PocketSense. I use a scrubber routine to remove the trailing "*M12345678" from Amazon.com purchases so they all alias to Amazon in MS Money.
DeleteThank you Andrew for keeping me out of the weeds. I've dumbed this down to just solve the issue of embedding the account number within the Chase credit card statement using the first 9 characters of the filename. For those who download daily and have several Chase accounts feel free to use this: https://drive.google.com/file/d/156A75GhAcUH2iSEwkqufRQBdOdaQI9KY/view?usp=drive_link
DeleteAs the comments state, I only had Chase credit card vs bank statements to test with. I'll check back occasionally in case someone stumbles on an error. Best of luck.
citicard still offers ofx downloads via pocketsense (as well as via their website). boa credit cards (and many credit unions) offers only via their website, which i then import using pocketsense.
ReplyDeleteI've been away a good bit and catching up on unrelated things... so with that in mind... regarding the issue with Chase: Is the issue only for "direct connect" download, or does it also happen using manual ofx download? If both, do they offer manual csv download?
ReplyDeleteChase stopped offering direct connect sometime earlier this year? They still allow QFX files to be manually downloaded for credit card accounts. Don't know about bank accounts.
DeleteChase still allows manual QFX download from the website for both checking/savings and CC: Quicken WebConnect option.
DeleteRobert - as @humblepie said, Chase stopped offering direct connect earlier this year, at least for credit cards. They allow manual downloads through the website of QFX files. The issue with the missing <ACCTID> is seen in the manual downloads. It is also missing the FITID for the transactions. As @Andrew Shafer stated, the workaround is to manually add ACCTID; the missing FITID does not seem to create an issue.
ReplyDeleteMy curiosity as to whether they offer csv downloads related to the way that FITID may be generated. When implementing a csv2ofx module for Discover Bank, I chose a method based on data columns available in most csv exports. Money matches transactions by FITID, so changing the method midstream leads to duplicates until the lookback no longer overlaps.
ReplyDeleteThat said, the following is how I implemented it for csv conversion (hope it comes through):
trnstr= transDate.strftime('%Y%m%d') + str(amount) + name + memo
fitid = hashlib.sha1(trnstr.encode('utf-8')).hexdigest()
where:
name = "pay to"
I have been using the Pocketsense scripts for many years. Something happened recently where, I seemed to be forced to create a password with the menu item for that in Pocketsense. I know I documented what I entered but alas have forgot where I left the password. Is there a way to start over? I was posting all the transactions myself to money and only using Pocketsense for the quotes server.
ReplyDeleteRename ofx_config.cfg to ofx_config.bak, then run setup.py and Enable Stock/Fund Quotes.
DeleteThank you very much! That did the trick. Do you have any idea why at the end of the script, after clicking "y" for import to Microsoft Money, I started to get "Access is Denied"? I have been dragging the quotes.ofx file from the xfr folder into Microsoft Money to import the quotes to get round that error for quite a while. It seemed to start out of the blue one day in the last few years.
DeleteNot sure. If quotes.ofx file is being created, then the scripts have local folder permissions. My guess is that ofx files aren't correctly associated with Money, perhaps due to another package being tested at some point? See FAQ #5 @ https://sites.google.com/site/pocketsense/home/msmoneyfixp1/faq
DeleteFor those of you who have Chase credit cards, the manual web downloads of QFX files continue to have an issue with missing the ACCTID as well as transaction FITIDs. The account ID is easy enough to paste in. Money seemed to be at first OK with FITIDs that were 0, however after a week or so I started having trouble with Money aggressively trying to match new transactions to others that looked "similar". I have come up with the following code for my Chase scrubber. It creates a hash of the transaction and adds it to the FITID:
ReplyDelete<pre>
import hashlib, re
p = re.compile(r'(?P.+?)(?P0)(?P.+?)', re.IGNORECASE|re.DOTALL)
if p.search(ofx):
ofx = p.sub(lambda r: _CreateFITID(r), ofx)
def _CreateFITID(r):
fitid = hashlib.md5(r.group(1)+r.group(3)).hexdigest()
return r.group(1)+fitid+r.group(3)
<pre>
So far it seems to work. As always your mileage may vary.
oops. the regular expression got all hosed up. I'll try again, and if it doesn't work, someone please tell me how to do it. Do I have to escape all brackets?
Deleter'(?P<s1><STMTTRN>.+?<FITID>)(?P<FITID>0)(?P<s2>.+?</STMTTRN>)'
I just downloaded my Chase statements today and they have restored the ACCTID and FITIDs!!!
DeleteI just realized this too! Thanks.
DeleteAnd Chase is back to being funny. Files are downloading fine and they include a ACCTID.
DeleteBUT - they are adding a dash before the last 4 digits of the ACCTID
Even weirder one of my credit cards downloaded with the OLD account number that got changed about 6 months ago when I had a fraudulent transaction. It had been downloading with the new number (except for the period with no account numbers) until today.
It seems that Chase's software team is deploying production code without thorough testing.
Hello Robert ! Is it just me or for everybody - as of today i am again getting 'invalid quote response. Skipping...' for every quote
ReplyDeleteQuotes working fine here.
DeleteInvalid quote response for me as well; the custom Yahoo address is commented out, and I rebuilt the sites.dat using the most recent template. No firewall except for Windows Defender. And I can get quotes fine from yahoo.com using the website. Last good set of quotes was 26Oct.
DeleteUpdate: I fully re-installed and updated everything with the 7-15 build, and it works fine. No idea what the deal was, but I seem to be ok. It's a pain to go through the "setup" again, but good to get it working.
DeleteThat's what I'm getting - invalid quote response - Skipping, sorry to say
ReplyDeleteInvalid response here too.
ReplyDeleteHasn't worked for me yesterday or today either. Bummer can't close out the month w/ quotes!
ReplyDeleteI have tested quotes multiple times today, all with success. Is an antivirus or firewall being used other than what's built into Windows (i.e., other than Defender and Microsoft Firewall)?
ReplyDeleteI was using the Pocketsense version released on May 27, 2023 and it stopped working last week with the same results you describe -- invalid quote response - skipping. I upgraded to the latest version of Pocketsense and everything has worked fine since. If you haven't updated, try this.
ReplyDeletePocketSense OFX Download Python Scripts, Ver: 2023-Jul-20
ReplyDeleteDownload transactions? (Y/N/I=Interactive) [Y] y
Default download interval= 1 days
No accounts have been configured. Run SETUP.PY to add accounts
Searching .\import\ for statements to import
Getting security and fund quotes
Getting quote for: UNH
** UNH: invalid quote response. Skipping.
Getting quote for: MDISX
** MDISX: invalid quote response. Skipping.
OK, i think i get it - i replaced /rebuild sites.dat using new template . Looks like it fixed the problem
DeleteFolks having trouble w/ quotes should definitely update to the latest version and disable the custom YahooURL option in sites.dat (i.e., #comment it out).
ReplyDeleteWell, I updated to latest and now back to where I was on July 24. When I try to run, I get below errors:
ReplyDeleteFetching new Yahoo Finance cookie
Traceback (most recent call last):
File "\Getdata.py", line 195, in
status, quoteFile1, quoteFile2, htmFileName = quotes.getQuotes()
File "\quotes.py", line 412, in getQuotes
yahooSession, yahooCrumb = getYahooSession()
File "quotes.py", line 357, in getYahooSession
response = requests.get("https://fc.yahoo.com", headers=headers, allow_redirects=True)
File "C:\Python27\lib\site-packages\requests\api.py", line 75, in get
return request('get', url, params=params, **kwargs)
File "C:\Python27\lib\site-packages\requests\api.py", line 61, in request
return session.request(method=method, url=url, **kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 529, in request
resp = self.send(prep, **send_kwargs)
File "C:\Python27\lib\site-packages\requests\sessions.py", line 645, in send
r = adapter.send(request, **kwargs)
File "C:\Python27\lib\site-packages\requests\adapters.py", line 517, in send
raise SSLError(e, request=request)
requests.exceptions.SSLError: HTTPSConnectionPool(host='fc.yahoo.com', port=443): Max retries exceeded with url: / (Caused by SSLError(SSLError(1, u'[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:727)'),))
Still running python 2.7.18:
Python 2.7.18 (v2.7.18:8d21aa21f2, Apr 20 2020, 13:25:05) [MSC v.1500 64 bit (AMD64)] on win32
The error suggests that the fc.yahoo.com url is being blocked, likely by a firewall or proxy service.
DeleteI was also seeing the same error messages and found it was due to the ad blocker (pi-hole) I was running.
DeleteETrade - Good morning. I am a weekend updater and this weekend (Nov 11/12) Etrade stopped working. I thought maybe weekend server maintenance but I just tried again (Monday 9am) and same error - Exception: Null statement received
ReplyDeleteEtrade does allow for money file downloads from online site and that did work
If any other Etrade users are having a better experience please let me know and I will investigate on my end
Tues Nov 14 - good news, Etrade is working again, seems like it was an Etrade tech issue
DeleteAnnouncing first release of scrubofxget.py with ofxtools on Python 3 (Rev 1.1)
ReplyDelete===================================================================
There has been some discussion of moving to Python 3.x. One option
is to try to convert the existing PocketSense scripts, and I have
seen postings recently to that effect. Another is to move to an
entirely new platform called ofxtools by Christopher Singley.
I chose that option in March 2023 and have had excellent results
using it with Fidelity, T. Rowe Price, Vanguard, and USAA over the
last 7 months.
What is OFX tools?
-------------------------------------------------------------------
ofxtools provides a very structured and comprehensive library for
implementing OFX clients in Python 3.8 or higher.
The project home page https://pypi.org/project/ofxtools/ says:
ofxtools requests, consumes and produces both OFXv1 (SGML) and
OFXv2 (XML) formats. It converts serialized markup to/from native
Python objects of the appropriate data type, while preserving
structure. It also handles Quicken’s QFX format, although it
ignores Intuit’s proprietary extension tags.
In a nutshell, ofxtools makes it simple to get OFX data and
extract it, or export your data in OFX format.
ofxtools takes a comprehensive, standards-based approach to
processing OFX. It targets compliance with the OFX specification,
specifically OFX versions 1.6 and 2.03.
A particular focus of ofxtools is full support of the OFX
investment message set.
More than 10 years' worth of OFX data from various financial
institutions has been run through the ``ofxtools`` parser,
with the results checked. Test coverage is high.
What is it NOT?
-------------------------------------------------------------------
ofxtools natively is not a direct replacement for the PocketSense
OFX scripts. Specifically it is missing two features:
1) A quote server. It has no support for downloading price
data from YAHOO, MSN Money, etc. If you take this route you
will need an alternative like Gaier Software's MSMoneyQuotes.exe.
https://gaiersoftware.com/Money
2) Unlike PocketSense Getdata.py, it does not wrap every OFX
response into one overall file to import. Instead there is
one file containing every account for each financial institution.
This version of scrubofxget does not trigger automatic import
of OFX response to MS Money. Both features are future
enhancement possibilities.
Comment continued below....
What is different?
ReplyDelete-------------------------------------------------------------------
ofxtools is a toolkit rather than an application. The web page
provides numerous programmatic examples for using the library. But
there is a powerful sample script called ofxget.py that encompases
most of the features of the toolkit such as:
list List known reachable OFX servers
scan Probe OFX server for working connection parameters
prof Download OFX service profile for server
acctinfo Download account information for a user login
stmt Download statement(s) for bank/CC/investment acct(s)
stmtend Download closing statement(s) for bank/CC account(s)
tax1099 (EXPERIMENTAL) Download US income tax data on f1099
A key feature of PocketSense is scrubbing OFX statements, and adding
that capability to ofxget was incredibly simple. ofxtools provides
direct access to the OFX data using the native Python library
xml.etree.ElementTree. I renamed ofxget.py to scrubofxget.py and added
a way to invoke specific scrubbers for individual financial institutions.
scrubofxget includes all the functions in PocketSense scrubber.py
(ver 6/23/2023) and scrub_Discover.py, but now implemented with etree.
It also comes with a suite of additional scrubbers for Fidelity,
TRowePrice, Vanguard, and USAA.
scrubofxget uses a combination of Command Line Interface (CLI) and
stored configuration information to download your OFX data.
Run it with a batch file that has one line for each financial
institution.
scrubofxget can optionally access OFX configuration information via
ofxhome.com, giving that central repository of financial institution
data a new lease on life.
How do you try ofxtools and scrubofxget?
-------------------------------------------------------------------
More detailed instructions are included with the scrubofx package,
but here is an overview.
1) Install Python version 3.8 or greater.
2) Install Python keyring to secure your OFX passwords.
3) Install ofxtools.
4) Unzip "scrubofxget 1.1.zip" file wherever you like. Link
is attached in this posting.
5) At the cmd prompt type "scrubofxget.py stmt -h" to see all
the CLI options available for downloading transactions.
Usage examples are included in:
"scrubofxget Installation and Usage.txt"
Support
-------------------------------------------------------------------
The ofxtools core toolkit is supported by it author.
Any frontends that use the toolkit are supported by the frontend
author and user communities like PocketSense. I will be happy
to help users who want to try scrubofxget via the PocketSense
forum if that is acceptable to Robert. If it needs a new home,
then I can try that too.
Final thoughts!
-------------------------------------------------------------------
I have used PocketSense since MS sunset Money and really appreciate
how a vibrant community has sprung up around it. However, I want
to move on from Python 2 and think that ofxtools is a powerful
toolkit that provides a structured migration path for downloading
OFX on Python 3.
Link to scrubofxget
-------------------------------------------------------------------
For those of you wishing to try out this approach, download the
zip file in this link:
https://drive.google.com/file/d/1P1jPjpx2Km735_ja4mKEpQjnDfwbuz5T/view?usp=sharing
Might this be a tool that can be used for the 2 to 3 conversion?
Deletehttps://docs.python.org/3/library/2to3.html
There are at least three options for migrating to Python 3. #1) Make minimum required changes to existing PocketSense scripts. A first attempt was posted by Matt on 24Aug2023 above. #2) Rewrite a simplified version of PocketSense scripts focusing on user interface and quotes, while letting ofxtools do the heavy lifting on the OFX end. Robert has previously mentioned how much more effort went into the UI than expected. #3) Build on a completely new foundation.
DeleteAt this point we have #1 and #3 to work with. I'm hoping to uninstall Python 2 soon. :)
Your link to "2to3 — Automated Python 2 to 3 code translation" seems viable for option #1. Will give it a try. Thanks for the tip.
DeleteI've tried the 2to3 tool on a couple tests. It worked very well, although I have not thoroughly tested it.
DeleteThe utility successfully converted all the files in PocketSense. The changes fell into one of 3 categories:
* Changed all references of urlib2 to a combination of urllib.request, urllib.error, urllib.parse
* Converted a number of Print() statements to Print(()) {with double parenthesis}. It appears this has something to do with printing tuples. I haven't been able to dig into WHY, but it all seems to work fine
* converted raw_input() to input()
As far as I can tell that was it. No other changes by the automated tool.
When running, I then had to take care of additional changes manually:
* change pyDes in text to pydes so it matches the capitalization of the actual file
* in ofx.py, line 272 encodes the response from the server to ASCII. the str.encode() function in Python3 returns type byte rather than type str. So I had to add a new .decode(encoding='ascii') as a number of consumers of that variable require strings. I think this has the same effect as the original - namely ensuring the new string is properly formatted as ascii, but I still need to read up some more.
Robert also mentioned that another user has also attempted a port:
GitHub - magnuspyke/ofxscripts
I haven't looked a that port to see how it compares.
Really, my main consideration right now is figuring out how to download automatically all the accounts that no longer offer OFX servers. That's a much more significant pain for me than some tweaking of Python 2/3.
While ofxtools seems to be a much more powerful library, I think the changes to current Pocketsense to go from 2 to 3 are minimal enough that it is not worth the effort to migrate to the new library.
Carlos, thanks for this write up on the 2to3 tool. The port you refer to on Github was the work I did. The changes the 2to3 tool did sound very similar to the changes I did manually in porting the ofxscripts to Python 3. The port works well for me with Python 3 and, as noted, my approach was to change the code in a minimal way. It allows me to avoid Python 2. If folks find this port doesn't work for them, please let me know by filing an issue on Github. I'll do what I can to help.
DeleteYou hit the nail on the head, regarding the main issue with downloading transactions from financial institutions. It appears that most FI's are moving away from offering OFX or even QFX (which is what I use) endpoints and moving towards other approaches, many proprietary. These approaches don't appear to follow standards and therefore keeping up with each FI's implementation is a lot of work. A good example is Capital One - https://developer.capitalone.com/documentation. If anyone is working on using their API to download account transactions, I'd be open to partnering with them.
Forgot to sign in before posting - the prior post was from me (Matt).
DeleteReleased v1.2
DeleteMinor update to warn if the OFX response is not successful.
https://drive.google.com/file/d/1KbbTgp-L7chxqIc9mBBFFsaUcqBuilVF/view?usp=drive_link
Must be something in the air these days since I also did a PocketSense Python3 port this weekend. I'd added precious metal quote support by web-scraping Kitco back in 2018 (when I didn't know much Python), so I needed to get it all in git to merge the latest with my changes.
ReplyDeleteI dropped the pyDes from pocketsense and adopted the pip installed one, just moving the password prompt function Robert added to rlib1.py. There is a compat issue with connect key lookup, so to avoid having to struggle with re-doing the keys with my banks (a pain when testing in parallel with my active 2.7 version) I refactored that to use b64encode on the lookup key and then stored the data in JSON instead of pickle so I could hand-edit the keys into the file. I have more testing to do to make sure it works on the automated schedule I run it on.
Just a quick comment to say I'm watching the 2to3 discussion, but on the fence about effort/reward. The most important is uncertainty about the continued use of MS Money via ofx, in turn due to the slow decay of ofx support by financial concerns. I'd prefer that it wasn't the case, but I'd also rather I wasn't getting old(er) ;)
ReplyDeleteAnother reason I haven't been too excited about it is that when I dig into the scripts with v3 in mind, I tend to want to wholesale rewrite parts. While 2to3 "mostly works", it can be a bit unsightly vs how it could (should) be written natively in v3.
Well said Robert, I agree with all of it. As long as the 2 code runs that is all I need, no need here for a change to 3. The big issue is what you described, the slow elimination of OFX feeds for which we are helpless.
DeleteHappy Thanksgiving to all!
Back in April I felt that Fidelity might stop OFX direct downloads. I worked on a program that would produce OFX files from Fidelity *.csv. I paused that, but it was pretty promising, and more featured than some OFX synthesis. I found my files, and may get inspired again. Loss of direct download from Fidelity would motivate me. I really like that the direct OFX files from Fidelity have continued.
DeleteWhat are the Schwab users doing? Move to Fidelity or Vanguard, or do they have a converter that produces an OFX file?
Cal Learner.
Good morning. I asked bing AI chat for info on using python to convert CSV to OFX. Here is the response which could be helpful (if not a complete solution)--
DeleteGood morning! Yes, there is a Python library called `csv2ofx` that can convert a CSV file to an OFX file. It is available on PyPI and can be installed using pip. Here is the command to install it:
```python
pip install csv2ofx
```
Once installed, you can use the library to convert your CSV file to an OFX file. Here is an example code snippet that shows how to use the library:
```python
import csv2ofx
csv_file = 'path/to/csv/file.csv'
ofx_file = 'path/to/ofx/file.ofx'
csv2ofx.convert(csv_file, ofx_file)
```
You can also use the command line interface to convert your CSV file to an OFX file. Here is an example command:
```python
csv2ofx path/to/csv/file.csv path/to/ofx/file.ofx
```
I hope this helps! Let me know if you have any other questions.
Source: Conversation with Bing, 11/24/2023
(1) csv2ofx · PyPI. https://pypi.org/project/csv2ofx/.
(2) GitHub - eholic/ofxer: CSV to OFX converter.. https://github.com/eholic/ofxer.
(3) Convert CSV to Excel using Python (example included). https://datatofish.com/csv-to-excel-python/.
(4) GitHub - reubano/csv2ofx: A Python library and command line tool for .... https://github.com/reubano/csv2ofx.
Cal,
ReplyDeleteregarding "What are the Schwab users doing? Move to Fidelity or Vanguard,?"
In my case, I have transferred a good majority of my investments to Fidelity, especially those that involve monthly transactions. I don't mind manually entering semi-annual transactions, so those have remained at Schwab for now.
Same. If a firm stops offering OFX downloads (either directly or via the website), I simply transfer all of my assets to a firm that has OFX downloads (I also moved to Fidelity). That's how a business will know that OFX downloads are important to some of their customers.
DeleteIt seems that this morning Schwab offers JSON export (and XML) in addition to CSV. That seems much closer to OFX, and might be easier to convert. The date range is preset for the last 12 months.
ReplyDeleteI reviewed the Schwab downloads in CSV, JSON, and XML formats. They all have the exact same transaction data (no unique transaction IDs, just basic date, action type, symbol, etc). The JSON and XML files also have a header that includes the date range covered and total transaction amount.
DeleteHello, I'm upgrading from an 8-yr old Win10 PC to a new Win11 machine. I installed Python 2.7.18 (Windows x86-64 MSI installer from python.org), DL'd the PS zip file, and installed Money. But when I run setup.py, a window flashes briefly then closes. The fresh sites.dat file is not created. Python appears to include pip.
ReplyDeleteDid you install the REQUESTS package? From cmd prompt:
Deletepip install requests
I did, both from the Python27 folder and from C:. Each time I got 'pip' is not recognized as an internal or external command, operable program or batch file.
DeleteI suspect you didn't select the "add python to path" option during install.
DeleteThanks for your help, but I must still have something wrong. I re-installed P2.7 and checked the Add Python to Path option, then did 'pip install requests' from CMD. Upon running setup.py, the window still flashes up and then closes.
ReplyDeleteTest by running from a cmd prompt (in the script folder) to see "startup/compiler" errors. Example:
Deletepython setup.py
Did you copy over your sites.dat file from old computer? Try copying entire pocketsense folder from old computer to new computer and running from there
DeleteRobert, we're on to something: I couldn't open CMD until I ran it as an administrator. Once I did that and I navigated to the pocketsense folder, setup ran successfully. I added my Fidelity account, and getdata also ran successfully.
DeleteBut I still can't run getdata from the desktop. This may be related to another issue I've noted on this new PC build: whenever I move/copy/delete files, it asks me for administrator permission. I've been googling this issue but no luck yet.
USAA Again: Starting to have a problem with USAA downloads.
ReplyDeleteError message in the returned OFX file reads:
We're unable to complete your request. To download USAA account transactions into Quicken, you'll need to verify your information. Get Access ID and PIN here https://df3cx-services.1fsapi.com/casm/usaa/enroll
I had authorized Quicken quite a while ago, and it still shows as being an authorized app on my Security page
When I try to navigate to that site, the browser shows that it redirected to:
https://api.usaa.com/auth/oauth/authorize?client_id=xxxxx&redirect_uri=https://df3cx-services.1fsapi.com/casm/usaa/connect&scope=usaa.profile.member.read+usaa.bank.aggregation.read&response_type=code&code_challenge=my_challenge&state=my_state&code_challenge_method=plain
and the return is:
{
"error": "invalid_request",
"error_description": "Validation error"
}
What am I doing incorrectly?
Thanks.
Ron
Additional information
DeleteSites.Dat
SiteName : USAA
AcctType : BASTMT
fiorg : USAA Federal Savings Bank
fid : 67811
url : https://df3cx-services.1fsapi.com/casm/usaa/access.ofx
bankid : 314074269
brokerid :
ofxVer : 103
appid : QMOFX
appver : 2300
mininterval :
timeOffset :
delay :
skipZeroTrans:
userAgent : InetClntApp/3.0
dtacctup : 19900101
clientUID : xxxxx 'same as in the redirected URL above`
SiteName : USAA CC
AcctType : CCSTMT
fiorg : USAA Federal Savings Bank
fid : 67811
url : https://df3cx-services.1fsapi.com/casm/usaa/access.ofx
bankid : 314074269
brokerid :
ofxVer : 103
appid : QMOFX
appver : 2300
mininterval :
timeOffset :
delay :
skipZeroTrans:
userAgent : InetClntApp/3.0
dtacctup : 19900101
clientUID : xxxxx `same as in the redirected URL above`
Pocket Sense version: From 18Aug2021 (I think) since I do not need the Quotes.
Ron: I suggest you get rid of 2300 and instead adjust tha tline to
Delete"appver : #blank means take default from control2.py"
The current default from control2.py is 2800.
Cal Learner
I'm experiencing the same behavior. Hopefully just a temporary server issue. Wait a few days and try again.
DeleteAndrew,
DeleteI hope it is temporary.
Looking at the Connected Apps tab on my Security page, it seems my last successful login was on 6-Dec.
Ron
Cal & Andrew
DeleteThis may be related: published 9/29/23 on the quicken web site:
https://www.quicken.com/support/why-am-i-receiving-message-about-my-usaa-accounts
Cal,
ReplyDeleteI don't know why my response to you earlier is not showing up, but I tried your suggestion.
With a blank or 2800 PS returns an html page which, when rendered, shows an 500 error code and an option to go to "home page" which is www.usaa.com
With the 2300, I get the same invalid OFX as above
It does look like there is some issue with the USAA/Quicken authentication page. Sadly, most of the CSRs at USAA are usually kept in the dark regarding ofx issues. If anyone here happens to have some info as to what's going with USAA's ofx authentication server, please post it here. I plan to check the Moneydance forums as well since they are also probably getting complaints from their customers regarding their software not connecting to USAA either.
ReplyDeleteJust a quick note regarding the Quicken page. They reference something called the "EWC+ connection method". I'm not sure if this refers to the recent change earlier this year or if they are switching yet again. Since the page has no date, it's entirely possible this could be referring to the recent change that took place this past summer. If this is the case, I'm hoping it's just a server issue that needs to be fixed.
ReplyDeleteCapt_Ahab,
DeleteAt the end of the article: "Last updated: 09/29/2023"
My bad! I didn't scroll to the end of the page and then I realized that the OP had also mentioned the date in their post as well. Hopefully this doesn't mean that PocketSense won't work from this point forward.
DeleteI don't use USAA, but curious if they offer manual ofx or csv download?
ReplyDeleteThey do offer .csv downloads but it will take that much longer to get my bank info into MS Money. I guess USAA is determined to make sure their customers are only using Quicken if they want convenient transaction updates in their application.
DeleteCurrent CEO of USAA has run the company into the ground. This was a great company to do business with. Now it's a shell of its former self. This is another bone-headed decision itappears.
ReplyDeleteActually, I am just starting to realize that USAA is in turmoil from the inside-out. I was a bit dismayed when my car insurance premiums went up 70% with no reason and now, all of a sudden, USAA just cuts off free access to their OFX server. Apparently, the new system they deployed requires you to pay a recurring cost through Plaid (according to Moneydance). I wonder if this move is a money grab where they get a residual cut from access fees.
DeleteI filed a complaint with USAA to voice my opposition to them discontinuing support for Direct Connect. I was told that the switch to EWC+ was done to enhance security for their members. That being said, can anyone recommend a good bank (or credit union) that continues to support the Direct Connect standard?
ReplyDeleteFidelity, PNC
DeleteI switched to TD Bank, as they continue to support OFX and have multiple physical branches (in New England at least) with friendly staff, and also a responsive (human) customer service phone response system.
DeleteThank you for the information! Would you happen to have the latest OFX connection info? I checked some resources online and can't seem to reach any of the listed "OFX server" addresses.
DeleteCapt_Ahab: Any? Can't reach?
Deletehttps://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/ has a nice list of descriptors for your sites.dat file.
Cal Learner
My apologies! I was looking for a working OFX server for PNY bank. Sorry I should have specified this in my last email. When I clicked the link https://www.oasis.cfree.com/4501.ofxgp for them, the website loads with the following message "Unknown error or not supported!". Hopefully, it's just on account that it's not designed for a web browser. I was hoping to make sure that the OFX server works before I sign up with them.
DeleteIt is normal that the URL listed in the sites.dat descriptors will not return a web page, but rather will return some kind of error indication. Those ports are expecting an OFX request.
DeletePocketSense sends an OFX request.
Cal Learner
I was hoping to get the connect info for PNY but after signing up for their virtual wallet, I am really underwhelmed with the way they operate. Transfers take several days to process, they have a monthly limit of just $2500 for mobile deposits and they charge a percentage fee if you want the money right away. As a customer of USAA for such a long time, I'm shocked at how predatory this bank is with your own money. At this point, I am hoping someone can recommend a good CU that has direct connect. Thanks again!
DeleteIt seems like Service CU may be a good alternative but when I look at the OFX Home site to check the details, it shows under OFX that it passed but under SSL it reads "Fail: SSL certificate missing issuer! Last validated on November 10th, 2020". Does this mean DC will work but the SSL certificate is out of date?
DeleteWith the impending death of Mint.com (and therefore MintAPI which has previously been discussed on this site as an alternative/companion to Pocketsense), I have created a very similar downloading solution using the Bank of America "My Financial picture" feature. BoA provides a more or less unknown aggregator service through Yodlee/Envestnet (as opposed to Plaid.com) which allows you to aggregate all your transactions for both BoA and external accounts and download as csv files. Converting the resulting transaction file to the ofx format isn't all that challenging, and I have successfully created a simple python script that can live alongside Pocketsense and process any supported checking/savings/moneymarket/creditcard account automatically (and add it to the Pocketsense import folder).
ReplyDeleteIf anyone is interested in trying this out, please reach out at djevel at live dot com, and I'll send you the code (in clear text) and instructions for how to auto-download from the Bank of America site for any accounts where direct connect is no longer supported (through Pocketsense or otherwise).
Caveats:
1) You have to be (or willing to become) a Bank of America customer (either through a bank account or a credit card...or perhaps even through an investment account (Merrill Edge or Merrill Lynch). This probably means that the solution is limited to US residents. Note however, that the code can probably be repurposed for any other aggregator out there.
2) You have to be ok with letting Bank of America (and Yodlee/Envestnet) aggregate your external accounts at Bank of America. It is completely "free", but you will obviously be sharing your transaction/balance data with them, which may be a non-starter for some.
3) You (likely) have to install python3 alongside your Pocketsense 2.7 installation. This shouldn't pose much of a problem, but if you want to stay on 2.7 I can't guarantee that the code in question will work (it has not been tested).
4) For browser automation (hand-free auto-download of files from BoA) you would have to be willing to install Selenium, but if you're willing to download files manually from Bank of America instead, then this is not a requirement.
djevel,
DeleteLike I did for Cal (see https://microsoftmoneyoffline.wordpress.com/2018/11/28/how-to-use-money-to-find-losing-tax-lots-to-harvest/ ), I'd be happy to re-publish any write-up you have in my blog. Send to danadkins at msn dot com
So your findings "process any supported checking/savings/moneymarket/creditcard account automatically (and add it to the Pocketsense import folder)." means that this doesn't include investment accounts, right?
Thanks NxtTek. I'll formalize my writeup and send it over to you sometime this week.
DeleteWhile investment accounts can be included, the aggregation only includes transactions in the cash-part of the account. So dividends, transfers and even purchases can be captured, but the accompanying investment transaction would have to be manually entered, so it may be of limited usefulness to many. Since the ofx structure for these types of accounts is completely different than for bank/creditcard accounts I have not yet included support for this in my python converter, but this functionality is indeed on the "roadmap".
I am noting that Cal has started an effort to capture investment accounts through provider-specific csvs which is likely going to be a better solution long term, but if someone only wants to capture, say, non-reinvested dividends, then I'll have support for that (at some point).
I published djevel's nice writeup today @ https://microsoftmoneyoffline.wordpress.com/2024/01/19/using-boas-aggregator/
DeleteOdd Problem with TD Bank:
ReplyDeleteFor a few months now, I have noted that when I download my savings account transactions from TD bank using PocketSense, the interest credited transaction is repeated four times. In the OFX file there are four separate entries with different FITID's. These repeats do not happen if I choose to export an ofx (or qfx) file from their website.
Thought I would check here before going to TD Bank tech support.
I'm wondering if there could be anything in Pocket Sense or in my sites.dat entry for TD Bank that might be causing that problem. Other TD Bank accounts (checking and credit card) do not show transaction repeats.
```
SiteName : TD Bank
AcctType : BASTMT
fiorg : CommerceBank
fid : 1002
url : https://ofx.tdbank.com/eftxweb/td.ofx
bankid : 211274450
brokerid :
ofxVer :103
appid : QWIN
appver : 2700
mininterval :
timeOffset :
delay :
skipZeroTrans:
userAgent :
dtacctup :
clientUID :
```
Thank you for any insight.
I would suggest using setup next month to test and grab the ofx file. then double click the ofx file and see what happens. if you only see interest credited once, I would suspect a scrub routine that alters the fid, like the Discover routine does.
ReplyDeleteThank you for your suggestions.
Delete1. But why wait until next month? The transactions will be downloaded for, I believe, 21 days after they take place.
2. The FID doesn't change.
3. I have downloads that invoke a scrubber -- eg Fidelity and AT&T, and they both say so on the `Get Data` screen. There is no such comment under the `TD Bank` downloads, nor do I see a TD Bank scrubber in my folder
4. In any event, I went into Setup and tested the relevant account. The FID was unchanged, but there were, as before, five (5) Interest credit items, identical except for the FITID's.
I am looking for the address and related info for the TD Bank (Canada) OFX server so I can try out PocketSense. I have seen here an address for a TD Bank server referenced by Cal but my accounts are in Canada. Thanks
ReplyDeleteI contacted a technical manager at TD Bank in Canada and he did not know about ofx servers at TD Bank. I also checked the OFX forum (ofxhome.com) and read several unanswered posts about ofx usage in Canada -- it is not in use.
DeletePocketSense does not seem to be useable for investment accounts at Canadian banks.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteScrubber Help:
ReplyDeleteI'm wondering if this could be aided by a scrubber routine.
I mentioned this above but I have more information.
The problem is duplicate entries only in my TD Bank Savings account (not in TD Bank checking or credit card account).
It seems similar to the Discover problem Robert found when he first set things up, but I don't think the same fix will work.
The entries are identical except for the FITID numbers. A representative FITID would be:
`20240123000008656702`
where the first 8 digits are the date of posting in `yyyymmdd` format, and the rest appear to be a sequential value.
The following FITID's were assigned to two different transactions in this particular download:
```
20240123000008656701
20240123000008656702
20240123000008656703
20240123000008656704
20240123000008656705
20231231000000342781
20231231000000342782
20231231000000342783
20231231000000342784
20231231000000342785
```
I'm not even sure how to clean this up.
If I repeat the download on the same day, the FITID's will be the same. I haven't checked it on a different day.
So two transactions with ten different FITID's.
It is possible in this account to have two identical transactions on the same date, but unlikely.
Any thoughts?
The first thought that comes to mind is to chop off the last digit, but only if payee and/or amount are duplicates. Then only one transaction will be processed by Money, as the remaining 4 will be repeats.
ReplyDeleteThat is a good thought. And I just checked today -- it seems that the same transaction will have the same FITID even on different days. I suppose an issue could arise if there were identical transactions on the same day, and the FITID's are assigned sequentially for those two transactions.
DeleteThis comment has been removed by the author.
ReplyDeleteEverybody click "Load more..." for post 201 and beyond.
ReplyDeleteOn this blog, only the first 200 comments on a thread unless you click "Load more..."
Ideally I should have made this the 200th comment.
I try to minimize my number of posts to keep from getting to that limit too soon. You will find that people often miss the additional posts, and Robert often creates a new thread to mainly avoid confusing those who don't know about "Load more..."
Ron, NexTxt's method is inspired.
Maybe use re.sub and Regex Lookbehind.
#change ALL FITID 20th digits into a 1.
ofx=re.sub( "(?<=\d{19})\d","1",ofx)
Would that work, or are there other non-duplicate FITIDs that you would not like to do that with.
You could get fancier.
I only tested in PDB.
(Pdb) ofx="blah20231231000000342782 more stuff"
(Pdb) re.sub( "(?<=\d{19})\d","1",ofx)
'blah20231231000000342781 more stuff'
(Pdb)
Cal Learner
Everybody click "Load more..." for post 201 and beyond.
ReplyDeleteOn this blog, only the first 200 comments on a thread unless you click "Load more..."
I screwed up on my previous post, and forgot about the less than and greater than signs disappearing. So here is the corrected version.
I try to minimize my number of posts to keep from getting to that limit too soon. You will find that people often miss the additional posts, and Robert often creates a new thread to mainly avoid confusing those who don't know about "Load more..."
Ron, NexTxt's method is inspired.
Maybe use re.sub and Regex Lookbehind.
#change ALL FITID 20th digits into a 1.
ofx=re.sub( "(?<=<FITID>\d{19})\d","1",ofx)
Would that work, or are there other non-duplicate FITIDs that you would not like to do that with.
You could get fancier.
I only tested in PDB.
(Pdb) ofx="blah<FITID>20231231000000342782 more stuff"
(Pdb) re.sub( "(?<=<FITID>\d{19})\d","1",ofx)
'blah<FITID>20231231000000342781 more stuff'
(Pdb)
Cal Learner