Specialized topic: If my broker stops providing an OFX/QFX server for PocketSense access
220 views
Skip to first unread message
Cal Learner
Dec 29, 2022, 3:37:10 PM12/29/22
to Microsoft Money
I am looking ahead to where Fidelity (and other brokers) may stop making our transactions available directly to us as OFX/QFX. Some brokers have done this already. I am specifically interested in investment accounts.
Fidelity offers a *.csv file named Accounts_History.csv. This can contain transaction info for all of your Fidelity accounts, or you can create a file for each account. My thought is to go for the file that contains all accounts, and let the converter create OFX files. These would be fed into the PocketSense import folder.
I am musing as to how to implement. I am thinking there would be a base file, and as the Accounts_History.csv was processed, each line would either be merged into the OFX file, or discarded, based on rules/algorithm.
Each transaction would have a TRNUID. This would be generated from date, description, and amount, but if there is already a transaction processed where those match, make a different TRNUID. So the first occurrence might always make a TRNUID that ends in 00, the next 01, and so forth. This seems like it should be fairly easy to generate. A bit of thought is needed if the processed CSV files are sometimes from a single account and sometimes from all accounts.
Then for each line, generate a BUYSTOCK, SELLMF or whatever, and insert the transaction into the OFX file.
When done, write the produced files into the PocketSense import folder.
Initially, some transactions in your Money file will already have come in with a broker-generated TRNID. So some method would be used to avoid duplicates. One method would be to have a date written into the converter to ignore entries earlier than a given date. Another would be to require the user to remove such entries before starting the converter.
This is not going to be easy, but I think it is very doable. My preference would be for Python for the converter.
This is early discussion, but it could turn into an urgent thing at some point.
Fidelity offers a *.csv file named Accounts_History.csv. This can contain transaction info for all of your Fidelity accounts, or you can create a file for each account. My thought is to go for the file that contains all accounts, and let the converter create OFX files. These would be fed into the PocketSense import folder.
I am musing as to how to implement. I am thinking there would be a base file, and as the Accounts_History.csv was processed, each line would either be merged into the OFX file, or discarded, based on rules/algorithm.
Each transaction would have a TRNUID. This would be generated from date, description, and amount, but if there is already a transaction processed where those match, make a different TRNUID. So the first occurrence might always make a TRNUID that ends in 00, the next 01, and so forth. This seems like it should be fairly easy to generate. A bit of thought is needed if the processed CSV files are sometimes from a single account and sometimes from all accounts.
Then for each line, generate a BUYSTOCK, SELLMF or whatever, and insert the transaction into the OFX file.
When done, write the produced files into the PocketSense import folder.
Initially, some transactions in your Money file will already have come in with a broker-generated TRNID. So some method would be used to avoid duplicates. One method would be to have a date written into the converter to ignore entries earlier than a given date. Another would be to require the user to remove such entries before starting the converter.
This is not going to be easy, but I think it is very doable. My preference would be for Python for the converter.
This is early discussion, but it could turn into an urgent thing at some point.
Ellen S
Dec 29, 2022, 5:45:20 PM12/29/22
to Microsoft Money
Good luck, I never was able to restore my Fidelity OFX download with Pocketsense. Fidelity quit on me 2 years ago, I thought I had the correct instructions, but maybe not? But I have another broker too. They all use .csv now. converters only work for bank downloads in my experience, not the more complicated brokerage transactions.
Cal Learner
Dec 30, 2022, 12:26:07 AM12/30/22
to Microsoft Money
If you want to try Fidelity and PocketSense again, say so.
But eventually there will be a broker CSV converter, even if I have to write it. I am not fast.
First Last
Dec 30, 2022, 4:52:51 PM12/30/22
to Microsoft Money
If we limit the project scope (at least at the start) to just 4 basic types
* buy/sell mutual funds
* buy/sell stocks
plus constraints:
* limit to USD currency (so no conversion is needed)
* and no additional fees (such as commission)
then it should be fairly straightforward
I will be happy to take a quick stab at a proof of concept prototype this week-end.
I only have Accounts_History.csv for mutual funds transactions. If someone can send me samples that has transactions for stock buy/sell, that will help.
--
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/368e7084-67ab-4ab5-bf84-62e88c3e84d0n%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Ameridan (microsoftmoneyoffline.wordpress.com)
Dec 30, 2022, 9:11:57 PM12/30/22
to Microsoft Money
I PM'd you one. I'll see if can find more.
First Last
Jan 2, 2023, 4:51:35 PM1/2/23
to Microsoft Money
Prototype/sample python codes to generate OFX for BUY/SELL STOCK, BUY/SELL MF
I am using an "internal" CSV input. The main idea is that new "helper/converter" code will have be created to convert other CSV format (such as that from Fidelity) into this "internal" CSV format
Cal Learner
Jan 4, 2023, 11:04:34 AM1/4/23
to Microsoft Money
Thanks. I downloaded your ofxlist-main.zip from github. The only python I saw was main.py, and that doesn't look as if it would generate an OFX file.
First Last
Jan 4, 2023, 11:14:28 AM1/4/23
to Microsoft Money
Please see the README file: https://github.com/hleofxquotes/ofxlist/blob/main/README.md
The actual codes are in src/ folder
$ python src/invtranlist.py --input data/example1.csv --output example1.ofx --acctid 987654321
# Reading input from file=data/example1.csv
# Writing output to file=example1.ofx
# Reading input from file=data/example1.csv
# Writing output to file=example1.ofx
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/5df4cff9-a006-4702-b6be-ee4dfd2a7816n%40googlegroups.com.
First Last
Jan 16, 2023, 3:52:42 PM1/16/23
to Microsoft Money
I've added some documentation on how to use the tool
I've done some sanity tests and made sure that the generated OFX passed Microsoft OFX validator and can be imported into money with no error.
Reply all
Reply to author
Forward
0 new messages