013: Data Import (Part 1)

Data import is one of the Stocklog+ superpowers! Brokers may provide CSV files, but no one said they had to be perfectly formatted, and most are not!

This is why very few brokers or journal applications attempt to import competing broker CSV files. Stocklog+ importer is pretty good at 'sniffing out' the correct headers, so most of the time no additional adjustments are needed.

Stocklog+ attempts to make this easy and there are rules you have to follow for successful imports:

1. Stocklog+ only looks for .CSV files
   
2. CSV files must reside in the data_import folder
3. The filename can be anything you want:
   
1.  BUT the account number must match what you entered when creating accounts
   
2.  Account number must be all UPPER CASE 
3. Separate the account number with an UNDERSCORE ('_') from the rest of the filename - account number can be at the front or back

TIP: Create a 9999 - DUMMY ACCOUNT. Use this as your test account.

RIGHT-CLICK on the active account and select Import CSV, if a file is located it will be imported and moved to the completed folder. If you have multiple files for the same account you will need to repeat the import request.

The following is a list of the primary header & Short Description used in Stocklog+ and some known alias headers you may see within your CSV files

1. Using the primary headers listed below in your CSV file will usually result in a 100% success rate
   
2. If your header has one of the alias headers, it should also import with no problem
   
3. The order of the columns in the file doesn't matter, just be sure to match the header to the correct content (i.e. 'shares header' should be located in the column that contains shares). Once you have a good header, it should rarely change for that broker

PRIMARY HEADER	DESCRIPTION	ALIAS
ticker	Stock Ticker Symbol (AAPL, TSLA, RIVN)	'symbol', 'security', 'instrument'
account_id	Account Number (Chars, Numbers & '-')	'account', 'acct', 'accounts'
ttype	Trade action: buy, sell, STO, STB, STC, Call, Put, Option chain	'security type', 'order type', 'side', 'trans code'
shares	Unit of measure for the security	'quantity', 'qty', 'total qty'
pps	Price Per Share	'price', 'share price', 'executed price'
tdate	Date trade was made	'run date', 'trade date', 'filled time', 'process date', 'placed time'
settle_date	Date the broker settle/close the trade	'settlement date', 'settle date'
exp_date	Option expiration date. Only use if you trade options and the csv file contains an expiration date column	'expired'
fees	Your broker should split out fees paid per trade	'fee'
comm	Broker may have this field. Will be added to fees	'commission'
total_price	Usually the shares * pps	'amount', 'net amount'
option_strike	Option strike price. Only use if you trade options and the csv file contains a strike price column	'strike'
close_price	Price the security was sold/closed	'closing price'
action	Description of an action taken on the security	'status'
desc	Description of what the row contains	'description', 'transaction description', 'security description', notes

REQUIRED FIELDS: Ticker, Account_ID, TType, Shares, PPS, TDate

TIPS & Suggestions:

1. Load your CSV file into a spreadsheet to see if it will even load properly, you will have better success if you remove blank lines, page numbers, random paragraphs, etc. from the top and bottom of the file. Stocklog+ will attempt to remove such items, however, pre-cleaning the file is one less chance of errors showing up
   
2. Make sure the number of headers match the number of items of the row. I.e. You count 9 headers, you should have 9 items separated by commas.
   
3. Check the log file (stocklog.log) for potential clues if the file continues to fail
   
4. Remove special characters by loading into something like 'Notepad' then immediately save it back out
   
5. You can leave miscellaneous columns in the file as-long-as the headers & rows match in length
   
6. If you have any issues using the basic import, use D.I.M.E.

Every time you successfully import, the original file will be moved to stocklog/imports/completed/ 

• Related Articles

• 014: Data Import (Part 2)

  See Data Import (Part 1) for header details Below are header samples with a few lines of the raw (Account field was added) .csv file to give you guidance. Each header used here worked at the time of document creation. Try using the headers below as a ...

• 015: Data Import Management Engine (D.I.M.E.)

  The absolute best way to achieve nearly perfect imports is to use D.I.M.E. for each broker you import data. Brokers rarely change the format of their CSV files, this gives us a chance to take advantage of having YOU visually inspect the columns to ...

• 001: Where to download Stocklog+

  Always download files from https://www.xenminds.com to make sure you're receiving the most up-to-date version. Once you're on the download page, choose any of the three locations to download the application: FULL: Download this version if this is the ...

• 010: Trade Details (Part 3)

  See 008: Trade Details (Part 1) for the start of this series Using the existing previous trades in Trade Details (007-009), we add the following on 2024-12-05: -1 2024-07-03 TSLA CSP @290 (Sell Cash Secured Put) - Expires Worthless 100 2024-08-01 ...

• 008: Trade Details (Part 1)

  See 007: Manually Adding Trades for a refresh on the trade column header. 0: Parent ID: The unique ID for this trade, it never changes and can not be modified 1: Child ID: Used to move a row under another, making it a child of the parent. You can ...