How to Add Fundamental Analysis to Your Crypto Portfolio with Messari (Part 1)
Adding fundamental analysis to your crypto portfolio is the next step after basic market data for volatility and risk adjusted performance. Using the Messari API with Cointracking & Cryptosheets makes that easier than ever before, here’s how to do it.
OVERVIEW
Portfolio asset allocation and risk management is critically dependent on the attribution of the underlying assets. Attribution can encompass a broad variety of characteristics including: asset or instrument type (crypto, derivatives. stocks, bonds etc), industry/sector/category, governance structure, supply/emission schedule, financing profile and much more.
If somebody said
“…my portfolio has really been outperforming the market recently thanks to my heavy tech stock allocations…”
— that intuitively makes sense to most people. It also provides context that assets with specific ‘attributes’ in the portfolio are outperforming others on a relative basis.
This article will demonstrate how to do the same for your crypto portfolio using fundamental metrics & attributes from Messari.
- Pull in your live crypto portfolio data using CoinTracking.info
- Find relevant metrics from Messari
- Apply those metrics to your portfolio holdings, gains & losses
- Visualize a basic analysis of the fundamental analytics relative to your portfolio
STEP 1: Pull in your live crypto portfolio holdings
We’ll use our demo portfolio from CoinTracking.info because it’s so easy to pull in using the Cryptosheets integration. It also already aggregates all of our exchange accounts, wallets and other crypto holdings and gives us up to date gains, unrealized gains and more.
👉 This is how easy it is to get started (paste this into your spreadsheet)
Excel =CS.QUERY("Cointracking","get balance")
Google Sheets =CSQUERY("Cointracking","get balance")First we use the get gains endpoint to create a table of our portfolio gains…
… then doing the same for the get balance endpoint gives us the below output…
STEP 2: Find relevant metrics
For this article we’re looking for simple classification and taxonomy type fundamentals to help us refine our portfolio performance and attribution by category and sector. The Messari API is one of the absolute best places to find this valuable data and it’s fully integrated with Cryptosheets!
- Navigate to the Cryptosheets Messari Docs page or API console
- Use the API console query builder to make a sample request
- Open the Output inspector
- Search for the term or data field you’re looking for like
SECTOR - When you find it 👉 just click it once, the full path will be copied to your clipboard
- Paste that back into the
_pathsglobal parameter and make the request again - BOOM!
TIP: you can skip all the above steps by simply selecting the prebuilt scenario for “Crypto Asset Sector” from the scenarios dropdown menu
PRO TIP: you can search across all the available Messari API data fields by using the “send fields to sheet” option in the dropdown menu
(spoiler 👉 it’s a gamechanger 😉)
STEP 3: Apply the metrics overlay to your portfolio
We’ll use the previous steps and some super handy premade Cryptosheets scenarios to grab the formula that will extract just the data field we need per position in this case category and sector.
Formula should look something like this
=CS.QUERYA("Messari","Get Profile By Symbol","_path","@.data.profile.general.overview.sector","_showHeaders","0","symbol","ADA")Then we simply copy the formula and paste it into our sheet, adjusting the symbol value of "ADA" to be the cell reference of our position symbol. Then simply drag it down to populate the category and sector data for each symbol in your portfolio.
Next we’ll use some simple =UNIQUE() and =SUMIF() formulas (sorry pivot table bros) to create a sub table of our portfolio realized gains broken out by sector
Finally we’ll add some data bars, conditional formatting and tie the My Balances, My Gains and (newly created) Gains by Category & Sector sections together and #shazam!
SUMMARY
👉 Here’s how we started: =CS.QUERY("Cointracking","get gains")
🚀 Here’s where we finished…
We’ve literally just scratched the surface here applying some high level metadata and fundamental analysis to our portfolio. Given the unparalleled depth of Messari fundamental metrics, the sky is truly the limit.
Follow us on Twitter, YouTube and here on Medium to catch the next edition of using Cointracking.info & Messari for advanced portfolio analysis , risk attribution and asset allocation decisions. In the meantime be sure to sign up for Cryptosheets and start building your own portfolio dashboards and tools.
Also — here’s the template we used for this post, you can download it today and try it yourself! 👉 Download the template here
Follow us on Twitter, YouTube and Medium to see new types of scenarios, the latest trending data and powerful techniques to extract and transform the evolving crypto data landscape!
Check out these related articles & templates….
Access all of your combined exchange holdings, trades, gains, and historical portfolio data using Cointracking.info in Cryptosheets
Benchmark your portfolio performance against any crypto or capital markets asset, index or reference rate across multiple time periods.
- Customize the time periods
- Choose from hundreds of thousands of possible benchmarks to analyze your relative performance
- Advanced version w/ fully integrated Cointracking holdings available for paid subs
Cryptosheets is free to sign up for and start using now….
What are you waiting for? 👉 Get crypto data in 30 seconds or less
Get the Googlesheets add-in now
IMPORTANT:
Existing Messari & Cointracking.info Users Get 20% discounts on any Cryptosheets paid subscription! Just follow these links
👉Get the Messari Discount
👉Get the Cointracking.info Discount
________________________________________________________________
Troubleshooting
- See FAQ and troubleshooting help topic in the help center here
- Explore troubleshooting tips & tricks in the quick docs section here
Tutorials
- Explore tutorials here
ADDITIONAL RESOURCES
Website | WebApp | Excel | Googlesheets | Help Center | Blog | Twitter | YouTube
TAGS: portfolio, messari API, cointracking API, crypto portfolio, portfolio management, portfolio analysis, crypto API, excel, googlesheets
