Importing csv files into Stocklog+

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 of them are not! This is why very few brokers or journal applications attempt to import multiple company CSV files.


We try to make this easy and there are rules you have to follow in order for successful imports:
  1. Stocklog+ only looks for .CSV files
  2. Stocklog+ only checks once at start-up
  3. CSV files must reside in the data_import folder
  4. 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.  
    4. Separate the account number with an UNDERSCORE ('_') from the rest of the filename - account number can be at the front or back
  5. The CSV file is automatically imported if an account is active
  6. Stocklog+ moves successful import files to the import_complete folder

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

The following is a list the 'primary header + Short Description -> alias headers'
  1. Using the primary headers 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'

Difference between ACTION & DESCRIPTION:

Some brokers, like Fidelity, have both columns making it easy enough to keep both and Stocklog+ will figure out which one to use. If you only have one column describing the row of data, it will probably have one of the headers listed under 'desc' ALIAS. If you have to create a header, between 'action' & 'desc', use 'desc' or 'description'.
Description examples: 'Name of Company' / 'PUT (BROS) DUTCH BROS INC DEC 16 22 $33 (100 SHS)'

An action is something that was done, i.e: 'You Bought...' / 'You Sold...' / 'Reinvestment...' / 'DIVIDEND RECV...' / 'ASSIGNED...'

Alert
Most brokers DO NOT include your account number in the CSV, you will need to add it (usually at the end). If you don't want to edit the file, try using D.I.M.E.

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. You will most likely need to add an additional header of 'account' and enter the account number in every row, this can be quickly done by dragging the cell in the spreadsheet down, just be aware that you're not adding-by-one .vs. copying the cell
  3. 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.
  4. Check the log file (stocklog.log) for potential clues if the file continues to fail
  5. Verify the file is a .csv file by loading it into something like 'Notepad', that will strip any special characters out when you save it again
  6. You can leave miscellaneous columns in the file as-long-as the headers & rows match in length
  7. Use account 9999 to test your imports; you can select the account, select all rows and press DELETE to remove them. In your file, use find/replace if you already have an 'account' column - i.e. FIND: X900-1234 REPLACE: 9999. Then change the account number back to the original when you get the header right
  8. If you have a large file, it's easier to create a smaller test file of 10-20 rows and work with that to get your headers right, then copy the header into your larger file
  9. If you create a custom header, be sure to save it to be used as a template for all files with that broker or use D.I.M.E.

Notes
Every time you import, the file will be moved to data_import/import_complete/. You should clean the folder up after multiple attempts


    • 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 ...

    • 008: Trade Details (Part 1)

      Using the input from the previous input from 007: Manually Adding Trades, with addition of adding 1 share, we now have three trades. Lets go over the Trading pane details: Info: Multi-use column, can be used to show different statuses of a trade. See ...

    • 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 location to download the application. All versions are the same, the difference in file size ...

    • 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 ...