Spreadsheet formulae for validating/verifying CoinTracking data

cointracking-spreadsheet-formulae.md

CoinTracking Spreadsheet Formulae

Spreadsheet formulae for validating/verifying CoinTracking data.

Table of Contents

• Cumulative Total
• See Also
  • My Other Related Deepdive Gist's and Projects

Cumulative Total

For a CoinTracking export with headings in columns A-K like this, and newer transactions on top:

Type,Buy,Cur.,Sell,Cur.,Fee,Cur.,Exchange,Group,Comment,Date

We can create a new column in L1 with the name of the asset we want a running total of (eg. NEXO), then starting from L2, we can add this formula and fill it down the column:

Note: Check your data and see if there are any other cases that need to be handled as well

=L3 + IFS(
  AND($A2="Deposit",    $C2=L$1), $B2,
  AND($A2="Withdrawal", $E2=L$1), -1 * $D2,
  AND($A2="Trade",      $C2=L$1), $B2,
  AND($A2="Trade",      $E2=L$1), -1 * $D2,
  AND($A2="Other Fee",  $E2=L$1), -1 * $D2,
  TRUE, 0
)

If we also wanted to add another column where we could add manual adjustments to be included, we could add another title in M1 (name doesn't matter, but lets use NEXO Adjustment for likely duplicate), then starting from L2, we can add this formula and fill it down the column instead:

=L3 + IFS(
  AND($A2="Deposit",    $C2=L$1), $B2,
  AND($A2="Withdrawal", $E2=L$1), -1 * $D2,
  AND($A2="Trade",      $C2=L$1), $B2,
  AND($A2="Trade",      $E2=L$1), -1 * $D2,
  AND($A2="Other Fee",  $E2=L$1), -1 * $D2,
  TRUE, 0
) + IF(NOT(ISBLANK($M2)), $M2, 0)

See Also

My Other Related Deepdive Gist's and Projects

• https://github.com/0xdevalias
• https://gist.github.com/0xdevalias
• Algorithmic Trading / Quant Trading / Systematic Trading / Automated Trading / etc (0xdevalias' gist)
• TradingView Pine Script Reference (0xdevalias' gist)
• Linear Programming, Optimisation Problems, etc (0xdevalias' gist)
• Exporting Nexo Pro Data (0xdevalias' gist)
• MtGox – Recovering Your Transaction & Balance History from the 2014 Leak (0xdevalias' gist)

Some other Google Sheets variations on a pattern similar to this, without needing to manually fill it down the column:

=SCAN(0, H2:H, LAMBDA(accountBalance, tradeAmount, 
  IF(tradeAmount="", "", LET(
    tradeType, INDEX(F:F, ROW(tradeAmount)),
    balanceDelta, IF(OR(tradeType="SELL", tradeType="WITHDRAW"), -tradeAmount, tradeAmount),
    rawResult, accountBalance + balanceDelta,
    ROUND(rawResult, 10)
  ))
))

=SCAN(0, I2:I, LAMBDA(accountBalance, btcAmount, 
  IF(btcAmount="", "", LET(
    row, ROW(btcAmount),
    tradeType, INDEX(F:F, row),
    fee, INDEX(J:J, row),
    balanceDelta, IF(tradeType="SELL", btcAmount, -btcAmount),
    rawResult, accountBalance + balanceDelta - fee,
    ROUND(rawResult, 10)
  ))
))