importing csv

[oyz79]

I'm trying to import a csv file using the following format but it says 'could not find all the specified fields in the importing file"

SYMB TRAN UD ANUM BNUM CNUM Transaction Description
DBC Buy 110103 27.83 119 0 purchase
VNQ Buy 110103 55.76 59 0 purchase
VTI Buy 110103 65.36 50 0 purchase
VTI Sell 110601 69.77 50 0 redemption
VEU Buy 110601 50.26 72 0 purchase
VEU Sell 110701 49.68 72 0 redemption
VTI Buy 110701 68.43 53 0 purchase
VTI Sell 110831 62.78 53 0 redemption
BND Buy 110831 83.4 40 0 purchase
VNQ Sell 110930 50.87 59 0 redemption
DBC Sell 110930 25.75 119 0 redemption

[Mark]

Hi oyz79,

What are you entering into the importing dialog for the format string? This is technically not a CSV file, as the fields aren't separated by commas. Are those tab characters between the fields? A good tip is to use Notepad or a text editor to view your importing file, so you can see how it actually looks. If those are tab characters, you would need to enter a format string of:

SYMBTABTRANTABUDTABANUMTABBNUMTABCNUMTABXX

The TAB keyword matches on the tab character. If those are actually commas in the file, replace the TAB keys in the above string with commas.

[oyz79]

Thanks, I was able to get it with commas. The "sells" imported as buys into the program but I was able to manually change them. Should they be listed with a "-" before the shares or amount for future reference?

[Mark]

Hi oyz79,

Glad you made some progress. Sell transactions should be specified with a positive number for shares. Did you have TRAN in y our format string, and have SELL specified as your transaction key? If you want to attach a sample CSV or TXT file along with the format string you're using that isn't importing sell transactions, I can take a closer look.

[oyz79]

I tried both "redemption" and "sell"

[Mark]

Hi oyz79,

What were you using for an "Input Format" string? The first line of your file doesn't matter, it matters what you type into the import dialog for an input format string.

The proper key to use is SELL, and the shares should be specified as a positive number.

Here is a sample line from your file:

"VTI","Redemption",110601,69.77,-50,0

Notice, you have double quotes around some of your fields. If you're viewing this in Excel you may not see this, but they are in the file. It is always a good idea to view your file with a text editor, like Notepad, so you can see the real format. You would need to change the key to SELL, and the shares to a positive number like this:

"VTI","SELL",110601,69.77,50,0

and then for the input format string, enter:

"SYMB","TRAN",UD,ANUM,BNUM,CNUM

Notice how there are double quotes around the same fields in the input format string as you have in your file. This is necessary to match what you have in your file.

[oyz79]

Awesome, thanks. It is working fine now and I am uploading portfolios without a hitch