Dogecoin: To the moon with LUSID by Finbourne
Tracking Cryptocurrency trading using the LUSID investment data platform
I recently started working at a London FinTech company called Finbourne who make a cloud-based data platform for investment data called LUSID.
It’s mostly used by hedge funds, pension companies, and investment banks to store their trading data, but you can use it to store your own personal investment data as well.
My colleague Mike McGarry has done exactly this, importing data from his personal financial accounts and investments, and then using LUSID to compute his net worth over time.
Reading Mike’s blog posts got me wondering if we could use LUSID to build a portfolio of holdings in different Cryptocurrencies and track its value over time.
And that’s where this blog post comes in!
LUSID Data Model
We’re going to be using some financial terms in the rest of this blog post and it probably makes sense to explain what we mean by them.
- Instrument — a financial instrument that we own or have exposure to
- Portfolio — records the history of transactions
- Transaction — an economic change to a portfolio. Can be one-sided (e.g. cash inflow) or two-sided (e.g. an equity trade purchasing stock and settling cash).
- Holding — quantity of an instrument or balance of cash within a portfolio.
- Quote — market prices for an instrument on a particular date.
- Valuation — a way of computing the value of the holdings in a portfolio on a particular date based on quotes for that date.
The diagram below shows how these terms relate to each other:
(drawn with arrows.app, which is still my favourite diagramming tool)
Let’s get to it!
If you want to play along, you can spin up a free instance of LUSID by going to lusid.com/app/signup.
The code used to create the charts and tables shown in this blog post is available as a Jupyter Notebook
Instruments Master
The first thing that we need to do is create an instruments master.
An instrument in LUSID refers to any financial asset that we own or have exposure to. Common examples of instruments would be currency or shares.
In this blog post, we’ll have instruments for USD cash and cryptocurrencies.
LUSID comes pre-loaded with instruments for currencies so we won’t worry about that. Instead, we can focus on cryptocurrencies.
I found a JSON file at crypti/cryptocurrencies that contains 3,758 (!) cryptocurrencies.
I’m not sure how easy it is to trade some of the cryptocurrencies on this list, but let’s import them all into LUSID anyway. Once we’ve done that, we’ll be able to see the instruments in the LUSID web app:
The identifier on the left is called a LUSID Unique Instrument Identifier and is a unique identifier assigned to each instrument.
Portfolio and Transactions
We’ll need to create a portfolio to keep track of our trading activity (transactions). Any transactions that we import will be attached to a portfolio.
It’s time to do some trading! We’ll go back to 1st January 2021 and give ourselves $1,000 cash, which we will invest more or less equally between Dogecoin and Stellar.
The prices in the CSV file are the closing ones for each of those cryptocurrencies on 2nd January 2021. We’re using the in-built transaction types FundsIn and Buy . LUSID will take care of subtracting money from our cash balance every time that we buy units of an instrument.
Valuing our portfolio
We’d like to be able to keep track of the value of our portfolio, which we can do by loading quotes/market data for each of the instruments.
If we were doing this for real we’d want to load the market data from an API, but here we’ll download CSV files containing the end of day prices for the last year for the 4 most popular currencies from CoinDesk and concatenate them into one CSV file:
Now that we’ve got the prices/quotes loaded in, we can compute the valuation of our portfolio.
Things were looking really good at the start of February when our total valuation was almost $9,000. It’s fallen down a bit in the weeks after that and is now sitting at just over $6,000.
Let’s break that down by instrument so we can see what’s causing these fluctuations:
Our cash amount is stuck down just above $0 and the value of our Stellar holdings haven’t varied much. It’s Dogecoin that’s responsible for the changing value!
Now let’s say at this point we decided that we’d had a good run with Dogecoin and will sell some units and buy Bitcoin with the receipts. We’ll do the trades described in the CSV file below on 3rd March 2021, using the end of day prices from 2nd March 2021:
After we import those transactions, we can run a valuation from January 2021 until May 2021. This time we’ll include the total valuation of our portfolio as well as the value of holdings in individual instruments:
We can see a dip in the value of Dogecoin at the beginning of March, which corresponds to us selling half of our position. The line for XRP also appears at that date, since that’s when we bought some XRP.
XRP has almost doubled in value in the 2 months since we bought it, but Dogecoin has absolutely skyrocketed. Our Dogecoin is now worth $25,000 and is a massive proportion of the value of our portfolio.
What if we HODL?
One question does remain though: should we have kept all of our Dogecoin back in March, rather than selling half of it and buying XRP.
We probably know what the answer is, but we can run a what-if analysis using LUSID to confirm our suspicions. This involves the following steps:
- Create a derived portfolio of our initial portfolio
- Remove the 3rd March 2021 transactions from the derived portfolio
- Run the valuation on the derived portfolio
A derived portfolio is basically a copy/paste of an existing portfolio and any changes made in the derived portfolio aren’t reflected in the parent portfolio.
Changes made to the parent portfolio are automatically reflected in the derived portfolio, but the derived portfolio can also contain its own additional transactions, which take precedence over the parent portfolio’s transactions.
Once we’ve created a derived portfolio, we can re-run the valuation for both the parent and derived portfolios. The chart below shows how the value of our portfolio changed over time:
And actually, it’s not as clear cut as I thought. Up until mid-April 2021, our trades were looking good, but then Dogecoin took off and if we’d kept hold of our whole position our portfolio would now be worth more than $50,000.
Mind you, getting to $35,000 is still pretty good considering we started with $1,000 at the start of the year!
In Summary
In this blog post we’ve seen that we can indeed track Cryptocurrency trading using LUSID.
The code used to create the charts and tables shown in this blog post is available as a Jupyter Notebook, so don’t forget to have a look at that if you want to reproduce this type of analysis yourself.
