A free Fidelity to OFX converter from Cal Learner (fidcsv3 or F2O name subject to change)

[Cal Learner]

Ameridan's blog nicely documents where this program/project stands and explains the methodology.

See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/

While the current main program is named fidcsv3.py, it will probably be renamed to something like F2O. py or f20.py at some point. Since this runs on windows, the filename will not be case-sensitive.

Advantages:

• open source
• developed on Python 2.7 as PocketSense uses
  
• free
• can be used with PocketSense or without (I do recommend PocketSense)
  
• puts both transactions and positions and price info into the OFX files, just as with the OFX files that currently come in from Fidelity via PocketSense
• still being improved, so any bugs bug reports or suggestions posted here will be reviewed and probably acted upon
• the program finds your latest  Fidelity positions and history CSV files in your Downloads folder without further interaction needed.

Disadvantages:

• not professionally coded.
• lots of global variables,
• Not modular.
• the coding style evolved since being started March 2022, so method of doing the same thing is not constant thru the program(s)
  
• the code is not already openly published, but that will change if there is any interest.
• human interface not polished, but will probably improve
  
• user must download the positions and history CSV files from the website for each run, but the program does find those in your Downloads folder without further interaction needed.
  

Questions and comments are welcome. Don't be shy. I expect interest to pick up after the Fidelity OFX server disappears. But by that time, it would be too late for some things that you would miss out on that would would have made the operation initially easier.

[Cal Learner]

I have decided to open the Fidelity CSV to OFX (F2O) beta to all now. I have a folder that should be accessible given the link. This is a folder rather than a file:
https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing

In the folder is f2o_beta3.zip which has several files. One file is F2Obeta.doc
in Word 97 format, and  F2Obeta.txt which is is the text-only version.

Feel free to post comments, bugs, or ask why did you do something the way you did. The code evolved starting in March 2023.

[Ellen S]

Cal, 

Again many thanks for doing this, I am looking forward to testing.    

I see that you answered this Q in the zip's text file (which I viewed in my download folder), but it might help some to see the answer here:   after downloading the zip, where to unpack/store the contents?  

thx,

Ellen

[Cal Learner]

Good point. Putting the suggestion of where to unzip to should be outside of the zip file too!

 Create a folder for the F2O files, and unzip the *.zip file into that.   I suggest you might choose
USERPROFILE%\Documents\F2O. This command in a command window can create that folder:
md %USERPROFILE%\Documents\F2O\

In Files Explorer (the new name for windows explorer) you can click Documents, Then right click into the right area and chose New->Folder.

Note that %USERPROFILE% in Windows stands for wherever your home folder is. If your username is mary, it would be the same as writing 

"c:\users\mary"

This lets us create instructions and batch files that will work without worrying about the username.

[Ellen S]

I have questions, mainly but not entirely about file locations, and a few comments.  

Cal says to put the F2O files in the "Documents" folder, but Dan says "The remaining files are probably best saved in the same folder as the rest of your Python scripts." (I believe he is referring to all but "fset.bat.") Is the Python location--or C drive--important? or can the app files actually go anywhere?  FWIW, most of my stuff is in the cloud (Windows calls my OneDrive stuff as "My Computer"), and my browser's default Download folder is also in the cloud. I keep programs and some data--eg, some Money backup files--on the c drive.

I recognize the app needs to know where to look for the csv downloads and where to store the resulting OFX files. Dan says Cal programmed the app to look for history/positions in the Downloads folder, while he reprogrammed the app to look in his Money folder (csv) and then store the OFX results in Pocketsense/Import.   In either case, I assume hard drive vs cloud storage doesn't matter as long as the app knows where to look, but if you choose other than Cal's pre-programmed locations, you need to modify "fset.bat."  But where is it?  I don't see fset.bat in the F2O zip package.    

Whatever folder I use for the downloaded csv files, will the app ignore other csv files that might be in there (older, other banks, etc)?   

Finally, how do I actually run the program?   Do I open (double click) F2O.py?    I tried that, but it just opens and closes quickly, I assume because it can't find what it is looking for.  I'm guessing I have more setting up to do.

I was surprised to see that I need to download positions in addition to transactions, I assume to reconcile positions inside Money.  I'd never thought about the need because Fidelity currently downloads everything so seamlessly.  Thanks, Dan, for mentioning.

NB:  I recognize several of you are fluent using Windows' command language, but I'm not.  While I used commands back in the day, today I find the scripts reported here hard to follow/decipher.  If that's the only way to convey what I need to do, I'll try to slog thru it, but TBH, I would much prefer modern Windows directions where possible. 

thx

[Cal Learner]

The default for CSV downloads is the downloads folder. That is a Windows thing, and it makes sense to me to go with that. F2O.py defaults to looking there.

Where you put the files for F2O does not really matter, but the sample batchfiles will make an assumption. If you want to change where the files get written to, modify the batch file(s). The default is the current folder, and my batch files use the CD command to set the current folder.

The other folder to take note of is for F2OGather.py to read existing OFX files from the Pocketsense xfr folder. That location will not be the same for you as for me. The location of the xfr folder is passed in to the F2OGather.py  program as a parameter, but I usually use a batch file to launch F2OGather.py.

[Cal Learner]

If you "open" a batch file in Windows the way you launch a program, it runs, but it is harder, but not impossible, to pass parameters that way. I think there is a way to "drop" a file onto a batch file. I have not tried that.

fset.bat is not something that is needed in normal F2O operation, but is a batch file for those who might want to save their Fidelity OFX files for later analysis, but are not wanting to use OFXGather.py.  It is also a way to create snapshots of the latest Fidelity CSV files and the Fidelity.OFX files for later study an analysis. It is more of a tool for developers. fset was added into a post, but I will be glad to add it to the Googledrive folder that has the other files. But again, it is not intended for most people to use.

Getting the Fidelity OFX files is fairly easy now once you are on the website. Dan is working on a system to automate that. Note that when you download the two CSV files, you don't have to pay attention to the names. F2O knows the name format, and automatically picks the latest pair. Specifically, it picks the latest Portfolio_Positions*.csv and latest Accounts_History*.csv files. Fidelity files are the only ones I have that meet those criteria.

How would you like it to work? Does Pocketsense interfaced work OK for you?

The way F2O works will adapt if/when the Fidelity OFX servers disappear. For now, we don't want to interfere with PocketSense, but want to make it so that people can test.

The place to start would be F2O_DBgather.bat which can launch F2O_DBgather.py. You would want to edit F2O_DBgather.bat. However now that I look at it, I made it more confusing than it had to be by making it work if the F2O files were in either of two places.

So probably I should address that first. What should I call that somewhat simplified batch file?

How things will go are under consideration.  One possibility is that PocketSense could invoke F2O similar to how it can invoke a Fidelity scrubber now.

I probably missed, or did not address well,  some items that you brought up. Bring any up again.

[Cal Learner]

Something just struck me... F2OGather.py presumes that the Pocketsense xfr folder contains separate files for each Fidelity account. However, if your Pocketsense sites.dat contains "CombineOFX: Yes" then the OFX files all combine. I should experiment with doing that. I don't know how readily I would be able to handle that in F2OGather.py.  So for now, setting the default  "CombineOFX: No" in your sites.dat would prevent this problem.

Ellen, I am planning a new post where I discuss ways of making the operations more comfortable for those who are not comfortable with command windows and batch files. Ideas I will mention including putting a shortcut on the Desktop that invokes F2OGather and another that runs F2O.

I will also consider compiling the python programs to exe programs. I have never tried that.

I will also discuss file locations. The Windows paradigm  would have files tucked into  %HOMEPATH%\AppData\Local\

If you look at that folder, you will see other programs do that.

So in working on the F2O, I felt a sense of urgency because I did not know how soon the Fidelity OFX server would be taken down. Your post has me thinking about how to make this more humane. Ameridan has done a lot of work in that direction, but things could be more automated.

Make comments, but watch for my new thread on the topic.

[Ellen S]

thank you, Cal.  I don't need this dumbed down too much, but it has been a bit confusing for me, and I assume, others as well.  

What to call the simplified batch file:  doesn't matter to me, your conventions are fairly clear.  FWIW, I didn't know how to interpret the letters to the right of the dot (bat, py, etc.) since I am not a programmer, but now I know .bat means batch file and where I need to start.

My Fidelity OFX are currently not combined (in the Pocketsense xfr folder).   Maybe the wisdom of that depends on how many accounts one has.

I really appreciate your efforts and did not intend to make more work for you--and now that I know where to start, I will return to trying your app.  and let's hope we have time to work through this.   In any case, I remain very grateful.

[Cal Learner]

I found your perspective to be useful. We all benefit from a wider perspective.

A batch file is a list of things you could have typed into a command window. If you put several of these commands into a file named *.bat, or *.cmd, then the things in the file get treated as if you typed them.

In Windows paths with "wildcards", the most common wildcard is the asterisk "*". It matches anything or nothing. So abc*.txt would represent all of the filenames that start with abc and have an extension of .bat. *abc*.bat would match any filename that contains abc with a .bat extension, regardless of if it is at the beginning or not.

[Cal Learner]

The problem I worried about regarding PocketSense " CombineOFX: Yes"  turns out to not be a problem. I just switched, and the files in the xfr folder remain separate. I will try leaving Pocketsense  "CombineOFX: Yes"  in place.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

Ellen, I finished my F2O Menu of sorts, that you may find helpful.  It's F2O.bat (link below) that once you customize the paths to your PC, will allow to choose any/all of Cal's and my scripts.  I also just finished a script today that grabs your CSV files from Fidelity.

https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/#F2Obat 

[Ameridan (microsoftmoneyoffline.wordpress.com)]

Cal, combine files doesn't actually combine any files, but it does produce one additional OFX  file for Pocketsense with everything combined.

[-Kevin N]

Hi Cal, Ameridan,

Perhaps a step-by-step quick set-up guide is in order.  

Much like Ellen, I have unzipped the contents of f20_beta3.zip in an F20 folder in Documents but what to do next is unclear.

[-Kevin N]

My apologies, I failed to mention that I have both Account_History.csv and Portfolio_Positions.csv downloaded to my Downloads folder.

Clicking F20.py and / or F20_DBgather.py briefly opens and closed a python window.  

I do have a collection of Fidelity OFX transaction files as well as quotation files saved up from Pocketsense.

I'm at a loss as to what to do next though.

[Cal Learner]

See https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/

While that page still could use some simplification, I think it does a good job documenting. Take notes about what points get ambiguous or confusing, and ask.

Do you have Python 2.7 installed? If you already use PocketSense, you do.

The thing that you want to do next is to to build F2O_DB.csv which looks at your PocketSense xfr folder. Do that by modifying the line just above the :done to match where your Pocketsense xfr folder is. If that could use some better description, say so.

If your new F2O folder is in your documents folder, then that one line is all that needs to be modified.

The batch file should be in your F2O folder, along with the other files that you unzipped. Then run the batch file in the way you feel comfortable with. I type the batch file name in a command window, but you can just run the batch file .

Then once the F2O_DB.csv get created, you can run F2O.py.

Fresh eyes, and identifying the confusing points can help polish this toward being usable by more people.

Thanks.

[-Kevin N]

Thanks Cal,  

I'll give Ameridan's Blog another go in the morning with fresh eyes. : )

[Ameridan (microsoftmoneyoffline.wordpress.com)]

I edited the article this morning to better align with Cal's packaging of the beta zip file.  As Cal stated, I'll do my best to address items still deemed to be confusing.

[Ellen S]

It is my understanding that, before running anything, I need to build/create F2O_db.csv, and I do that by modifying the "line just above :done" to match my xfr location.  At first, I had trouble locating ":done" but finally found it using page search under #1, at the F2O_DBgather.bat discussion.

I located my F2O_DBgather.bat file and discovered ".bk1" appended after .bat.  I am guessing this is a result of trying to run the program before customizing to my environment.  In any case, I opened the file in Notepad, saw the comment lines at the top and located where I needed to replace Cal's filepaths and folders with my own.   Progress.

Now, a few questions/comments:   

• I assume "rem" tells the program to ignore what follows, since they all appear to be instructions/explanations for the reader.  Like # in Sites.bat.
• re first action line "if not exist ..." -- what does this do?  It's not telling the computer to create a new folder, is it?
• re next line, "cd", changing directory to my file path:  I've named my F2O folder "Fidelity to OFX."  For command language purposes, do I need to eliminate the spaces between words?   perhaps use underscores?
• re ":not_mn" the 6th line in the programming section.  Since "mn" refers to Cal's filepath, is this line telling the computer to not look for "mn"?  or do I need to substitute my folder name here? 
• My xfr folder is located in Pocketsense at Program Files (x86), not in Documents.   Am I required to send the new F2O OFX files there--i.e., will the app only look for "xfr"--or can I create a new folder under Documents with a different name?   I may rearrange everything later, but for now I'd like to keep things separate.

That's it for now.  I feel like I am very close to a successful run.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

• I assume "rem" tells the program to ignore what follows, since they all appear to be instructions/explanations for the reader.  Like # in Sites.bat.
  

• Yes, that's correct.

• re first action line "if not exist ..." -- what does this do?  It's not telling the computer to create a new folder, is it?
  

• This (and 2 questions down) is Cal's way of telling the script to go to the line above :done if it isn't his computer ;)

• re next line, "cd", changing directory to my file path:  I've named my F2O folder "Fidelity to OFX."  For command language purposes, do I need to eliminate the spaces between words?   perhaps use underscores?
  

• No, just use double-quotes   "Fidelity to OFX"

• re ":not_mn" the 6th line in the programming section.  Since "mn" refers to Cal's filepath, is this line telling the computer to not look for "mn"?  or do I need to substitute my folder name here? 
  

• No.

• My xfr folder is located in Pocketsense at Program Files (x86), not in Documents.   Am I required to send the new F2O OFX files there--i.e., will the app only look for "xfr"--or can I create a new folder under Documents with a different name?   I may rearrange everything later, but for now I'd like to keep things separate.
  

• That's fine Ellen, just remember same if you adopt any of my bat files ;)

[Ellen S]

Thanks Dan.  My adventure continues:

After replacing Cal's "mn" path in F2O_DBgather.bat with mine--"%USERPROFILE%\documents\personal\"Fidelity to OFX"\OFX" I double clicked on "F20_DBgather.bat" which briefly opened a command window with what appeared to be a list of Fidelity accounts scrolling by very quickly.  And then it closed.

Progress, I guess, but as far as I could tell, nothing happened.  I could not find a newly created "F2O_db.csv" anywhere, nor did I find any new OFX files.  

Would it matter if I used the command window to run it?  I've got it open and was able to change the directory to "%USERPROFILE%\documents\personal\"Fidelity to OFX"\beta3" (location of the app files) but now wondering what's next.  I tried "run F2O_DBgather.bat" but nothing happened.

[Cal Learner]

My xfr folder is located in Pocketsense at Program Files (x86), not in Documents.   Am I required to send the new F2O OFX files there--i.e., will the app only look for "xfr"--or can I create a new folder under Documents with a different name?   I may rearrange everything later, but for now I'd like to keep things separate.

Program Files (x86), Program Files (x86), and Windows are special. They may not be written to by ordinary users, unless the privilege gets elevated.

Since PocketSense is working, and successfully writing to the xfr folder (with the other PocketSense stuff, you must have done something to run

PocketSense with elevated privilege.  https://www.thewindowsclub.com/elevated-privileges-windows

Note that F2O_DBGather.py only reads from the Pocketsense xfr folder and does not write to it. So having the xfr folder in one of the special folders will not bother F2O_DBGather.py.

I would prefer to not have my F2O files in the special folders.  Then no privilege elevation needed.

In the plan, Windows users (such as Guest) have their own private places, such as Documents. They cannot read each others Documents. They can read, but not write Program Files*. Pocketsense will have your broker login credentials in ofx_config.cfg, tho PocketSense can be set up to encrypt that.

[Cal Learner]

Some comments which are not important to know, but one or more could be of interest:

1. If you add

pause

as a line in the  batch file, the window will stay open until you press a key.

2. You don't type "run",

3. If you type

F2O_DBgather.bat >temp.txt

it will write the brief output to a file called temp.txt in the current folder.

4. if you type

dir c:\F2O_DB.csv /s

into a command window, it will search your whole C: drive for that file. If you put your F2O files into Program Files or Program Files(x86), you may be surprised to see where that file ended up. It is a place called the Virtual Store. There is a search term for you if you find F2O_DB.csv in an odd spot

5. There is a way to getDBgather to write a whole lot more to the screen, but for now, even debug level 1 will bring up the debugger. In the batch file I have a comment about replacing the zero with a 4. A 1 would be a better place to start. If you do that, the stuff that comes will be cryptic. it is there because I used it as I was writing code. If you get into the debugger,  entering

c

will continue. Most Windows stuff is not case sensitive, but that c must be lowercase.

[Ellen S]

sigh.  I am but a lowly mortal w/r windows' secrets.  At least you know F2O is getting a workout.  

Like Kevin, I will return another day when my head is fresh and I can focus on this again.

Thanks Cal and Dan, appreciate your teaching and patience.    

[-Kevin N]

Hi Cal, Ameridan,

Let me preface this with how appreciative I am for the work that both of you have put into this and for making this available to us MS Money / Fidelity clients.

I'm sure that after a year of working on this project the two of you are very familiar with how this works and I'm sure it's all very obvious to both of you.

However, I defy any average joe freshly walking into to this project to be able to make heads or tails of it.

Let's start right off with creating the F20 folder and unzipping the files.  They don't 'land' in F20, they land in F20/beta3.  So, any paths have to include /beta3 (or the files in /beta3 have to be moved to /F20).  That alone could styme a lot of users.

As Ellen mentioned, there is no F20_DBgather.bat file, it's a F20_DBgather.bat.bk1 file.  Tell the user to rename it to F20_DBgather.bat or tell them to make a copy and rename it.  It's simple stuff like this that the average users aren't going to know.

The remarks in F20_DBgather.bat.bk1 are confusing and assume too much.  
Cal, why tell us to use F20 and then include remarks about '...MN\ofx_frame'?  It only makes it confusing.
Ameridan, in your blog, step '1. F2O_DBgather.bat', why say that you save your files in a \Python scripts folder.  It's already been determined that we should be using F20.  It only makes it confusing.

If there are lines in the F20_DBgather.bat file that do not pertain to new users, rem them out or better yet, remove them. They only make it confusing.
 
We need line-by-line instructions for setting up F20_DBgather.bat.  e.g. remark to the user to <insert the path to your F20 folder here> or < insert the path to your Pocketsense 'xfr' folder here>.  Assume that we know very little to nothing.

I have not been able to create F2O_DB.csv.

[Cal Learner]

Based on the helpful comments, I have uploaded f2o_beta4.zip This is in the same  folder accessible as the older version was :
https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing

The highlight is a new batch file called

make_new_F2O_DBgather.bat.bat

It assumes you have unzipped files into a folder named F2O in your Documents folder. It creates a custom F2O_DBgather.bat for you.

It assumes there is only one xfr folder. If there is more than one, the batch file produced may not work. It may point to an old PocketSense folder that you are no longer using. I think this is not going to be a problem for most people.

It also does not have the confusing 2-level file structure. All of the files that you should unzip are where they should have been from the beginning.

[-Kevin N]

Hi Cal, Ameridan,

Thank you.  The new UI is  now much more user-friendly.

I DO have 2 Pocketsense xfr folders, one for quotes and one for transactions.  Based on the paths displayed in the generated F20_DBgather.bat file, the make_new_F20_DBgather.bat.bat file correctly locted the proper xfr folder.

Below is the contents of F20_DBgather.bat

cd %USERPROFILE%\documents\F2O
python F2O_DBgather.py c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr

Unfortunately, running F20_DBgather.bat still does not produce the requisite F2O_DB.csv file.

Below is the output from running F20_DBgather.bat.  I'm not sure why it states that "The system cannot find the path specified".  

C:\Users\Kevin\Documents\F20>cd C:\Users\Kevin\documents\F2O
The system cannot find the path specified.

C:\Users\Kevin\Documents\F20>python F2O_DBgather.py c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr
> c:\users\kevin\documents\f20\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2
(Pdb)

[Cal Learner]

I think I see the problem. Your files are in F 2 zero, and the program expects F 2 oh. And why did they add to the confusion by putting 0 and O adjacent on the keyboard?

You can rename the folder, if no command window or other window is currently focused into the folder.

I could add a check for that in the make_new_F2O_DBgather.bat.bat file!

[-Kevin N]

Hi Cal,  

Good eye!  0 ≠ O

It no longer throws the error "The system cannot find the path specified".  

Unfortunately, it still doesn't produce the csv file.

C:\Users\Kevin\Documents\F2O>cd C:\Users\Kevin\documents\F2O

C:\Users\Kevin\Documents\F2O>python F2O_DBgather.py c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr
> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()

-> pair_i=pair_i+2 #loop counter by 2
(Pdb)

Does the path to the xfr file require closed quotes?

Am I correct in assuming that the csv file would be created in the F2O folder?

[Cal Learner]

Yes and yes! Thanks again.

I was aware that a space in the pathname needed quotes, so I should have had the quotes. I was unaware that a minus sign in the path needed quotes, so let's hope the added quotes fixes things.

I have replaced  f2o_beta4.zip with f2o_beta5.zip.

There are two file differences.

1. The make_new_F2O_DBgather.bat.bat file has been modified to make sure that the folder is not F20 instead of F2O, and I added quotes into the generated F2O_DBgather.bat file.
2. There are minor changes in the F2Obeta.doc

[-Kevin N]

Hi Cal,

I can confirm that F2O_DBgather.bat now includes closed quotes to the 2 paths.

I'm sorry to report though that it still does not produce the F2O_DB.csv file for me.

Hopefully, Ellen will give beta5 a whirl so that we can compare notes.

Just on a hunch, I tried changing my (Kevin) Standard User account to an Administrator account to see if that made any difference but no joy. : (

C:\Users\Kevin\Documents\F2O>cd "C:\Users\Kevin\documents\F2O"

C:\Users\Kevin\Documents\F2O>python F2O_DBgather.py "c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr"
> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2
(Pdb)

[Ellen S]

Kevin, Cal, here I am reporting my beta5 experience, step by step.   

1.  read f2obeta.txt and had a question at #3:   "In the files find f2o.bat. If you copy that file into a folder that is in your PATH, you can change to that folder by typing f20 or F2O into a command window. Windows is not case-sensitive."

Q1: If I copy that file ...:  do I NEED to copy it and put in another folder?  

Q2:  By "in your Path," do you mean where the XFR folder is located, which for me is Pocketsense in Program Files (x86), or somewhere else?

Error noted @ #3:  0 vs O -- "typing f20" >> should be "f2o."

2.  I ran (double clicked) "make_new_F2O_DBgather.bat.bat" and successfully created a new F2O_DBgather.bat in the F2O folder.

3.  I ran (double clicked) "F2O_DBgather.bat" and got output very similar to Kevin's run:

C:\Users\ellen\Documents\F20>cd "C:\Users\Ellen\Documents\F20"

C:\Users\ellen\Documents\F2O>python F2O_DBgather.py "c:\Program Files (x86)\Pocketsense\xfr"

> c:\users\ellen\documents\f2o\f2o_dbgather.py(272)<module>()

-> pair_i=pair_i+2 #loop counter by 2
(Pdb)

4.  I Looked in Documents/F2O and in Pocketsense but did not find a new .csv file.  A search of the whole c drive did not find F2O_DB.csv.  

5.  However, I did find a new "ofx_config.cfg" file in Pocketsense.   It contains my logon info; I was wondering how F2O would get into Fidelity, so now I know that part works.  

6.  I ran (double clicked) F2O_DBgather.py just to see what would happen; the command window flashed and closed, could not see what was in it.

Bottom line:   still no F2O_DB.csv.

[Cal Learner]

Ellen:
1. PATH
https://www.howtogeek.com/118594/how-to-edit-your-system-path-for-easy-command-line-access/ seems good at explaining what PATH is about. It also explains how to make the path be what you want whenever the computer reboots.

I can search further if some aspect is confusing.

For myself, I made a folder called c:\bat
By putting that into my search path, if I put a file called doit.bat into that folder, I can just type doit to get doit.bat. Not everybody needs such a folder, but it is second nature to me now.

I have several files in that folder. The current folder is generally in the path. But if your batch file  (or exe file etc) is in a folder that is in the PATH, then you don't have to have the batch file in the folder you are working on.

2. Glad for some good news.

3. I know  what happened to an extent.
In line 244 of F2O_DBgather.py I have this:
DBmatch = DBpat.search(ofx_pieces[pair_i])

It expected ofx_pieces[pair_i] to match a pattern that I had set earlier. It did not.

Could you enter this following the (Pdb) prompt?
ofx_pieces[pair_i];;pair_i

Yes the double semicolons was intended. Python is case-sensitive, but ideally you will be able to copy and paste.

After that, I will likely ask for some more info. The one piece of somewhat sensitive info to watch out for is your account number. But other than that, could you copy the result and paste into a reply?

My letting the non-match drop into the debugger was an expedient. After I know what happened, there is a good chance that if that condition happens again, I will just do nothing and move on in the program.


4. That condition  that you described in #3 aborted the  f2o_dbgather.py so the datbase did not get written.

5.  "ofx_config.cfg" file in Pocketsense was always there. You did not notice it before. F2O does not do anything with that.

6. To see something, you could create F2O_DB.csv as an empty file, and then run F2O.py

Or just wait for the troubleshooting and fix of f2o_dbgather.py.

[Ellen S]

(pbd) is a prompt?   who knew.  Ok, I'll try entering that string, but I'm done for today and tomorrow I'm working at the polls, so maybe Wed I can pick this back up.

ofx_config.cfg may have always been there, but it was modified after the run because it had today's date.   I last ran GetData yesterday.  The Setup.log also had 2 new lines but didn't reveal anything. If curious:

2024-03-04 16:32:51,986 - INFO - PocketSense OFX Download Python Scripts, Ver: 2023-Jul-20
2024-03-04 16:33:00,153 - INFO - ----------------------------------------------------------

[-Kevin N]

HI Cal,

Here's what I get...

C:\Users\Kevin\Documents\F2O>cd "C:\Users\Kevin\documents\F2O"

C:\Users\Kevin\Documents\F2O>python F2O_DBgather.py "c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr"
> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2

(Pdb) ofx_pieces[pair_i];;pair_i
'<STOCKINFO><SECINFO><SECID><UNIQUEID>060505104<UNIQUEIDTYPE>CUSIP</SECID><SECNAME>BANK AMERICA CORP COM<TICKER>BAC<DTASOF>20240302033006.000[-5:EST]<CURRENCY><CURRATE>1.000<CURSYM>USD  </CURRENCY></SECINFO><STOCKTYPE>COMMON<DTYIELDASOF>20240302033006.000[-5:EST]'
10
(Pdb)

[Cal Learner]

1. So that section of code is looking at transactions from your Fidelity*.ofx files in your xfr folder. The fact that it hit that debugger spot indicates there was some kind of thing that I had not run across in my files. I had allowed for that by invoking the debugger. I know, not user-friendly. it is developer-friendly, and I will now polish it some.

2. I plan to make a new F2O_DBgather.py that will not invoke the debugger if DEBUG is zero (the default). What I expect to do is to ignore the unrecognized element  except that it may write debug info to a file. I have not decided whether to always generate that debug data file if there is an unknown element, or only do that if DEBUG > zero. There is a pretty easy way to change the DEBUG level without getting into the actual code. The file will be on your computer, and not go anywhere on its own. The debug info should help identify if there is an element that I should look for in the future, or just ignore it.

3. When the Python DeBugger gets triggered, it gives the (PDB) prompt. If you enter c, execution will continue. The code should ignore that element that was not recognized. There could be only one thing that triggers that, or there could be more. The logging to a file should make it easier to look, and then decide if you want to share some of the content. So to restate, when the program stops, you can enter c to continue. ("Enter" c means that you poke the c key and then the Enter key)

4. My PocketSense ofx_config.cfg file is timestamped 02/21/2024  07:22 PM.

I am actually surprised that it is dated that recently. PocketSense will read it when you getdata, but I would not have thought it would regularly be written to. I may have run Pocketsense  setup.py at that time to use the test function to bring in in a fresh OFX file. My OFX files in xfr are timetamped 03/02/2024  08:53 AM,

I assure you that none of the F2O files looks at or modifies ofx_config.cfg

[-Kevin N]

Hi Cal,  

I did the c Enter thing a couple of times...

C:\Users\Kevin\Documents\F2O>cd "C:\Users\Kevin\documents\F2O"

C:\Users\Kevin\Documents\F2O>python F2O_DBgather.py "c:\Users\Kevin\Documents\Money\Pocketsense\Pocketsense-Data\xfr"
> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2

(Pdb) ofx_pieces[pair_i];;pair_i
'<STOCKINFO><SECINFO><SECID><UNIQUEID>060505104<UNIQUEIDTYPE>CUSIP</SECID><SECNAME>BANK AMERICA CORP COM<TICKER>BAC<DTASOF>20240302033006.000[-5:EST]<CURRENCY><CURRATE>1.000<CURSYM>USD  </CURRENCY></SECINFO><STOCKTYPE>COMMON<DTYIELDASOF>20240302033006.000[-5:EST]'
10
(Pdb)

(Pdb) c
> c:\users\kevin\documents\f2o\f2o_dbgather.py(264)<module>()
-> pdb.set_trace()
(Pdb) c

> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2

(Pdb) c
> c:\users\kevin\documents\f2o\f2o_dbgather.py(264)<module>()
-> pdb.set_trace()
(Pdb) c

> c:\users\kevin\documents\f2o\f2o_dbgather.py(272)<module>()
-> pair_i=pair_i+2 #loop counter by 2
(Pdb)

[Cal Learner]

Kevin. I stuck your OFX segment into one of my OFX files, and replicated the problem. I located and fixed the problem I think.

I only did a quick test after making the changes.

 f2o_beta6.zip is up with the changes. There are significant changes to F2O_DBgather.py and a minor change to make_new_F2O_DBgather.bat.bat which in turn would make a minor change to F2O_DBgather.bat . That change would allow passing a DEBUG level parameter. Taking the default is generally best, so no need to use that parameter unless getting into the debugger on purpose.

[Jason J]

Cal,   

  Did you intentionally leave pdg.set_trace() in line 264 or did you intend to comment that out for the beta?   This would explain the python debugger pdb() prompts.

-Jason

[Cal Learner]

Jason,    f2o_beta5.zip, and before, did have that intentionally, because I did not expect that to be hit. I just fixed some stuff in  F2O_DBgather.py based on reports here in f2o_beta6.zip which is now available in the same https://drive.google.com/drive/folders/1-4fBbRxaPhNYZK0gNHuZ3n-Bdoncy2aR?usp=sharing  folder.

As to why this did not turn up in my or Ameridan's testing is that we use a Fidelity scrubber with PocketSense that prettified the OFX a bit. That added a newline, and the code that parsed the entry did not work without the scrubbing. It turned out that problem string appeared in the code twice, and has been fixed. Thanks.

[-Kevin N]

Hi Cal,

Success!  beta6 creates the requisite F2O_DB.csv

Opening F2O_DB.csv in Notepad++ reveals all 32 of my holdings.
Ticker, CUSIP, Security Type, Security Name

Upon running F2O.py, I'm presented with the following:

Fidelity csv 2 Ofx converter
('history entry has\n', ' 02/29/2024,"**REDACTED ACCOUNT NAME**" **REDACTED ACCOUNT NUMBER**," DIVIDEND RECEIVED FIDELITY GOVERNMENT CASH RESERVES (FDRXX) (Cash)", FDRXX," FIDELITY GOVERNMENT CASH RESERVES",Cash,0.000,,,,,64.45,\n')
Can now add a new entry. I suggest MF
Inputs are not case-sensitive. Do not use quote characters, but do press Enter.
Enter one of ('MF', 'STOCK', 'OTHER', 'OPTION', 'DEBT') :

Obviously, I've redacted the account name and number.
For this and what I assume will be other entries, should I just follow the 'Security Type' designations that are entered in the F2O_DB.csv file?
In other words, the csv file designates the Fidelity Government Cash Reserves fund as MF, so I should choose MF and follow through on that course.  Correct?

[Cal Learner]

Kevin, useful topic.  In OFX, there is no distinction between a mutual fund (that has a price of $1) and a MMF.

For my "core holding" I use a Pocketsense scrubber that removes buys and sells of the core MMF. Thus my core/sweep MMF is reflected as cash in MM. That does make for an oddity by having dividend payments with zero shares. I have considered replacing the core MMF dividend transactions with interest transactions.  The Fidelity positions CSV file does identify the core holding for each account by **.

Microsoft Money  DOES make a distinction between MMF and MF. So in Money, you want to create the MMF investment before downloading your transactions via OFX. When you introduce a new MMF into Money in OFX, it does not give you the choice of identifying it as a MMF.

Having an MMF  as type MF in Money is not terrible.... it is just that your Portfolio will group the MMF into the Mutual Funds section when you choose
"Group by: Investment type".

[Cal Learner]

I mentioned using a Fidelity scrubber that removes my core MF buys and sells. https://drive.google.com/file/d/1VJPPkb_XgxBg-40QklPnjvKHwGAD_NBp/view?usp=sharing  is my scrubber.

[-Kevin N]

Hi Cal,  

I treat MMF's as securities in both Moneydance and MS Money.  Buys, Sells, Dividend Reinvestments, the whole works.  

For me, doing so creates something of a checks and balances in that, treating the MMF as a security, the balance of the separate 'Cash' register of Investment accounts is always zero.  If there is balance listed in the separate 'Cash' register, then I know something is amok and needs to be looked at.

I'll go ahead with the MF designation for the MMF.

[-Kevin N]

Hi Cal,

I slight clarification to my earlier post, I thought that the F2O_DB.csv file already contained the MMF but F2O.py is actually looking to 'ADD' the MMF to F20_DB.csv.

I just wanted to clear that up. :)

[-Kevin N]

HI Cal,

The OFX files generated all look to be in working order (5 accounts).  Well-done, Sir!

Fidelity discontinuing direct-connect has always been a worrisome issue in the back of my mind.

It was the main reason that I started fiddling around using Excel to create QIF files from Fidelity's csv files.  I was successful but it's clunky and not as elegant as OFX.

Yours and Dan's work with F2O has future-proofed the matter.  (for as long as Fidelity provides csv files) Ugh, I don't want to even think about that.

Looking through the 2 py files I can appreciate the enormous amount of work went into this.

I want to wait until the market closes today to d/l fresh 'Account_History' & 'Portfolio_Positions' files so that I can run a balances comparison to my working MS Money and Moneydance files.

Again, well done and thank you for your work on this project.  

-Kevin N.

[Cal Learner]

The user should not have to know about this topic, but it is a favorite of mine... so here goes.

I like the OFX files to deliver closing prices and dates. So if I am analyzing the prices in the positions file, there are 3 conditions to deal with.

1. The market is not yet open so use the date of the last day the market was open.

2. Market is open now... this one is tricky. I compute the closing price from the current price and the change for the day. Still use the previous market-open date. I am not sure this always works, but I don't know if it is a deficiency in the F2O code or the CSV file. To avoid this, avoid getting your CSV files while the market is open. Or if testing, DO get your files while the market is open as the toughest test.

3. Market has closed, but was open today. Use today's date. Use the price presented.

Money remembers the price from the first OFX it got for a given date. If you tried to provide intra-day prices via OFX, then only the first OFX processed for the date would count.

I know I don't have to tell you that if you feed the OFX files to Money to test, use a copy of your file -- preferably from an earlier date. You don't want transactions to overlap with what you brought in with PocketSense. The FITDs will be different, an Money will think the same transaction is a new transaction.

[-Kevin N]

Hi Cal,

If I may make a small suggestion, in 'make_new_F2O_DBgather.bat.bat, Line 2 that begins with a hashtag may want to be either echo-ed or rem-ed.  

As it stands it produces '#only' is not recognized as an internal or external command, operable program or batch file.

[Cal Learner]

Kevin: Thanks. I used rem on my copies. That will go out with the next beta release.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

One other small suggestion:

add python F2O.py to F2O.bat, so it will do something ;)

For anyone wanting to use F2o.bat as a menu of sorts, my version is posted as an example @

https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/#F2Obat

[-Kevin N]

Hi Cal,

Happy to report that, so far, my 'Copy' data file of MS Money using F2O reconciles to-the-penny with my working MS Money and Moneydance data files.

The IGNORE_BEFORE_YYYYMMDD feature is a really nice touch.   

-Kevin N.

[Ellen S]

So glad this got worked out in my absence, glad to help, haha.

Now trying to use.   I replaced the previous beta5 with the new beta6.   I successfully created F2O_DBgather.bat and F2O_DB.csv, which correctly lists all my investments at Fidelity.   but the next steps are unclear, and what should I expect to see?

I tried double clicking F2O_DBgather.bat (again), and then, each in turn, F2O.bat, F2O.py (Dan wrote this needed to be added to F2O.bat) and F2O_DBgather.py (I know the txt files say this doesn't need to run again unless/until you need to update the investments).   The command window briefly opened each time but not long enough for me to see what was going on, and I can't find any new OFX files or imported data.   My Pocketsense/xfr folder contains only previous OFX downloads dated 3/3/24.  Money doesn't have any new data, neither transactions nor prices (I didn't expect this anyway).   

BTW, I forgot to test the runs using an older file and had my main file open.   I thought the new OFX files would sit in XFR until I was ready to import them, or at least hoped that would be the case.  Then, I planned to run them through Microsoft's "OFX analyzer" before importing to check for file problems.  see Analyzing OFX files [Microsoft (MS) Money FAQ and Help] (moneymvps.org)).   

So--what is my next step?   which F2O file do I focus on?   do I need to customize one or more of them?  and will the new OFX files show up in XFR as I expected or try to upload immediately into Money?   I was hoping the txt file would help, but if the answers are there, I am missing them.  

[Cal Learner]

Ellen, The new OFX files sit in the %USERPROFILE%\documents\F2O folder, for testing while we are in the period where the Fidelity OFX files still work. Try exploring there.

I am planning to put out a revised f20.bat as suggested by Ameridan.

Currently I am thinking it will contain this:

-----------------------------------------------

cd %USERPROFILE%\documents\F2O
python f2o.py
timeout /T 20

-------------------------------------------------

The timeout will wait for 20 seconds unless you press a key. Press a key sooner, and the window will close for you if you invoked the batch file from File Explorer.  Is 20 the ideal number of seconds in your opinion? Easy to change yourself, but it is easier if I start people with a good number.

If you test the new files on a Money file, I suggest you restore an older *.mbf to a different name than you usually use.

You would not want the new transactions from F2O to overlap dates with the ones that came in from Fidelity. Since F2O makes its own FITID values for the transactions, overlapping dates will make Money think this is a new transaction.

Before doing that, I suggest that you adjust your backup settings.

================================================

In Money Plus: Tools->Settings->BackupSettings

There are two SaveIn boxes. Make sure those point to folders that exist. This is important when you are setting up a new computer.

 I suggest checking
  x Automatically backup my Money file every time I close Money
   Save in [a place on your hard drive ]. It is best to Browse to
      a location rather than typing the path.
    x Ask me before backing up each time*
    x Compress Backup Files
    Keep only the _20_most recent backup files [adjust to taste; harddrive space is cheap]
 
  x Perform scheduled backups every _7_ days
   Save in [a USB flash drive location]
    x Ask me before backing up each time*
    x Compress Backup Files
    Keep only the _8_most recent backup files [adjust to taste]


 * The reason I have Money ask me, is that I don't want to back up each time I exit. If I went into Money 5 times in a day, I would not want to back up 5 times, nor would I want to delete 5 other backup files. You will be given the choice to BackUpNow or to Postpone.

==============================================================================================

With F2O experimenting, I suggest that you always choose Postpone for backups UNLESS you have changed the two SaveIn boxes to not overwrite your regular Microsoft Money backups.

I would also comment that from a cmd window, you could type/paste this command

dir c:\fid*.ofx /s

That will search your whole C drive. It should find files in your PocketSense xfr folder, plus the F2O folder. You may have a favorite way to search for file that does not involve a cmd window.

[Ellen S]

Cal, I should have mentioned they are not in the F2O folder either.  I did a search on c, no joy.

Going forward, yes, I will certainly experiment with an older file, I just forgot this time.  no worries about renaming/backing up etc, I am very experienced there, at least.

timeouts are good, 20 sec sounds fine at this juncture.

[Cal Learner]

I suggest you replace the contents of your f20.bat  with this

---------------------begin--------------------------

cd %USERPROFILE%\documents\F2O
python f2o.py

timeout /T 600

----------------end ------------------

Note that while 600 represents 10 minutes, pressing any key while focusing on that window will let you close that out.

I was thinking of putting out a new zip file with updates now, this is would be the main change. So probably easier to edit the batch file with Notepad etc, and pasting those 3 lines into that file replacing the current contents.

[-Kevin N]

Hi Ellen,

Can you verify that you downloaded the Accounts_History and Portfolio_Positions csv files from Fidelity to your Downloads folder?

-Kevin N.

[Ellen S]

Cal:  edit made.   but...[insert embarrassed emoji]

Kevin:  yikes! duh, I did forget this step, too accustomed to Pocketsense's hands off system I guess.  

Update:  downloaded 10 days of transactions from one account (no positions, won't match anyway the older test file anyway).  Put the file into c Downloads (also into Onedrive Downloads b/c it is my default, just in case)  

Ran F2O.bat again, still no joy.  Search finds no OFX files, altho that can't be right because some are in XFR.  will look again later, time for dinner.  

This is what I got in the command window: after first 2 path lines, something like this:

Fidelity csv 2 OFX converter

Traceback (most recent call last):

File "F2o.py", Line 629, in <module>

max_hist_file = max(files, key=os.path.get.ctime)

ValueError:  max() arg is an empty sequence

then it goes to timeout

[Cal Learner]

max_hist_file = max(files, key=os.path.get.ctime)

ValueError:  max() arg is an empty sequence

That goes along with not having downloaded the history and positions CSV files from the Fidelity website.

I generally don't delete the old ones. I let them pile up, and let f2o.py figure out the

[Ellen S]

Ok, but here's the thing: that is what I got after I went back and downloaded the history and put into Downloads.   What I didn't do is also download positions.  Are you saying I need to also download positions to make this work?

[Ellen S]

PS - my C: search did eventually turn up my OFX files (including several I created with iCreateOFX as well as Pocketsense), but they did not include any new ones following the run.

[-Kevin N]

Hi Ellen,

Yes, you need both the Accounts_History and Portfolio_Positions csv files to be positioned in the Downloads folder.

-Kevin N.

[Ellen S]

Thanks Kevin.   Positions download now added.

1st try:  downloaded new history and positions csv files (both with today's date, even tho no new transactions since yesterday)

placed the files into c:users\ellen\downloads

ran f2o.bat

got same command report as reported yesterday.  

OFX search yields no new OFX files today, just the ones created yesterday when I ran Pocketsense (xfr folder).

2d try:  moved history and positions files to OneDrive Downloads (my default)

ran f2o.bat

same results

what to try next?

 

[-Kevin N]

Hi Ellen,

It would be my assumption that F2O.py is designed to look in the regular 'Downloads' folder for the 2 Fidelity CSV files.  I think you should move them out of OneDrive/Downloads and put them back in the regular Ellen/Downloads folder.

Let's try to simplify things a little...

Your Ellen/Documents/F20 folder should contain, at a minimum, the following 3 files:

F2O.py
F2O_DB.csv
F20.bat

F2O.py is Cal's CSV to OFX program.

F2O_DB.csv is the CSV with a listing of your Fidelity holdings.

F2O.bat is a batch file that runs F2O.py.
Using a text editor check to make sure F2O.bat contains the following:

cd %USERPROFILE%\documents\F2O
python F2O.py
timeout /T 30

Make a new folder in your Ellen/Documents/F2O folder named 'Files' and move everything except the 3 files mentioned above into the 'Files' subfolder.

Make sure that the Account_History and Portfolio_Positions csv files are in the regular Ellen/Downloads.

Click on F2O.bat and let's see what happens.

[Ellen S]

csv's are back in "regular" Downloads land (without any renaming like I did yesterday), folder slimmed down, F2O.bat checked--FWIW, the files were already conforming except I changed 'python f2o.py" to "python F2O.py" in case the case made a difference--still no joy sorry to say.  

Last year when I was trying to revive my Fidelity downloads via Pocketsense, having learned that others were successfully doing it, I ultimately traced the issue to "Fidelity.com" having an upper case "F" in Sites when it needed to be lower case.   I would not be surprised if something just as simple is at play here.  

One thing does occur to me, tho I can't see how it would matter:  I've downloaded from only one of my Fidelity accounts; is there any reason to think I need to download histories and positions from all 3?  

[-Kevin N]

Hi Ellen,

There is a mention of "All Accounts" in the section titled '3 F2O.py' of Dan's blog.  If you go to that section and scroll down to where is says 'Activity & Orders' (in red text).  It mentions 'All Accounts' So yes, I think it may have an effect.  

F2O, a Fidelity-specific CSV to OFX converter – MICROSOFT MONEY OFFLINE 🔴 Ameridan's Blog (wordpress.com)

[Jason J]

Hi Cal,

max_hist_file = max(files, key=os.path.get.ctime)

ValueError:  max() arg is an empty sequence

I had this error as well.  I usually change the name to Fidelity_dateperiod.csv when downloading so I'm able to easy understand the data period of that particular history file.

It'd be helpful to print an error message with recommended resolution (ie,   CSV Files not found in Downloads folder.  Please add Account_History and Positions CSV files to the Downloads folder) rather than just the ValueError which isn't helpful to most.

Also, F2O_DBgather.py appears to have a typo for the ctr increment +=1  (BGather instead of DBGather)

You might also need to define the variable as a global variable within the function since it's called later.

global DBGather_log_ctr  # Declare DBGather_log_ctr as global

def DBGather_logit(msg):
  if DBGather_log_ctr==0:
    file_mode="w" #start with empty file
  else:
    file_mode="a" #append
  with open("DBGather_log.txt",file_mode) as lf:
    lf.write(msg)
  BGather_log_ctr+=1

Separately, it seems that DBGather is reliant on "TICKER" to be included so some of my securities aren't picked up.   I have some Mutual Funds that are unique in that they don't have ticker symbols.    The only place I can get the current price is through Fidelity.com (or picked up by Money when imported through Fidelity OFX)

<MFINFO><SECINFO><SECID><UNIQUEID>4004<UNIQUEIDTYPE>CUSIP</SECID><SECNAME>FID LPS POOL CLASS O<FIID>4004<UNITPRICE>25.5100000<DTASOF>20240308160000.000[-5:EST]<MEMO>Market close as of 03/08/2024;FID LPS POOL CLASS O</SECINFO><MFTYPE>OTHER</MFINFO>

Unfortunately, I haven't had success yet.   I also notice most of the stocks that are found are strange in the CSV generated F2O_DB.csv file.  The first name looks normal, but the 2nd one has many cut off letters.

PFIZER INC,FIZER INC,STOCK,Cash,,,,,
TESLA INC COM,A INC COM,STOCK,Cash,,,,,
APPLE INC,APPLE INC,STOCK,Cash,,,,,

Thanks for your continued effort.

[Cal Learner]

Jason: heavy duty.

max_hist_file = max(files, key=os.path.get.ctime)

ValueError:  max() arg is an empty sequence

I had this error as well.  I usually change the name to Fidelity_dateperiod.csv when downloading so I'm able to easy understand the data period of that particular history file.

There is an undocumented feature to override the default CSV file locations. I think there is strong advantage for people to use the defaults, and you could consider making a specially named copy for your other purpose. But you don't have to. I am working on documenting that feature. Currently the working filename is advanced_f20.txt but I will probably choose a different name. Advanced is an inviting term. Maybe I will change that to unwise_features.txt. But anyway, here is a preview. If you add this line to your F2O_cfg.py file, it will look for files that match the pattern.

OVERRIDE_HISTORY_FILES=r'%USERPROFILE%\Documents\MySpecialPlace\Fidelity_*.csv'

Similarly, there is are OVERRIDE_HISTORY_FILES and OVERRIDE_WRITE_FOLDER too.

Since we are running on Windows, filenames are never case-sensitive.

Incidentally, if you replaced the backslashes with forward slashes, it would work and the r would not be needed.

It'd be helpful to print an error message with recommended resolution (ie,   CSV Files not found in Downloads folder.  Please add Account_History and Positions CSV files to the Downloads folder) rather than just the ValueError which isn't helpful to most.

I can work on an error detection for that file-not-found condition. Then print the better message. The ValueError was from Python itself.

DBGather_log_ctr=0 #global variable to know if log file should start fresh

Good point. Good find. Thanks. It now starts

def DBGather_logit(msg):

global DBGather_log_ctr

Separately, it seems that DBGather is reliant on "TICKER" to be included so some of my securities aren't picked up.   I have some Mutual Funds that are unique in that they don't have ticker symbols.    The only place I can get the current price is through Fidelity.com (or picked up by Money when imported through Fidelity OFX)

I compared. It seems that they have <FIID>4004 in the ticker spot. I just looked into <FIID> indeed the OFX spec, and indeed there it is. So what appears in the column C of the Portfolio_Positions_Mar-09-2024.csv (for example) file for that holding? 4004??? Note you can look at an F2O CSV file with a spreadsheet program, but don't write it back.

Regarding the longer cut off strings, I am not so sure this will cause a problem. I will try to think this over at a time where I might be more inspired.

[Jason J]

For the ValueError when the CSV files aren't found.  I simply added these to my F2O.py file before the code defining max_hist_file and max_pos_file as a reminder to myself.  I'm sure there are many other ways like try/except, or clearer messaging, but it was quick and simple. :)

hist_file_error_message = "Error: Fidelity's Accounts History CSV file is missing! Expected Location: {}"

if not files:

    exit(hist_file_error_message.format(OVERRIDE_HISTORY_FILES if "OVERRIDE_HISTORY_FILES" in globals() else homepath + file_type))

pos_file_error_message = "Error: Fidelity's Portfolio Positions CSV file is missing! Expected Location: {}"

if not filesp:

    exit(pos_file_error_message.format(OVERRIDE_POSITIONS_FILES if "OVERRIDE_POSITIONS_FILES" in globals() else homepath + file_type))

As for the special funds...they're interesting...

I don't know how rare/common they are though, so it might not be worth your time to incorporate anything for them.   This is from 401k account.  Like Ellen, I have multiple accounts at Fidelity.

Portfolio_Positions has information in the Symbol (Column C).  It's not my account number or anything.  Example of normal MF and the special ones.

FSELX - FIDELITY SELECT SEMICONDUCTORS PORT
31617E471 - FID GR CO POOL CL S
31617E596 - FID LPS POOL CLASS O

Accounts_History

The Symbol (Column D) is blank for those.  Action = Contributions or Exchange In or Exchange Out.  Price field Blank. Quantity = #Qty and Amount = $Amount

OFX from PocketSense.  No mention of 31617E596 from the Portfolio Positions anywhere.

I may look more into in later.  I was also looking why I end up in an infinite loop here "while len(ptext)>2: #process all lines in the file" and need to keyboard escape to end the code.  

It may have something to do with these special funds.  Later, when I manually edited the CSV files to remove all but a couple of stocks, it generated OFX files (I haven't tried importing those yet though....still exploring) :)

Thanks again for your effort in this program.

Thanks

-Jason

[Cal Learner]

I just uploaded f2o_beta7.zip

Thanks for the code. I had already made my version however. Yours is more elegant.

It does the missing csv file message.

It does the <FIID> thing in F2O_DBgather.py

It has a file named F2O_risky_info.txt  -- but now that I think about  it, maybe I should have named itF2O_risky_info_ignore_this_file.txt

[Jason J]

Ok.  I'll try beta7 tomorrow since I need to get to bed now, but before that came out,  I was playing some more and some of the file OFX writes failed since some of my Fidelity account Numbers are 5 digits instead of 9.  So it'd fail writing the file because the code extracts the last 9 characters for the account name.

I believe the 5 digit are subaccounts that represent a specific type of account (IRA, 401k, other retirement..etc)

Example failure (replaced my account number with #####)

    with open (write_folder+"Fid"+this_acct+dt_Ymd+".ofx", "wb") as an_ofx:
IOError: [Errno 22] invalid mode ('wb') or filename: 'C:/Scripts/f2o/ofx/FidAN" #####20240310.ofx'

      # this_account_string=split_text[T_acct][-9:] #last 9 characters is account number

      this_account_string=split_text[T_acct].strip().split()[-1]  # Split by space and get the last element

        # account=split_text[1][-9:] #just the last 9 characters of field 1... specific to Fidelity. maybe....

        account=split_text[1].strip().split()[-1] # Split by space and get the last element

Assuming that the account number is always last (that's an assumption), I changed the code to get the last element so that it doesn't hard code 9 characters.  

I haven't checked the other parts of the code for hardcode of 9 characters length yet though to see if that has a ripple effect....but at least it didn't fail writing the ofx with 5 digit account number.  :)

Thanks!

-Jason

[-Kevin N]

HI Cal,

Just confirming that F2O.py (beta7) now displays an appropriate error message if either or both of the Fidelity CSV files are not present in the Downloads folder.

[Cal Learner]

I took down f2o_beta7.zip, which had an error that crashed,  F2O_DBgather.py,  and put up f2o_beta7a.zip.

Kevin, thanks for that.

Jason, incorporated your code that is tolerant of account number of different lengths.

[Cal Learner]

I put  f2o_beta8.zip into the folder where you have downloaded things before.. It has some improvements that will not affect most people.

I also added a file named  F2O_MorningAfterFidelity.docx that discusses the expected actions after the Fidelity OFX server goes off.

There is a configuration file (F2O_cfg.py) that can be placed into the F2O folder. It will have a couple of items...

OVERRIDE_WRITE_FOLDER

 will tell the generated FID*.OFX files where to go. For many of us, that will be the PocketSense import folder. You can also feed OFX files to Money more directly, if you like.

IGNORE_BEFORE_YYYYMMDD

will tell F2O2 to ignore transactions that overlap those already brought into Money while the OFX server was working.

I anticipate writing  a batch file, named make_F2O_cfg.py.bat to generate that file, but the file can be created manually too. You don't have to really look at all of this now, but note there is a proposed plan that should work.

One other thing that I do is that I launch PocketSense with a batch file. I copy all of the new OFX file to a folder called oldofx in the F2O folder. This will accumulate OFX files that I could look at with a modified version of F2O_DBgather.bat to collect any available new data for the database.

[-Kevin N]

Hi Cal,

beta8 appears to be working as expected.

I tried tinkering with F2O_cfg.py.  I edited the "Date" for IGNORE_BEFORE_YYYYMMDD to "20240308".  F2O.py seemed to ignore the "Date" and provided transactions from before 3/8 when I imported the OFX files.

I noticed in F2O_cfg.py that there were 2 leading spaces in front of IGNORE_BEFORE_YYYYMMDD="20240308" and DEBUG=0, so I removed the 2 leading spaces and reran F2O.py.

This time F2O.py honored the Date and did not provide any transactions from before 3/8.  I also noticed the creation of an F2O_cfg.pyc file that was not generated before I removed the 2 leading spaces in F2O_cfg.py.

I know nothing of Python, so I cannot say definitively that the leading spaces were the cause of the issue but removing them did seem to resolve it.

[Cal Learner]

Kevin, thanks for trying these out and turning up bugs.

1. I will have to pay better attention to that. and make a point of it in describing creation of that F2O_cfg.py file. I had F2O_cfg.py.sample1 with two leading spaces on the lines, and Python is very picky on indentation! I have corrected my copies, and will incorporate that correction on subsequent download.

2. The *.pyc files are created by Python by default, and can be deleted any time you like. They are a compiled version of the corresponding *.py program. You will find those in your PocketSense folder also. They probably save a few milliseconds the next time the program was run. There is a software switch that can inhibit them being created.  When Python goes to run a *.py file, it checks to see if there is a corresponding *.pyc with a date/time newer than the *.py file. I remember getting bit by this in the past. While Windows is case-insensitive, Python is not. I forget the exact problem, but it involved an editor I no longer use changing filenames to all caps. This made my new changes to the *.py file to be ignored. This was very confusing for a while.

[-Kevin N]

Hi Cal,

I'm glad I could help out.

Thank you for the .pyc explanation. 

Just to confirm that the indent was causing the command to be ignored, I tried OVERRIDE_WRITE_FOLDER="C:/Users/Kevin/Downloads/" with AND without the indent.  It too was ignored with the indent but worked as expected without the indent.

[Cal Learner]

I just did a little experiment.

When I ran the problem code in a command window,  I got

IndentationError: unexpected indent

So I am thinking that it would be best to have the ofx.py program run from a batch file that ends with a pause command to let the user see any error message.

Alternatively, the batch level could check ERRORLEVEL and only pause in the case of an error.

[Cal Learner]

When I wrote the "batch level" I meant "batch file".

Here is my proposed new f2o.bat. Note that when I write "call python f2o.py" that python could be python.exe or python.bat, depending on how the path stuff is set up. Alternatively, that line could be replaced by "f2o.py" if your path and file associations are set up accordingly.  The call causes you to come back to the calling batch file.

The point of this is that if you run f2o.bat -- not from a command window but from File Explorer or clicking something on the desktop -- the command window that gets opened stays open to let you see the error information. Then pressing a key lets the batch file continue, which would close the window.

@echo off
rem proposed new f20.bat
cd %USERPROFILE%\documents\F2O
rem adjust the next line to best fit your installation
call python f2o.py
if not errorlevel 1 goto done
echo Python detected an error during execution of f2o.py program. Note error message above.
pause
:done

[-Kevin N]

Hi Cal,

I ran the proposed F2O.bat with 2 leading spaces in F2O_cfg.py for IGNORE_BEFORE_YYYYMMDD to "20240308" and 2 leading spaces for OVERRIDE_WRITE_FOLDER="C:/Users/Kevin/Downloads/"

It appeared to just ignore the indented commands as before.  

The window just flickered open and closed so I added a 30 second timeout to keep the window open.  

There was no error message displayed.

[Cal Learner]

Well DANG. I did my testing using a little file called play.py and I unfortunately extrapolated to the case where there was an include involved. So at this point, I guess I could stick to a warning to not have leading whitespace in that file.

I could alternatively read the  F2O_cfg.py  file if it exists, and test for that condition. Right now I am leaning toward just a warning.

I will keep the bit about a pause in F2O.bat  if the compiler reports an error, and I am thinking of a timeout /T 15 if no fatal error is detected by Python.

[-Kevin N]

Hi Cal,

I think that once you remove the leading spaces from F2O_cfg.py.sample1 and provide the file as such, doing so should alleviate the issue.  An additional warning regarding indented commands should resolve the matter once and for all.  Bug squished!

The ability of the program to report errors is always welcome.  A timeout T/15 should suffice.  It's certainly easy enough to change if the user wants it longer or shorter.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

I just tried out beta8, and got this error (I'm pretty sure there are no misplaced commas in F2O_DB):

Traceback (most recent call last):

  File "F2O_DBgather.py", line 177, in <module>
    i=findTickerInBiga(match.group(4)) #passsing the  ticker
  File "F2O_DBgather.py", line 52, in findTickerInBiga
    if (the_ticker==biga[i][0]):
IndexError: list index out of range

[Cal Learner]

Ameridan. I could not replicate the problem, but that is OK. I was writing up some debug procedure, but I decided that it would be simpler to have the program print info to the screen, where you could copy the messages and paste.

i uploaded F2O_DBgather.py.test8a.zip which contains a copy of F2O_DBgather.py  with some error handling at that point where the error hit. This could go into the next distribution, since it should behave the same as before if there is no error.

When you invoke F2O_DBgather.bat, you could invoke it as F2O_DBgather 4 if you want DEBUG level 4. If your F2O_DBgather.bat does not have %1 after the line that ends in \xfr, generate a new F2O_DBgather.bat with make_new_F2O_DBgather.bat.bat.  That allows the passing of a DEBUG number.

You can run without the debug number, or you could use a debug number greater than 1 to always cause a breakpoint early, but you can enter c to continue. With the default debug level, that error condition you hit will cause some info to print, and then things continue.  Then if the error occurs and DEBUG is greater than 1, after printing the extra messages, it invokes the debugger.

The  F2O_DBgather.py in  F2O_DBgather.py.test8a.zip could replace the  F2O_DBgather.py for later distribution.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

My  F2O_DB.csv  file contained investments that I had manually added for a Schwab experiment, but I know now that F2O won't handle Schwab files, so they no longer need to be included.   From memory, that error resulted from something wrong with F2O_DB.csv so this morning, I decided to just delete that file, since a new file is created if missing anyways.   I suspect that one of those extra entries got corrupted somehow (all it takes is an extra comma).     All is well now. 

I like the new code you added to help build the database for unknown cash sweep investments.

[Ellen S]

It seems my absence has helped the process.  Today I downloaded f2obeta8b.zip, ran make_new_F2O_DBgather.bat.bat, ran F2O.bat and found downloaded Fidelity OFX files waiting for import.  After checking them with Microsoft's OFX analyzer (just being careful), I imported each successfully to my Money test file.  Woo hoo!    I will continue to keep an eye open for more discovered issues/tweaks, but I'm satisfied F2O works very well for my needs.    

Many thanks to Cal and all the beta testers on this thread.   Well done!

 

[Cal Learner]

Thanks Ellen. I am happy to see that it worked for you, although there was a problem in f2o_beta8b.zip.

There is now  f2o_beta8c.zip. Only F2O.py differs. In addition to some small improvements, it prints out diagnostic info in the case Python detects a particular error.

The newer F2O.bat keeps the command window stay open  15 seconds after F2O.py finishes if there was no error detected. to let you view messages. If PYTHON detected an error that window stays open until you press a key.

I also note that Ameridan's  # 6. optional CSV auto-downloader module (with it’s own login credentials cfg file)   (#selenium)   on https://microsoftmoneyoffline.wordpress.com/2024/02/03/cals-fidelity-specific-csv-to-ofx-converter/ works for me.

At some point, it will make sense to have a batch file invoke that auto-downloader  and then F2O. The Windows scheduler could be used to start that batch file.

[-Kevin N]

Hi Cal,

Just a heads up, the F2O_cfg.py.sample1 file in f2o_beta8c still contains a leading space before the two commands.

Other than that, all is working as expected.

[Cal Learner]

Kevin: D'oh. On the F2O_cfg.py.sample1 I had edited it down, but unfortunately I just reduced from 2 leading spaces to 1. I will include the repaired no-leading-space F2O_cfg.py.sample1 in the next version.

[-Kevin N]

Hi Cal,

I am curious about the log_qq1.csv file that is generated when I run f2o.bat.

F2Obeta.docx, states "Also, there is a file called "log_qq1.csv" that can be written if DEBUG>0. This lists lines from your transactions that are not handled by F2O.py."

I have DEBUG=0 in F2O_cfg.py but the log_qq1.csv file is generated anyway. The contents of the log file reference my two money market funds.

Transactions pertaining to the 2 funds seem to be 'handled' by F2O.py as they DO appear in the generated Fidxxx.OFX files.

I should mention that the 2 money market funds in question are NOT produced by F2O_DBgather.bat in the resulting F2O_DB.csv file.  

I manually entered them as follows.

FDRXX,316067107,MF, FIDELITY GOVERNMENT CASH RESERVES
SPAXX,31617H102,MF, FIDELITY GOVERNMENT MONEY MARKET FUND

I also tried allowing F2O.py to enter the 2 funds opting for the 'MF' designation for each and opting for the 'U' Update giving me the following results in F2O_DB.csv.

FDRXX,zzzzFDRXX,MF,FIDELITY GOVERNMENT CASH RESERVE,,,,,
SPAXX,zzzzSPAXX,MF,FIDELITY GOVERNMENT MONEY MARKET,,,,,

There doesn't seem to be anything amiss, regarding the 2 funds within the generated Fidxxx.OFX files (their transactions are being handled) and I do have DEBUG= set to 0.

So, my question is... Is it still the expected behavior for the log_qq1.csv file to be generated given the above DEBUG=0 parameter and resulting 'handled' transactions in the Fidxxx.OFX files?

Oh, and welcome back Ellen. :)

[Cal Learner]

I have not thought about the log_qq1.csv  for a good while. IIRC, it overwrites the existing file each time.

Now why were those two MMFs not picked up by F2O_DBgather.py? There is a good chance that you use the Fidelity scrubber that removes the buy and funds used for sweeps, so it is interpreted by money as more like cash. To test for that, I could modify the scrubber to not remove those, and run  F2O_DBgather.py again. See if they get inserted.

In the Portfolio_Positions files, the core position comes in with ** appended to the symbol for each account ("FDRXX**"). That could be used to simply not even do a lookup if the symbol has two asterisks. But then what do you do about the dividends paid? So handling that by editing the  F2O_DB.csv to add would put things at the way they are now. Letting F2O.py generate the symbol for F2O_DB.csv works too, but in that case when you get a transaction, you would want to tell Money that you already have that security in Money, and select it from the drop-down list. Thereafter Money will expect the new  zzzzSPAXX (for example) CUSIP to be used. There is also a feature in Money Plus where you can have Money recognize two CUSIPs for the same security. I have not played with that for a good while.

====================CombineWith=============================

The CombineWith operation;  Money Plus continues it.

The CombineWith operation was motivated largely by the Fidelity Net
Benefits quirk of having a wrong "CUSIP" ID in their download.  So
downloading data from a source with a correct CUSIP and from them
did not allow Money to understand they were the same stock, and
could share the same symbol and quotes.

First, keep an extra copy/backup of your file, so that you can undo
something.  Then accept Contrafund K as a new fund, for the moment.
The way the CombineWith operation works is that there is a
CombineWith button on the Details page.  You can select a security
of the SAME Investment Type.  When you go to Contrafund K details
and click CombineWith, a list of mutual funds already defined comes
up.  A pop-up tells you that all of this investment will be renamed
as the other selected investment.  If you combine with Fidelity
Contrafund (Symbol: FCNTX), in the register, All Contrafund K
transactions will now appear as Fidelity Contrafund (Symbol:
FCNTX).  I suspect that there is a hidden table that will now say
that both CUSIPs will henceforth refer to Fidelity Contrafund
(Symbol: FCNTX).

===================================================

[Cal Learner]

1. CORRECTION is cumulative at this point. Delete it any time you like.

2. I plan to modify line 840 from 

log_qq1("last else")

to

 if DEBUG>0: log_qq1("last else")

to match F2Obeta.docx,

[-Kevin N]

Hi Cal,

I don't use the Fidelity scrubber.  I track all MMF transactions.  

Could the asterisks be causing the MMF(s) to not be included in F2O_DB.csv?

[-Kevin N]

Hi Cal,

Just confirming that F2O_DBgather.bat successfully added a new investment to F20_DB.csv as expected.

[Cal Learner]

Kevin, F2O currently ignores the asterisks. I may in the future make things default to using those asterisks to identify the core fund, and remove those transactions, as the scrubber does. I would want to make sure that there was a way to disable that feature.

I am glad that adding your securities using the feature in F2O.py worked for you.

In new future zip files that I upload, I plan to include a file called F2O_DB.csv.seed. This can serve as a starting point for F2O_DB.csv (the database file). It also serves as an example of what entries for various kinds of security look like. It also includes a header line, that F2O will ignore. Most recently, I have added entries for various Fidelity core position. Somebody using F2O_DB.csv.seed contents as a starting point, either before using F2O_DBGather or somebody starting with F2O, would not have to enter the values for the core. Also, that file contents could be merged into the gathered database. Just remember that spreadsheet programs will normally mess up the database.

I have placed F2O_DB.csv.seed for separate upload temporarily. If there are core positions, or other entries that would logically be added to F2O_DB.csv.seed, post the info.

[-Kevin N]

Hi Cal,

I've run into an issue with one of the Fidxxx.ofx files generated by f2o.bat.  It throws the following error in MS Money:

"The file you attempted to import appears to be invalid or contains corrupt data.  Please download the file again.  If this problem continues, contact your financial institution."

I've narrowed down the offending security to FIDELITY STRATEGIC DIVIDEND & INCOME

If I remove FIDELITY STRATEGIC DIVIDEND & INCOME transactions from Account_History.csv and then run f2o.bat, the generated Fidxxx.ofx file will then import into MS Money as expected.

I'm not sure how to proceed from here.

-Kevin N.

[Cal Learner]

The name should appear in the  F2O_DB.csv file as  "FIDELITY STRATEGIC DIVIDEND &amp; INCOME"

I suspect I failed to translate the ampersand to  &amp; when generated by F2O.py.  F2O_DBgather.py does that, and so should  F2O.py. Thanks.

[-Kevin N]

Hi Cal,

Thank you for your reply.  The following is the line from F20_DB.csv.

FSDIX,316145887,MF,FIDELITY STRATEGIC DIVIDEND &amp; INCOME

So, that looks correct.

[Cal Learner]

Do you know how to use OFXAnalyzer? If so, have it analyze the problem file.

Ideally it will detect an error. Copy and paste the error message into a post.

For example, I munged up an OFX file on purpose, and got 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    = 11
Severity      = X
Type          = C
Error Message = XCNo element declaration for %s end-tag GI; end-tag ignored
Other Info    = MNYSGMPB: SGML error at (null), line 168 at ">":
          XCNo element declaration for INVVVPOS end-tag GI; end-tag ignore

============================================

Line 168+10 is line 178.The line I messed up was line 178.

Can you then post the record that surrounds the suspect line? You can change numbers/amounts if you like.

[-Kevin N]

Hi Cal,

Unfortunately, the analyzer didn't seem to find any problem with the file...  Weird.

*** All line number references do not include header lines ***
***OFX 1.02 Add ~10
***OFX 2.0 Add ~2
Running Data Through Parser

No Parse Errors

Reading Data Into Buffer
Tokenizing Data
Initializing Data Structures
Analyzing Data
Verifying Security Types
Verifying Security Uniqueness
Verifying FITID Uniqueness
Verifying Sign Correctness
Verifying Total Calculations
Verifying Other
Done Analyzing File