Import .csv file into an investment account

PaulV's Avatar

PaulV

28 Nov, 2022 01:05 AM

I have a 401k account that only allows me to download transactions in the form of a .csv file. I tried to import that file into Moneydance, which I can do, but the problem is Moneydance does not offer the appropriate fields to associate with the number of shares and price. It looks as though the only fields that are offered are those associated with a checking or savings account. Is there a way to add additional fields or modify them? Or is there some other work around?

Thank you

  1. 1 Posted by dtd on 28 Nov, 2022 01:12 AM

    dtd's Avatar

    just a user - basically, investment accounts and csv files don't mix, as there are no standards on what an investment account csv file should look like.

    Sure, you can import that file into excel and see what data is there, but with the inconsistency of formatting AND of field presentation, it is very hard to import into a personal finance program.

    Someone else with more knowledge can give a better explanation.

    As to a workaround - what bank/firm are you using? Is there a way to link to it via OFX or through Moneydance+ so that you get a format that is recognized by personal finance software?

  2. 2 Posted by dwg on 28 Nov, 2022 03:00 AM

    dwg's Avatar

    I'm a fellow customer.

    I'll try to expand on what dtd has stated.

    While consistently formatted CSV files can be imported into bank, credit card, asset, and liability type accounts it is not possible to import a CSV file into an Investment or Loan Account.

    CSV files are just a table, hence it is possible to map this table into the columns and fields that are present in non-investment or loan accounts. Non-investment accounts only have a small number of mandatory fields, things like date and amount, there is a minimal risk of this data being absent and thus the files being problematic.

    Investment accounts however are not a simple table. The data that is in a record in such accounts is driven by the contents of the action column, which in turn determines what fields are available, which fields are mandatory, which are optional etc. Certain fields in Investment accounts are only allowed to have certain values as well, this is especially notable with the action column, there is no scope to have any thing other than the pre-defined values, also the contents of the security field can only be the securities that have been pre-associated with this investment account.

    The only CSV files that seem to have come close to being suitable are those from other personal financial programs and programs like Excel have macros available for them that can be installed to create QIF files from this data which can then be imported into Moneydance, of course you would only be working with such files when migrating to Moneydance.

    This is a brief overview but hopefully explains that given the inner workings of investment accounts it makes them unsuitable for the sort of simple mapping that you can do with CSV files.

  3. 3 Posted by PaulV on 29 Nov, 2022 01:26 AM

    PaulV's Avatar

    Thank you, dtd and dwg. I'll look into finding and installing macros in Excel to produce QIF file. That would be ideal, if I can get it to work.

  4. 4 Posted by dwg on 29 Nov, 2022 02:10 AM

    dwg's Avatar

    XL2QIF is one of the better known free macros. The main point however is that such macros require pristine input, they do not restructure data, or do things like substitute invalid values, they also do not typically do any error checking, any work like to get good input needs to be performed before the macro is called. If you are looking to do that in Excel then it is probable that you would have to write VBA code.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac