1. hleofxquotes-team Avatar hleofxquotes-team
  2. open
  3. dist-sunriise

Wiki

Clone wiki

dist-sunriise / sunriise-export Field Guide to parsing the CSV files

View History

Field Guide to parsing the CSV files

The sunriise export tool will create the following CSV files

  1. accounts.csv
  2. categories.csv
  3. currencies.csv
  4. filteredTransactions.csv
  5. payees.csv
  6. securities.csv
  7. transactions.csv

Each file roughly represents a table.

Some examples to help you get a feel of the available data

ID column

In each file, each row will ALWAYS has a column named "id" which is a positive and unique number. For example: 

"id","name","isoCode"
"1","Argentine peso","ARS"

Any column name that ends with a word 'Id' (such as relatedToAccountId) is a 'foreign key' (or pointer) to another row in another file/table. For example:

"id","name","accountType","currency","startingBalance","relatedToAccountId","relatedToAccountName","closed","retirement","investmentSubType"
"66","Woodgrove Bond Account","INVESTMENT","USD","0.00","67","Woodgrove Bond Account (Cash)","false","false","NOT_APPLICABLE"
"67","Woodgrove Bond Account (Cash)","BANKING","USD","0.00","66","Woodgrove Bond Account","false","false","NOT_APPLICABLE"
  • Woodgrove Bond Account has id=66 and relatedToAccountId=67
  • Woodgrove Bond Account (Cash) has id=67 and relatedToAccountId=66

Diagram showing how the table's id are related

Date type

  • Date has format 'yyyy/MM/dd'. For example: 2006/07/15

Simple tables

  1. currencies.csv
  2. payees.csv
  3. securities.csv

currencies.csv

currencies.csv is one of the "simpler" table. It has 3 columns

  1. "id": id of the currency
  2. "name": name of the currency
  3. "isoCode": isoCode of the currency

Currency is used in the account table: column name "currency".

payees.csv

payees.csv has 3 columns

  1. "id": id for the payee
  2. "name": name of the payee
  3. "parentId": parent id of the payee. In theory, payee can be hierarchical but I have not actually seen one yet.

securities.csv

securities.csv has 3 columns

  1. "id": id of the security (stock, mutual fund ...)
  2. "name": name of the security
  3. "symbol": symbol of the security

Tables with foreign keys

  1. accounts.csv
  2. categories.csv
  3. filteredTransactions.csv
  4. transactions.csv

accounts.csv

This table contains a list of accounts. The columns are

  1. "id": id of account.
  2. "name": name of account.
  3. "accountType": account type:
    1. BANKING(0)
    2. CREDIT_CARD(1)
    3. CASH(2)
    4. ASSET(3)
    5. LIABILITY(4)
    6. INVESTMENT(5)
    7. LOAN(6)
    8. UNKNOWN(-1) // Please log a bug/issue if you see this
  4. "currency": account currency (see: currencies table)
  5. "startingBalance": starting balance
  6. "relatedToAccountId": for example, an investment account can have a Cash account has its related account
  7. "relatedToAccountName": relatedToAccount name
  8. "closed": true if this account is closed.
  9. "retirement": true if this account is a retirement account
  10. "investmentSubType": if this is an investment account, it could have subtype such as 401k,
    1. _403b(0)
    2. _401k(1)
    3. IRA(2)
    4. KEOGH(3)
    5. RETIREMENT(11)
    6. NOT_APPLICABLE(-1)
    7. UNKNOWN(-2) // Please log a bug/issue if you see this

categories.csv

This table contains a list of categories. The columns are

  1. "id": category id
  2. "name": category name
  3. "parentId": parent id. This is used to form a category and subcategory relationship.
  4. "level": level of the "tree". top-level has level 0. Children of top-level will have level 1 and so on ...
  5. "classificationId": (TODO) ????

Category can be hierarchical. For example 

"id","name","parentId","level","classificationId"
"160","Bills","131","1","0"
"161","Cable/Satellite Television","160","2","0"
means the full name of categoryId=161 is 'Bills:Cable/Satellite Television' (id=161 has parentId=160')

filteredTransactions.csv

This table has all the transactions that DOES NOT contribute to the accounts' balance. In general, I think these transactions are the "template" for the recurring bills. The columns will be the same as that in transactions.csv

transactions.csv

This table is the most important one. There a couple of things I want you to know about the data in this table

A transaction

  • can have splits: a list of "sub-transaction". Typical example:
    • a mortgage payment has principal and interest transactions
    • or a paycheck has pre-tax contribution, health insurance payments, direct deposit (transfer) to multiple checking/saving accounts.
    • See columns: splitsCount, splits, splitParentId
  • can be a transfer. In such case, you want to know about the "other-side" of the transaction.
    • See columns: transfer and xfer*
  • can be an investment transaction. For example: a stock purchase. In such case, you are likely will want to know the purchase price and quantity.
    • See columns: investment and inv*

Some examples to help you get a feel of the available data

There are a lot of columns. So let's put them into groups.

Basic data

  • "id": transaction id.
  • "date": transaction date (has format 'yyyy/MM/dd'. For example: 2006/07/15
  • "number": Typically a check number BUT note that it is really a free-form column: the data type is actually a string.
  • "amount": transaction amount
  • "memo": memo line
  • "fiTid": financial institution transaction ID (used in OFX download to unique identify a transaction)

Category

  • "categoryId": category id
  • "category": category name for this transaction. If it is a transfer, the category name will be 'Transfer from Woodgrove Bank Credit Card) and the other side of the transfer will have a transaction that say 'Transfer to Woodgrove Bank Checking'

Payee info

  • "payeeId": payee id
  • "payee": payee name

Split info

  • "splitsCount": if value > 0, then this transaction has splits (a list of sub-transactions)
  • "splits": a list of splits in the following format
    • rowNumber:transactionId/rowNumber:transactionId/...
    • for example: 1:2026/2:2027/3:2028/0:2025. That means there are 4 items in the splits: row=0 has transactionId=2025, row=1 has transactionId=2026 ... and so on
  • "splitParentId": if a transaction is a split-transaction it's splitParentId will be non-empty and has the transactionId of its parent. So the parent-children relationship is established as followed
    • parent has a list of children via column: splits
    • child has a pointer to its parent via column: splitParentId

Account info

  • "accountId": account id that this transaction is associated with.
  • "account": account name

State info

  • "void": if true, this transaction is voided.
  • "cleared": if true, this transaction is cleared.
  • "reconciled": if true, this transaction is reconciled. As far as I can tell, for msmoney, void, cleared, and reconciled are mutually exclusive.
  • "unaccepted": if true, this transaction is unaccepted: downloaded by has not been accepted yet.

Transfer info

  • "transfer": if true, this is a transfer transaction: there is an "other side" of this transaction in another account.
  • "xferAccountId": the "other side" account id
  • "xferTransactionId": the "other side" transaction id. If you import this list of transaction, you can use the linkage to make sure that you don't create duplicate transactions.

Investment info

  • "investment": if true, this is an investment transaction.
  • "invActivity": investment activity 
        static final String STOCK_OPTION_EXPIRE_ESOS_STR = "Expire (ESOs)";
    static final String STOCK_OPTION_VEST_STR = "Vest";
    static final String STOCK_OPTION_GRANT_STR = "Grant";
    static final String ACTIVITY_UNKNOWN_STR = "ACTIVITY_UNKNOWN";
    static final String BUY_STR = "Buy";
    static final String SELL_STR = "Sell";
    static final String DIVIDEND_STR = "Dividend";
    static final String INTEREST_STR = "Interest";
    static final String RETURN_OF_CAPITAL_STR = "Return of Capital";
    static final String REINVEST_DIVIDEND_STR = "Reinvest Dividend";
    static final String REINVEST_INTEREST_STR = "Reinvest Interest";
    static final String REMOVE_SHARES_STR = "Remove Shares";
    static final String ADD_SHARES_STR = "Add Shares";
    static final String S_TERM_CAP_GAINS_DIST_STR = "S-Term Cap Gains Dist";
    static final String L_TERM_CAP_GAINS_DIST_STR = "L-Term Cap Gains Dist";
    static final String REINVEST_S_TERM_CG_DIST_STR = "Reinvest S-Term CG Dist";
    static final String REINVEST_L_TERM_CG_DIST_STR = "Reinvest L-Term CG Dist";
    static final String TRANSFER_SHARES_IN_STR = "Transfer Shares (in)";
    static final String TRANSFER_SHARES_OUT_STR = "Transfer Shares (out)";
  • "invSecId": investment security (stock, mutual fund ...) id (see table securities)
  • "invSecName": investment security name.
  • "invSecSymbol": investment security symbol
  • "invTxPrice": investment security price
  • "invTxQuantity": investment security quantity. Note: if (price * quantity) != amount, then that means there are other miscellaneous cost such as commission.

Recurring info

  • "recurring": if true, this transaction is recurring transaction (typically such transaction will be in the filteredTransactions table.
  • "frequency":
    • Non-recurring
    • Only once
    • Daily
    • Weekly
    • Every other week
    • Every four weeks
    • Twice a month
    • Monthly
    • Every other month
    • Every four months
    • Every three months
    • Twice a year
    • Yearly
    • Every other year

Classification1 info

  • "classification1Id": (TODO) ???
  • "classification1Name": (TODO) ???

Updated