Metrics DAO Portfolio Analysis

For this assignment I examine

• The current token holdings and their balances
• The change in portfolio construction and value over time through MDAO's history
• The flow in and out of the treasury of the USDC token

The method to my madness

• I looked at the end of month balances.
• I excluded sh*t coins and any token balances under $100.
• I used the treasury contract address provided by MetricsDAO in their docs

Three Flipside tables were used

1. ethereum.core.dim_dates — used for slick date arithmetic
2. ethereum.core.ez_current_balances — shows the balances of every token every address owns, refreshed once daily.
3. ethereum.core.ez_balance_deltas — has an entry for every datetime in which a tokens balance changed (transfer in or out).

By "cross joining" (cartesian product of two tables) the dates from the 'dates' table with the list of symbols which at one point held a value over 100 dollars, so as to be able to have a date entry for every asset in the portfolio, even on days when there were no changes in the number of tokens held.

This is done by "left joining" — we take the table we created above and combine it with the balance deltas table. Every row in the left table is kept, with columns added from the table we are joining.

This has the effect of making sure there is one row entry for each symbol, even if there was no change in balance on that date. There would just be blank spaces where there was no value. The last step of the process is to use the window functions, lag and lead to fill in the blank spaces. This was unnecessary, when using the monthly window for MetricsDAO, but for daily data, which we employ in our 'inflow/ outflow` analysis.

TMI, I know, but this serves as my notes for the class, and as it happens it was immensely useful. I had not figured out how to get a daily balance over time, since they depricated a table they used to use for that. The cross join and the window function acrobatics, was also very instructive.