What if the Fidelity OFX server goes away for PocketSense users?

[Cal Learner]

What if the Fidelity OFX server goes away for PocketSense users?

We enjoy PocketSense being able to get OFX files directly from Fidelity. There is a worry that Fidelity will close down the OFX server and to only allow this access via a third party.

If that happens, those who don't want to involve a third party, or want to continue to get transaction and positions info into Microsoft Money (or other application that accepts OFX files), would use a workaround. Ideally Fidelity would continue OFX acces, and the second best would be that they allow access via their new interface directly by an individual investor, and we figure out how to convert that to OFX.

The underlying info is available at Fidelity by downloading the Accounts_History*.csv and Portfolio_Positions.csv. I have not gotten far in that, because I wanted to gather some lookup data while it is available. I hope this was a waste of time, and the OFX servers will continue. Here is the plan:

Create a CSV file that has the TICKER as the first column of a line. Next is the CUSIP. Then the type of security (STOCK|MF|OPT|OTHER|DEBT). Next is the name of the security. Any further columns will vary according to type.

I am not sure what to name that database file. I have been calling it candidate.csv, but that is not a good name to continue. Maybe OFX_INFO.csv, OFX_look.csv, CUSIP_table.csv. What do you think?

I have mostly working code. The main exception is that I don't have any OFX files with options to test it on.

To invoke it, you have one parameter that can either be
 a. path ending in "\" ; to use the current folder, use ".\" as the param All *.ofx will be processed
 b. a single ofx, with a path that ends in .ofx. Not case-sensitive.
 
This is intended to build the symbol to CUSIP database. You could just save copies of a set of your fidelity*.ofx from the sfr\ folder. Then run this code later. Or do it now, and save the database now. The database grows, so if a new ticker is seen, it gets added in. This is good if you don't want to run this non-professional code until others have checked and blessed.

You can look at or modify the database with a spreadsheet program. It is important that the CUSIP colum (B in your spreadsheet) be treate as text, so it does not get confused with a number. Ideally they would all be treated as text, but from what I tried. ony the CUSIP column had a problem.

I intend to continue writing code, that uses this CSV database, to look at the most recent Fielity CSV files, and to write the OFX fileS... Yes FileS... I intend that the code take the CSV files that have info from more than one account, and generate an OFX for each of those accounts. But I work sporadically. And the Fidelity OFX server is still working great.

*********
Action items:
1. Save your fid*.ofx files to a storage folder for later analysis. That will insure you against sudden loss of function.
2. If you have suggestions on naming the saved global database file, offer your suggestions. If you have a suggestion for the Python program that reads your OFX files, and adds security info to the database. Code is tested on Python 2.7.10 or higher, but I have no reason to think it would not work the same on Python 3.x. PocketSense will not work with Python 3.x.
3. If you want to try out what I have so far, let me know. The only file it writes to so far that data base file.
4. If you find this post or content hard to understand, or you have questions, say so.

[Dan Adkins]

Thank you for sharing Cal.  I thought I would explain why I'm not volunteering - none of my investments at Fidelity have ticker symbols (only CUSIP), so I wouldn't be a good testing candidate.   If Fidelity eliminates their ofx server, I'll switch everything back to Schwab, close my Fidelity accounts, and enter transactions manually.

--
You received this message because you are subscribed to the Google Groups "Microsoft Money" group.
To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/b71d3752-7928-41e3-8169-a82101401b7an%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cal Learner]

Thanks. My stuff is not nearly ready for prime time, or even tertiary time,  anyway. 

Also, my current plan is that if that database does not exist, and if the TICKER is not a 9 character CUSIP-like string, to pad the ticker up to 9 characters with 'Z' characters. Incidental to my studies, I find that the CUSIP characters have some  meaning.  https://en.wikipedia.org/wiki/CUSIP https://www.investopedia.com/terms/c/cusipnumber.asp

If the user has no database, and a generated CUSIP replacement is used, for each security, Money will present the pop-up that asks if this is already known (select) or if it is new, and a new security will be generated.

Reminder that I think it is somewhat important to define MMFs in Money, rather than importing them for the first time. If Money does it during download, the security will be treated as a mutual fund. Not that bad, but in the Portfolio, you have the option to group by InvestmentType, and MoneyMarket is one of those types.

[Cal Learner]

If nothing has a non-CUSIP ticker so far,  you might look into Fidelity SPRXX and FZDXX, for cash that is waiting, for a while, to be used. If you buy something, those automatically get sold to provide cash to pay for your new Buy.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

Cal, I do have SPAXX, but remember - your great scrub routine removes all Buys and Sells, so only the dividends directed to the Cash account show up.  That MMF has no Quantity / Market value / Cost Basis data in my Portfolio Investments view, other than total dividends showing up in the Gain column. (Perfect!) 

[Ameridan (microsoftmoneyoffline.wordpress.com)]

SPAXX doesn't even show up on my OFX statement view.

[Cal Learner]

Right. SPAXX is your core position, and IMO should therefore be treated as cash. Incidental to that, I am considering turning core position dividends into interest, but that would be pretty far down the line.

What I was suggesting is that if you find yourself holding an amount in your core position for a time, that instead you consider explicitly buying a higher-yielding Fidelity MMF. While they don't get bought automatically, as your SPAXX does, they do get sold automatically. Not a big deal, since SPAXX yields about 4.24% -- remarkably good for a  sweep/core fund.  SPRXX would yield about 10 basis points more, an FXDXX about 22 basis points more. Worth the effort? Who is to say.

I am confident that your investment choices have done a lot better than mine for the past 3 years.

[Fur Rari]

Well, mangofox will actually do it for you -- it's similar to pepperfox but for brokerages.  Gets you nice .ofx files on my 4 Fidelity accounts.  But I haven't converted over to using them since Fidelity still has an OFX server.

I do use it for "JPMorgan Investments" since they aren't going to give you data in OFX format.

$ time $HOME/mangofox/bin/run
Saved 1013 transactions from Waterhouse in /fox/ofx/TDAmeritradeInc-xxxx-2023-03-19.ofx.
Saved 16 transactions from Fidelity Go in /fox/ofx/FidelityInvestments-xxxx-2023-03-19.ofx.
Saved 81 transactions from Fidelity Roth in /fox/ofx/FidelityInvestments-xxxx-2023-03-19.ofx.
Saved 16 transactions from Fidelity Rollover IRA in /fox/ofx/FidelityInvestments-xxxx-2023-03-19.ofx.
Saved 27 transactions from Fidelity Taxable in /fox/ofx/FidelityInvestments-xxxx-2023-03-19.ofx.
Saved 10 transactions from JPMorgan Investments in /fox/ofx/Chase-xxxx-2023-03-19.ofx.

real 0m07.09s
user 0m00.05s
sys 0m00.05s

[Cal Learner]

I am slowly making progress in spurts. 

Some things that I think I have learned

1.  <TRNUID> value gets ignored. I can reuse the same value, and the OFX file gets processed. 
2. <FITID> appears to  not be case sensitive.
   

These observations are based on experiments, but if somebody has come to different conclusions, my experiments could have been flawed.

[Fur Rari]

I just use “<TRNUID>0” for everyone.

 

If where I get the data doesn’t have a unique transaction ID I use something like ticker+time+shares or a hash of that.

--
You received this message because you are subscribed to a topic in the Google Groups "Microsoft Money" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/microsoft-money/3TzQSdfq79c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/efe8487c-b05c-4364-8585-7493cd4d1144n%40googlegroups.com.

[Cal Learner]

Does Money pay attention   to the <TRNTYPE> entries?

The Fidelity OFX files have DEP, PAYMENT, CASH, OTHER, CHECK, and maybe DEBIT types, but does Money care about the distinction?

[Fur Rari]

In some cases, at least, yes.  Only the CHECK type can have a <CHECKNUM>.

--

You received this message because you are subscribed to the Google Groups "Microsoft Money" group.

To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/58142346-8729-4c30-9b64-42170bbacbe0n%40googlegroups.com.

[Cal Learner]

I found https://studylib.net/doc/8522333/ofx-message-support-in-msn-money-and-microsoft

It says, in section 11.4.2.3.1that Money accepts all values of this tag and ignores this tag.

[Cal Learner]

In working thru stuff, I see that a T-bill I bought at Fidelity resulted in a <BUYDEBT> entry, and when that T-bill mature, I got a <SELLOTHER> for the same CUSIP. Any comment as to why this would be, or anything else? I expect it works fine, and I presume Money is not bothered by the asymmetry. 

[Dan Adkins]

Cal, I wrote an article on my favored method of recording T-bills.  It doesn't sound like the interest will be reported properly, but you can adjust the Sell transaction accordingly.

https://microsoftmoneyoffline.wordpress.com/2019/04/01/u-s-treasury-bill-transactions/

You received this message because you are subscribed to the Google Groups "Microsoft Money" group.

To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/2893c696-735a-4c26-9d93-9edc44ada449n%40googlegroups.com.

[Cal Learner]

Thanks, Ameridan. I am not sure how I would translate that into an OFX, so maybe I could re-think that after other stuff is working.

I had a thought on T-bills in Money. Since the Buy in Money is on the auction date, and the sell comes in on the settlement date, the Money TR calculations will show a slightly lower yield than reality. This effect would be much more significant for the shorter term Treasuries, such as 4-week. And even then, the difference may not crack the threshold for significant. So maybe it rises to the threshold for a factoid, which has a low bar for significance.

[Cal Learner]

I saw this today: https://www.reddit.com/r/fidelityinvestments/comments/18hqzh5/problems_with_new_download_connection_method_in/

Fidelity OFX worked fine for me this morning, and let's hope it continues.

Action item: Keep copies of your recent OFX files from the PocketSense xfr folder. This may be useful for synthesizing OFX files from Fidelity CSV files.  The info that we would want to extract is if a security is a STOCK, MF, DEBT, OTHER. These could be filled into a spreadsheet, but I did make a program to extract that from existing OFX files into the spreadsheet database.

I revisited my start on a program to create Fidelity OFX files. Lots to be done, however.

[-Kevin N]

I'm currently using Pocketsense with Moneydance and MS Money Sunset.  

My approach in hopefully future-proofing Fidelity is converting the Fidelity CSV file (Accounts_History.csv) to QIF.  OFX is far too complicated for my limited knowledge.  It might as well be Klingon.

I open the Fidelity CSV file in Excel and run several macros to eliminate the extraneous data and massage the remaining data to match that of Moneydance.  

I then copy & paste, 1 account at a time, to another workbook so as to transpose the data to a single column adding in the requisite QIF letter designations and ^ transaction separators.

Finally, each account(s) data is written to a QIF file named with the last 4 digits of the account number.

I've been using this method for the past several months and it works well with importing it into Moneydance.  

It is my intention to also experiment using it with MS Money, but I have only just begun doing so and haven't delved into it too much.

My investments are pretty straight forward - some stocks, mutual funds and money market funds so the QIF format seems suitable enough.

[Cal Learner]

I am working on a program that will produce *.ofx files for Fidelity. For PocketSense users, at some point the files could be directed into the imports folder. I am slow. I am not structured. Code is in Python, so by nature, it will be open source. This does not mean that other efforts should be reduced.

My method uses a database called fidDB.csv that has info including the CUSIP (to let Money match up investments that you already have). I also have a program currently called FidDBgather.py that either creates or adds-to fidDB.csv. I usually invoke that with a batch file named  FidDBgather.bat, and in there I identify the folder that contains Fidelity OFX files to get info for fidDB.csv. For me, I point the PocketSense xfr folder. This requires that I not have PocketSense integrate the multiple Fidelity*.ofx files into a single file. It is possible that  combined file, with some program changes, but I don't plan to do that. Somebody could implement that.

Note that PocketSense deletes the existing *.ofx files before attempting to download the new stuff. The day the Fidelity OFX server stops working, it will be too late to read info from those OFX files into fidDB.csv, unless you have saved copies of the OFX files for later use.

Being a csv file, I can read or change  fidDB.csv with a spreadsheet, but you need to have your spreadsheet program not modify the file when you import.

I also have a program currently called fidcsv3.py, but that name could be changed. To use that, you go to the Fidelity website. You download the history and positions csv files. The default is that fidcsv3.py identifies the latest (by date code) the tow latest relevant CSV files, and uses those. It checks the history, and turns the history into OFX transactions with the help of fidDB.csv.

There is more to discuss, if there is interest.

This command, in a command window, will tell you where the current Fidelity OFX files are, if you have any.

dir \fidelity*.ofx /s

This one will only show those files that are from 2025, but does not show the folder.

dir \fidelity*.ofx /s |find "2024"

I can post a batch file that will just save the Fideliy OFX files that are in the xfr folder, and put them into a place that does not have files automatically get deleted.  The batch file would preserve your options without running *.py yet.  I run my *.py on Python 2.7.x where x > some number. (which PocketSense uses). If porting to Python 3.x, there would be come considerations, which we could discuss

I expect to provide FidDBgather.py soon for those who want to run that now.

At some time fidcsv3.py but I am more actively changing that. That would  need a more motivated and skilled person at this point.

Features include ignore transactions before a specified date, and for your live *.mny file,  that date would be the date that the Fidelity servers went offline. There are transaction types to be added, and options (OPT) are one that is not done.

There are more enhancements planned. Anybody still reading?