Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
BasicsCryptocurrencyStock investments

Crypto Portfolio Analytics

Written by Vanshika Kothari
Wed May 12 2021
Crypto Portfolio
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Crypto Portfolio
 

With Coinbase (NASDAQ: COIN), the largest Cryptocurrency exchange getting listed, and the crypto rally that we’ve seen, especially bitcoin, the cryptocurrency market is quite a new sensation for many. Nakamoto first introduced cryptocurrencies in 2008, and since 2013 it has seen massive growth.
However, popularity and growth come with many risks as the cryptocurrency market is known for its volatility and speculation. If you’re new to the world of cryptocurrencies, check out our detailed article on Cryptocurrencies here.
But, if you’re a crypto investor or trader and are looking to manage your portfolio, we’ve got it covered for you; keep reading!

Tracking and Analyzing your Crypto Portfolio (Excel Template)

Cryptocurrency investors usually have a portfolio of cryptos varying from 3 to 20 and more because managing the portfolio requires a lot of effort, time, and energy and is even complicated. Having them managed all in an excel sheet along with your other investments makes it a lot less cumbersome, isn’t it? Well, that’s what marketxls is for, to make your investment journey easy and hassle-free.

You can analyze your portfolio and manage it with our template, which provides EOD data for your cryptos, risk-return metrics, charts, and graphs to get a visual summary. 

The template has a sheet that provides a complete list of Cryptocurrency codes you can use to add to your portfolio analysis template to run the analysis.

The Parameters

LHDXjbTw1WKddK37TR0EqS-HB26YOjCzL7dLJTfdGrz” alt=”Crypto Portfolio Analysis”/>

The various portfolio ratios will be calculated as we input the codes. It calculates the Sortino Ratio, the CAGR, Maximum drawdown, Portfolio Beta, Volatility, Efficient Frontier chart, Sharpe ratio, and much more, giving you the power to see your portfolio as large fund managers do!

Let’s break down each parameter used in detail:

  1. Sortino Ratio

The Sortino ratio measures the risk-adjusted return for the portfolio. It differentiates harmful volatility from total overall volatility and uses the portfolio’s downside deviation instead of the total standard deviation of portfolio returns.

  1. Value at Risk 

Value at Risk measures the level of financial risk of the portfolio for a specific time frame. It assesses the potential loss for the portfolio and the probability of the same, for, eg. A 5% one-month VaR of 1% representing a 5% chance of the asset declining in value by 1% during the one-month time frame. 

  1. Portfolio Volatility

Volatility is a measure of risk. The portfolio volatility indicates how widely the total value of the portfolio appreciates or depreciates. Standard deviation and correlation among the stocks in the portfolio are two main elements of the risk in the portfolio.

  1. CAGR

CAGR or the Compound Annual Growth Rate measures the annual growth of the portfolio over a while. It measures how much you have earned every year during a given time frame.

  1. Portfolio Beta

It measures the portfolio’s systematic risk, which is equal to the weighted average of the beta coefficient of all the individual stocks in a portfolio.

  1. Treynor Ratio

Treynor Ratio determines how much excess return is generated for each unit of risk taken on by a portfolio. Excess return is the return earned in addition to the return earned by investment in risk-free security.

  1. Sharpe Ratio

The Sharpe ratio also measures the return per unit of risk taken. However, here the risk-adjusted return measured in Sharpe ratio is not just the systematic risk but the portfolio risk (i.e., σp)

  1. Maximum Drawdown

A maximum drawdown (MDD) is the maximum observed loss from a peak to a portfolio trough before a new peak is attained. Maximum drawdown is an indicator of downside risk over a specified period.

  1. Portfolio Efficient Frontier Chart

The efficient frontier of the portfolio is optimal portfolios that offer the highest expected return for a defined level of risk or the lowest risk for a given level of expected return. It plots the expected return on a portfolio of investments on the y-axis and its risk measured by its standard deviation on the x-axis.

The Crypto Portfolio Analytics Template Explained

Crypto Portfolio Template

The Cryptocurrency codes and their respective weight that you hold can be added to the cells.  The list of crypto currency symbols are in the sheet “All Crypto Symbols.” As you can observe the necessary ratios will be calculated automatically once the cryptocodes and the weights have been added. The Efficient Frontier Graph and the Monthly returns is also automatically generated. The Wealth Index calculates the portfolio value of $10,000 invested in the beginning of the period over time.

Note: MarketXLS only provides EOD data for cryptocurrency at the moment. 

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.

References

Efficient Frontier Definition. https://www.investopedia.com/terms/e/efficientfrontier.asp

Maximum Drawdown (MDD) Definition. https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp 

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

STORE Capital Corporation logo

STORE Capital Corporation

31.58
 
USD
 
-0.14
 
(-0.44%)
77
Rank
Optionable: Yes
Market Cap: 8,978 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
Dillard's Inc. logo

Dillard's Inc.

280.00
 
USD
 
-6.76
 
(-2.36%)
77
Rank
Optionable: Yes
Market Cap: 5,077 M
Industry: Department Stores
52 week range    
167.03   
   416.71
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

125.49
 
USD
 
-3.52
 
(-2.73%)
76
Rank
Optionable: Yes
Market Cap: 8,796 M
Industry: Specialty Retail
52 week range    
101.58   
   223.32
ICF International Inc. logo

ICF International Inc.

108.01
 
USD
 
-0.46
 
(-0.42%)
74
Rank
Optionable: Yes
Market Cap: 2,040 M
Industry: Business Services
52 week range    
84.68   
   111.10
First Interstate BancSystem Inc. logo

First Interstate BancSystem Inc.

41.49
 
USD
 
-1.14
 
(-2.67%)
74
Rank
Optionable: Yes
Market Cap: 4,622 M
Industry: Banks - Regional - US
52 week range    
32.40   
   45.33
Global Cord Blood Corporation logo

Global Cord Blood Corporation

2.99
 
USD
 
0.77
 
(34.58%)
74
Rank
Optionable: Yes
Market Cap: 317 M
Industry: Biotechnology
52 week range    
2.03   
   5.50
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

100.37
 
USD
 
-3.16
 
(-3.05%)
73
Rank
Optionable: Yes
Market Cap: 7,684 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   126.93
General Mills Inc. logo

General Mills Inc.

79.17
 
USD
 
-1.61
 
(-1.99%)
72
Rank
Optionable: Yes
Market Cap: 44,923 M
Industry: Packaged Foods
52 week range    
58.85   
   81.24
Allegiance Bancshares Inc. logo

Allegiance Bancshares Inc.

43.90
 
USD
 
0.18
 
(0.41%)
72
Rank
Optionable: Yes
Market Cap: 883 M
Industry: Banks - Regional - US
52 week range    
35.99   
   46.96
National Research Corporation logo

National Research Corporation

39.24
 
USD
 
0.49
 
(1.26%)
71
Rank
Optionable: No
Market Cap: 948 M
Industry: Diagnostics & Research
52 week range    
30.93   
   52.36

More Features