Schwab support broken

[rvernica]

It seems that the schwab and schwabbank endpoints broke. My setup used to work fine. Now, when I do ofxget stmt schwab I get:

Traceback (most recent call last):
  File "/.local/bin/ofxget", line 8, in <module>
    sys.exit(main())
  File "/.local/lib/python3.8/site-packages/ofxtools/scripts/ofxget.py", line 1549, in main
    REQUEST_HANDLERS[args["request"]](args)
  File "/.local/lib/python3.8/site-packages/ofxtools/scripts/ofxget.py", line 690, in request_stmt
    with client.request_statements(
  File "/.local/lib/python3.8/site-packages/ofxtools/Client.py", line 334, in request_statements
    return self.download(ofx, dryrun=dryrun, verify_ssl=verify_ssl, timeout=timeout)
  File "/.local/lib/python3.8/site-packages/ofxtools/Client.py", line 590, in download
    response = urllib_request.urlopen(req, timeout=timeout, context=ssl_context)
  File "/usr/lib64/python3.8/urllib/request.py", line 222, in urlopen
    return opener.open(url, data, timeout)
  File "/usr/lib64/python3.8/urllib/request.py", line 531, in open
    response = meth(req, response)
  File "/usr/lib64/python3.8/urllib/request.py", line 640, in http_response
    response = self.parent.error(
  File "/usr/lib64/python3.8/urllib/request.py", line 569, in error
    return self._call_chain(*args)
  File "/usr/lib64/python3.8/urllib/request.py", line 502, in _call_chain
    result = func(*args)
  File "/usr/lib64/python3.8/urllib/request.py", line 649, in http_error_default
    raise HTTPError(req.full_url, code, msg, hdrs, fp)
urllib.error.HTTPError: HTTP Error 403: Forbidden

I'm using Python 3 and ofxtools 0.8.22

[csingley]

HTTP 403 is kind of a bitch! This isn't coming from my code, but from Schwab's server.

Most likely is that they've upgraded their OFX server (provided by a 3rd party vendor e.g. FiServ), which is now being stricter about detected clients allowed from some whitelist. The name of the game is to spoof Quicken.

The easiest thing to do is to modify ofxget configs for APPID/APPVER... by default it should be sending QWIN 2700, which AFAIK is what's sent by currently supported versions of Quicken for Windows. However, it's possible that the new owners have changed that in their drive to force all users to annual subscription licensing. If so, it should be easy to spoof the current settings.

Another possibility (perhaps more likely, b/c the server threw an HTTP error not an OFX error) is that the server is checking the HTTP User-Agent header, and we're not matching whatever Quicken sends. I just leave this header blank; I don't know what Quicken does. Unfortunately this is more difficult to check; it requires setting up a proxy to MITM a running version of Quicken... which I don't have.

It is annoying. A couple other FIs that used to work (Vanguard for one) have recently started exhibiting this kind of behavior.

[skontrolle]

I was optimistic about trying to figure out the User-Agent of Quicken but I'm not sure that's going to fix it. Going from the curl User-Agent to None fixed my connectivity issues a year or more ago for my own script. I had a quick look at the quicken interface which now requires an online account to do anything. It's possible it can still send things directly for a MITM with an existing account but I suspect it's equally likely they could act as a proxy to Schwab to use their servers under a whitelist or using a token.

Digging a bit more over the week, it looks like Schwab is using an OAuth framework now and asking developers to register. http://www.ofxhome.com/ofxforum/viewtopic.php?id=49841 https://developer.schwab.com/home This would explain why the anonymous queries we're used to aren't working anymore. I do like that they're attempting to improve on the security in spite of my account info getting stale.

Lastly, there appear to be other OFX users left out from the changes and there was some notice to the YNAB developers. Is there any indication vanguard is doing something similar? https://www.reddit.com/r/ynab/comments/jesiw5/schwab_and_ynab_plaid/

[csingley]

I believe Schwab's OAuth framework is unrelated to this issue - it's aimed at the plethora of services like Plaid or YNAB who don't authenticate through OFX. They're saying "For the love of God stop roboscraping our website; let's set up proper API access". Quicken, which has a near-monopoly on the OFX interface, does not use OAuth at all.

My goal for ofxtools is that, if Quicken can get download data through an OFX interface, then we should be able to as well.

I don't know about Schwab; I don't have data.... but ofxtools users have provided Quicken logs of successful OFX profile requests to Vanguard. I match the same request, and it fails. If you look at the OFX spec, really the only place to discriminate is in the HTTP wrapper. Apparently Vanguard at least is starting to do that; Schwab may be the same. This would be explained by, for instance, a 3rd-party vendor like FiServ pushing an OFX server upgrade that got picked up by both brokers.

HTTP wrapper isn't captured by Quicken logs, unfortunately. It will be necessary to have a host with Quicken installed, configure its internet access through a proxy, then MITM the proxy to dump the HTTP request.

I would be very interested to see the HTTP headers sent by Quicken. I set ofxtools User-Agent to None years ago, and it's been working fine for pretty much everybody until recently, except for some FIs like Capital One going dark on OFX because they don't like the security situation.

[davraamides]

I'm having the same problem with American Express (403 Forbidden). It started happening around Nov 14th 2020. I can't find any information on their site about a change.

[csingley]

This issue has some information about Amex. If I patch ofxtools.Client.OFXClient.http_headers as mentioned in that ticket (i.e. change "Accept" from */*, application/x-ofx to text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8) then it starts working for me.

Unfortunately, this doesn't seem to work for Schwab.

[csingley]

Discussion of Amex problems & fixes split off into a separate issue.

Still not sure what the hell is up with Chucky Schwab.

[q0rban]

On a related note, Schwab OFX download has stopped working in other budgeting apps. I opened a support request with one particular app, and received this response:

Schwab have recently made significant changes to the way they offer connections and we were sent a link to this press release from them by another Schwab customer:

https://pressroom.aboutschwab.com/press-releases/press-release/2020/Charles-Schwab-Reinforces-Its-Commitment-to-Customer-Data-Protection/default.aspx

It seems that Schwab have signed an agreement with three specific (huge) companies to enable other companies to provide customers to access their data, possibly in part to avoid having to deal with many small software companies such as us.

For [us] (or any other personal finance software) to now be able to access customer data for Schwab customers, we'd have to sign up with one of those organisations - in our case Envestnet | Yodlee - to access that data. In the shorter term, you can still import your transactions by downloading an OFX file from your online banking and importing directly.

And so here I am, trying to see if I can use ofxget to download OFX data from Schwab to import into my budgeting software.

[csingley]

Has Schwab turned off Quicken downloads?

I'm not concerned about Yodlee or the like. I am just interested in piling up 3 monkeys in a trenchcoat & passing them off as Quicken.

I'm looking for confirmation that Quicken can connect to Schwab, but ofxtools cannot.

[q0rban]

I can download Quicken to try… and maybe I can put wireshark or mitmproxy in there to see what requests Quicken is making if it works

[csingley]

Quicken's new owners are all about those subscriptions... they've disabled OFX download capability for older (purchased rather than rented) versions of Quicken. They do have a 30-day money back guarantee, but still...

If you do ascend into a dudgeon sufficient to motivate you to MITM a copy of Quicken, drop me a line! Or if you talk to any Quicken users who are currenty using Direct Connect to import their Schwab transactions, I'd be interested to know about that as well.

But it's not all that bad to download OFX data manually from FI websites. I mean, you pretty much have to go there anyway to download statement PDFs.

[q0rban]

I did confirm that Quicken can download Schwab statements. (30-day money-back, here I come).

I captured some packets, and it seems like Quicken first connects service.quicken.com and then to ofx.schwab.com, then back to service.quicken.com. I need to dig further, but I'm wondering if it's doing an OAuth connection?

I was thinking I could do a mitm, but given this is all TLS traffic, I don't think I can?

But it's not all that bad to download OFX data manually from FI websites. I mean, you pretty much have to go there anyway to download statement PDFs.

Unfortunately, Schwab doesn't provide OFX downloads from their website. 🤦‍♂️

[csingley]

The dudgeon rises! Good on ya.

Before going too far down the rabbit hole, it's worth getting Quicken logs (I think available under the Help submenu) which should capture application-layer traffic to Schwab. Compare Quicken's PROFRQ to our PROFRQ and see if it's a formatting issue that can be handled with a simple change to ofxget.cfg or similar.

Failing that...

I was thinking I could do a mitm, but given this is all TLS traffic, I don't think I can?

You can; at least one ofxtools user has already done so. The transport-layer encryption will prevent you from snooping the packets with e.g wireshark. What you need to do is to set up a proxy such as mitmproxy on another host, and then set the IP configs for the host running Quicken to access the internet via that proxy. If you don't have a bunch of machines lying around, you can:

1) install mitmproxy on a machine 2) install VMware or somesuch 3) install Quicken inside a VM 4) set the VM's IP config to use the machine running the VM as a gateway (i.e. no transparent bridging etc.), on the IP address where mitmproxy is listening.

[PrplHaz4]

You can; at least one ofxtools user has already done so. The transport-layer encryption will prevent you from snooping the packets with e.g wireshark. What you need to do is to set up a proxy such as mitmproxy on another host, and then set the IP configs for the host running Quicken to access the internet via that proxy. If you don't have a bunch of machines lying around, you can:

1. install mitmproxy on a machine

2. install VMware or somesuch

3. install Quicken inside a VM

4. set the VM's IP config to use the machine running the VM as a gateway (i.e. no transparent bridging etc.), on the IP address where mitmproxy is listening.

CharlesProxy may be a little more user-friendly than mitmproxy - I've used it for mobile apps in the past - it has a gui, can install locally on win/mac/linux, and can proxy and inspect http/s traffic. Looking forward to seeing this dismantled.

[q0rban]

Ok, so I got CharlesProxy working… Here's the interesting thing. Quicken makes a request to ofx.schwab.com, which 403s. It then seems to do all of its OFX requests to services.quicken.com.

[q0rban]

[csingley]

"OFX Secure Plus"... public key management system... just ducky.

What exactly is being POSTed to services.quicken.com/ofx-secure-plus/10655 either time? Let me guess... encrypted alphabet soup.

Is there anywhere where the proxy can snoop an *RQ or *RS, or is the payload all end-to-end encrypted?

[csingley]

Here are some people on the Quicken forum struggling with a similar issue for Vanguard. N.B. this is not at all how we recently restored connectivity to Vanguard.

[q0rban]

Both requests have a Bearer token and the following User-Agent.

User-Agent | InetClntApp/3.0

If I'm reading this correct, here's the payload of the requests (not the responses):

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
<SIGNONMSGSRQV1>
<SONRQ>
<DTCLIENT>20201201180216.392[-5:EST]
<USERID>anonymous00000000000000000000000
<USERPASS>anonymous00000000000000000000000
<GENUSERKEY>N
<LANGUAGE>ENG
<FI>
<ORG>Intuit
<FID>8886
</FI>
<APPID>QMOFX
<APPVER>2300
</SONRQ>
</SIGNONMSGSRQV1>
<PROFMSGSRQV1>
<PROFTRNRQ>
<TRNUID>[redacted uuid?]
<PROFRQ>
<CLIENTROUTING>MSGSET
<DTPROFUP>19900101
</PROFRQ>
</PROFTRNRQ> 
</PROFMSGSRQV1>
</OFX>

The second:

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
<SIGNONMSGSRQV1>
<SONRQ>
<DTCLIENT>20201201180217.542[-5:EST]
<USERID>[redacted]
<USERPASS>[redacted]
<GENUSERKEY>N
<LANGUAGE>ENG
<FI>
<ORG>Intuit
<FID>8886
</FI>
<APPID>QMOFX
<APPVER>2300
</SONRQ>
</SIGNONMSGSRQV1>
<SIGNUPMSGSRQV1>
<ACCTINFOTRNRQ>
<TRNUID>[redacted]
<ACCTINFORQ>
<DTACCTUP>19900101
</ACCTINFORQ>
</ACCTINFOTRNRQ> 
</SIGNUPMSGSRQV1>
</OFX>

[csingley]

OK, thanks.

So at least it's not e2e encrypted. Bad news is (a) it is indeed an OAuth type protocol at work here, as people were trying to tell me above, and (b) the server request/response is proxied through Quicken itself.

This ain't gonna work, is it? At the end of the day, I really can only maintain an Open Financial Exchange protocol library.

I'm sure I'm an outlier, but I have in fact had quality of data downloads factor largely in my decision to switch financial institutions. TD Ameritrade doesn't do any of this horseshit, but still stands up an entirely reasonable liability limitation. Interactive Brokers offers much higher quality data downloads in a proper XML format... I've in fact written a Python library for that too, you may be unsurprised to learn.

I know it's not a satisfactory response to your entirely admirable efforts here, but I've just become less & less willing to pay FIs who offer crappy service.

Anybody got any bright ideas here?

[q0rban]

This ain't gonna work, is it? At the end of the day, I really can only maintain an Open Financial Exchange protocol library.

It seems that way. I don't blame you! Thank you for the help and consideration.

I'm sure I'm an outlier, but I have in fact had quality of data downloads factor largely in my decision to switch financial institutions.

I did the same thing recently, and unfortunately had the rug pulled out from me again, this time by Schwab.

Thanks again for all the work on this!

[skontrolle]

Don't throw the baby out with the bathwater. There are many more significant reasons to pick a brokerage than OFX access and I suspect many other companies will be following or carving out a more convoluted route. I certainly don't have any level of trust for quicken or the other proxies so I'm quite keen on figuring out what can be done.

With the 403, it's possible there might still be a whitelist possibility if their support doesn't deflect directly to the api service.

Schwab's notices imply that there's a more granular access control within the account settings but I can't find anything new on the internal side and the security settings still have OFX access enabled/disabled. For Oauth, I would expect some kind of web-self service access since they imply you don't have to give the 3rd parties access to your account anymore (which doesn't exactly seem true from the above).

[csingley]

@q0rban - since you've gone to the trouble to hax0r Quicken, I don't suppose you're interested in extracting a bit more value from your free trial? I don't know if it's feasible, but the database of FI connection information (originally sourced from MS Money) could really use a refresh. ofxtools.__author_email__ if interested (or the git log, or the PyPI project page... I'm not hard to find).

@skontrolle - if you need to book lots of transactions every month, it might change your perspective on the relative importance of data downloads. You might even wind up spending many years building a software library or two to help extract this particular baby from the bathwater...

Just to reiterate, though... the core purpose of the ofxtools library is to parse OFX data into Python data structures, to facilitate DIY financial software. The OFX client code hosted here, and especially all the jank needed to make it work with your particular FI and their heinous protocol violations, are explicitly noncore... I personally don't need to use this stuff at all. I realize there's a need for users of GnuCash et al. to get hold of their data, and I don't mind helping out; free software has been good to me.

But it really needs to be in the OFX specification, or immediately spec-adjacent. If Quicken has decided to cement their monopoly by wrapping up OFX in a proprietary transport layer, and your service providers are buying into this scheme... y'all are gonna need to write your own library to deal with all that tasty goodness. Feel free to use ofxtools to read the actual data once you've liberated it from the Forces of Wack.

[skontrolle]

I certainly see the value in automatically booking the transactions but their approach to transaction fees, account servicing, and having a history of these long outlive my use of gnucash and OFX.

It's clearly not an ofxtools issue and I'm very grateful for your approach so far. I'm even more happy to have found a group interested in the same problems I'm having with them. Their developer site seems to imply they're moving to the FDX DDA which seems to be another one of those pay based on size formats.

[csingley]

I certainly see the value in automatically booking the transactions but their approach to transaction fees, account servicing, and having a history of these long outlive my use of gnucash and OFX.

All brokers suck in some regard; you pick your poison. I probably just have Stockholm syndrome from putting up with IBKR's concept of customer service for 20+ years.

@aclindsa - I hear the roar of the masses calling out for you to inaugurate an fdxtools library. Don't look at me; I'll be over here working on ibflex.

[aclindsa]

@aclindsa - I hear the roar of the masses calling out for you to inaugurate an fdxtools library.

Hah! I did actually manage to obtain access to the API, but now have absolutely no idea how to determine which banks implement it or how to find their URLs... Does that sound familiar?

[rvernica]

Do we have a critical mass to make a coordinated effort to inform Schwab (e.g. open letter, petition) that OFX support is something critical for us? We could broadcast our effort on the GnuCash Mailing List, OFX Home Forum, and YNAB Reddit and maybe get additional support.

[csingley]

You might be able to pick up a few more in the plaintext accounting (i.e. ledger/hledger/beancount) communities.

[q0rban]

I don't know if it's feasible, but the database of FI connection information (originally sourced from MS Money) could really use a refresh.

Does this get you what you need? https://download.fidir.intuit.com/qm2400/data/xmlonline.zip

[aclindsa]

@q0rban Well, that's nifty!

That does appear to show at least some entries for Schwab as DIRECT, presumably meaning "direct connect". Though I also see EXP-WEB-CONNECT, so I suppose it's possible they're falling back on that if/when DIRECT doesn't work.

[csingley]

https://download.fidir.intuit.com/qm2400/data/xmlonline.zip

That's cool! I'll have to scrutinize it in more detail, but it doesn't seem to have what we need.

To make a connection, we need:

1. URL of OFX endpoint
2. ORG
3. FID
4. (as appropriate) BROKERID

In the ZIP archive above, the list of FIs is in fidir.txt, given as TSV. The given values seem to be Quicken FI identifiers (sometimes but not always to FID; e.g. Schwab's FID of "08888" appears nowhere in the file), a link to the main website, phone#, URL to signup for Quicken delivery, and OFX capabilities.

The Quicken FI identifier can be used to query the XML file (XPath baby!) to get Quicken's config for that FI, e.g. text to fill in GUI templates, how to handle investment income reinvestment... even instructions for how to parse stock splits.

This information can be very useful for people trying to figure out e.g. they need to use their USAA member# to log on, but it doesn't give us the info we need to drop into fi.cfg or ofxget.cfg.

[rvernica]

Just for completeness, here is the response from Schwab:

As of market close on October 16, 2020, Schwab restricted access to its legacy OFX (ofx.schwab.com) for all third parties except for: Quicken (personal finance management), TurboTax, and H&R Block (for tax preparation). This step is being taken to secure our clients' data.

Known impacted parties include Sigfig, Gainskeeper, Plaid, and Quovo, though this list is subject to change as we continue to work with them to sign Data Access Agreements. Known third parties have been notified both via conversations as well as a series of emails earlier this year (beginning in January of 2020) about the OFX restriction going live effective October 16, 2020. There are also many unknown parties that access Schwab's OFX endpoint, including clients who have written scripts on their personal computers to connect to OFX. With the initiation of the OFX restriction, these parties will be blocked from accessing Schwab OFX data going forward.

Third-parties who have historically used OFX to aggregate Schwab client data are encouraged to register for access to the Retail Account Aggregation API at developer.schwab.com . Registration on this website is not meant for clients, but for third-parties who provide software solutions/apps for Retail Account Aggregation.

[aclindsa]

Yikes. So now you have to manage to register both at https://financialdataexchange.org/ and at https://developer.schwab.com/home as a 'developer' to have any hope of getting programmatic access to your own data. I certainly don't love the current state of OFX, but it seems like it might be better than what is coming next...

[aclindsa]

I came across this morning that Moneydance supposedly has Schwab support working again: https://infinitekind.tenderapp.com/discussions/problems/63978-moneydance-stopped-downloading-transactions-from-charles-schwab

[aclindsa]

I came across this morning that Moneydance supposedly has Schwab support working again: https://infinitekind.tenderapp.com/discussions/problems/63978-moneydance-stopped-downloading-transactions-from-charles-schwab

Ah, seems like they may be proxying the requests, and have specified their own app_id and app_ver - see https://infinitekind.com/app/md/fi2004.dict. I wonder if Schwab allowed them to register for OFX access and tie a particular IP/app_id/app_ver combination together and allowing access for it or something.

[rvernica]

Schwab UI allows exporting the transaction data in CSV format. Is there a script to convert a Schwab CSV file to OFX?

[csingley]

Is there a script to convert a Schwab CSV file to OFX?

I've seen several similar types of scripts written by users of GnuCash, beancount, etc. I don't conceive it as a goal of ofxtools to develop a library of such scripts. OTOH, it is part of my mission to make it easy for everybody to write such scripts as needed. If you know how to parse a Schwab CSV file, it shouldn't be too difficult to use the data to generate an INVSTMTRS. The classes in ofxtools.models hopefully won't let you create any markup that is grossly malformed.

[csingley]

You could also have a look at this project. It seems to support investment transactions.

[csingley]

My h4x0r skillz are insufficiently 3l337 to penetrate Schwab's puissant security countermeasures. As such I'm closing this issue INVALID. The related discussion it generated is rather interesting; if desired it may be continued off the bug tracker on the newly created Discussions page.