Edit F2O_DB.csv with Excel

[Dan Battagin]

Cal has been his usual fantastic self and provided us all with the F2O package that allows us to (relatively easily) continue to download our Fidelity information for import into Money.

That package creates a CSV file (F2O_DB.csv) that contains a database of security information used by the F2O.py script, and in the documentation, Cal rightly warns users from using a spreadsheet application to edit that CSV file. Doing so, he notes, can lead to corruption of the file because spreadsheets have a tendency to convert strings of numbers into actual numbers (0012345 becomes 12345), and to represent certain large numbers using scientific notation (12345678901 becomes 12346E+10).

As I was getting everything set up, I ran into a couple of bugs (really, feature requests to support additional Fidelity account types), and Cal and I took our conversation out of the group and into email as we got things sorted out. Cal then released F2O_beta14.zip with the improvements.

While we were emailing, I divulged that I had some expertise in Excel and Cal requested that I put together a tool that would allow editing of the F2O_DB.csv file in Excel. Naturally, the best "tool" to edit a file in Excel is Excel itself!

Cal was nice enough to serve as my tester and general QA engineer, and I'm now ready to present the first version of F2O_DB-Editor.xlsm (zipped up for download / email attachment). Cal may also decide to include this editor in future versions of his F2O suite, which would be quite an honor.

https://1drv.ms/f/c/592b05f76554281f/IgDkhWXzWCc9TqcUvHw7KbgYAZhR0Idi-ciNzAD-cyb3Y6E?e=dToJI9

Instructions for using the file are contained on the first sheet of the file itself, but I'll include them here as well since this is the first time I've made the tool available.

Feel free to give it a try, and let me know if you have any questions.

Cheers,
Dan

Instructions - - - - - - - - - - - -
1. Unzip and then open the Excel file and enable macros.

Note: If you don't see a yellow bar across the top to enable macros, but instead see a red bar, you must first "unblock" the file. To unblock the file
A. Close the file.
B. Open Windows Explorer and find this file. Right click > Properties
C. In the Properties dialog, near the bottom is a "Security" section. Check the box to "Unblock" the file.
D. Click OK to close the Properties dialog and then re-open the file.

2. Click the [Load F2O_DB] button
Browse to, and select your F2O_DB.csv file.
Data from your F2O_DB.csv file will be loaded into the F2O_DB.csv sheet.

3. Make any modifications to the CSV data on the F2O_DB.csv sheet.
Do no use commas in any entries/edits, since those are used in the CSV file to separate values.
Verify that numeric values are stored as text (not numbers). A green triangle should appear in the corner of the cell to indicated this, and values should remain as typed.
4. Click the [Save CSV] button
If there are any commas in your entries/edits, you will be prompted to replace them and save, or cancel the save operation.
if SAVE:
A backup of your existing F2O_DB.csv will be made as F2O_DB-YYYY-MM-dd-HH-MM-ss.csv, and a new version will be saved with the data from Excel.
if CANCEL:
No file will be saved.
If you do not like the replacement text - by default it is " " (space) - you can click the [Set Replacement Char] button to enter your own replacement character.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

Nice job!  I'm still on Excel 2007, and it worked great. 

I had previously changed my file association of csv files to EditPlus3, a text editor, to overcome the shortcomings of Excel (without your macros), and I will leave that in place, but your tool allows me edit the data in columns, as intended.

I linked this thread to my F2O_DB module, to help promote your tool.

[Cal Learner]

Note that you should not feel a need to edit your F2O_DB.csv database. But if you want to do so, or just look through it, this offers a way to avoid the pitfalls of having your database corrupted by unwanted transformations if you just open the csv as a spreadsheet. Edit it with notepad or notepad++, fine. But using a spreadsheet program directly takes special effort.   F2O_DB-Editor  saves you the effort of learning about the precautions needed.

 F2O_DB-Editor also makes automatic timestamped backups when you make a change.

Do the Windows security unblock on F2O_DB-Editor.xlsm before opening.  F2O_DB-Editor tells you about that, but only after you have first opened it. If you do, no problem. Exit. Unblock, and come back into  F2O_DB-Editor.

https://bin95.com/articles/safety/unblock-downloads.htm gets to the point sooner than most articles on how to unblock.

Inside F2O_DB-Editor, use the green navigation buttons to access you  F2O_DB.csv database. If you use the File or regular commands, you are working on   F2O_DB-Editor.xlsm itself,  rather than   F2O_DB.csv