Cryptosheets
Published in

Cryptosheets

How to Pull 30,000 Cryptocurrency Sharpe Ratios in 5 seconds

Access in depth Sharpe ratio & performance data on crypto assets to leverage one of the most widely used risk metrics in professional finance without doing any math, here’s how.

Load over 30,000 Sharpe ratios for thousands of cryptocurrencies using simple Cryptosheets formulas and templates for Excel & Googlesheets
Crypto Sharpe ratios screener template by Cryptosheets allows you to rapidly build custom screens by filtering customizable ranges and thresholds for over 20 unique Sharpe ratio metrics

If you’re an experienced user and know what you’re doing, you can go straight to the templates and try them now by clicking here

OVERVIEW

Professional risk managers are compensated based on their performance. Because some portfolios invest in government bonds while others invest in exotic illiquid derivatives their performance has to be qualified or ‘adjusted’ relative to the amount of risk they take with their investments. The volatility of an asset is considered the standard measure to quantify ‘risk’ and thus can be broadly applied across different portfolio allocations and asset classes. Enter the Sharpe ratio.

The Sharpe ratio was developed by Nobel laureate William F. Sharpe and is used to help investors understand the return of an investment compared to its risk. The ratio is the average return earned in excess of the risk-free rate (RFR) per unit of volatility or total risk. Volatility is a measure of the price fluctuations of an asset or portfolio.

Crypto Sharpe ratios dashboard template by Cryptosheets allows 100% customizable performance metrics grids (choose from 54) & taxonomy overlays for sector, category (choose from 100+ other data fields)

BACKGROUND

As a portfolio manager I needed easy methods to quickly filter out all of the noise inherent with wildly unstable, unpredictable and often outright false or fake price and volume data. After focusing on Sortino ratios at first

  • I quickly realized that available data was lacking and not easily accessible
  • Because Sortino requires each user to define their own specific downside levels it also quickly became apples to oranges leading to confusing results
  • With Sharpe ratios I knew all the data was using the same underlying benchmark RFRs which simplified things
  • Because Sharpe is more widely used and crypto asset markets evolve rapidly I focused on the widest possible net to capture the broader universe from which point I could include more refined measures like Sortino ratios

The reality of not ever being able to 100% trust any single crypto data feed or provider was a lesson painfully learned and genuine paradigm shift for me. I focused on redundancy and depth of data to balance that dilemma.

Using LIBOR & T-Bills across multiple periods helps filter outliers & identify (potentially tradeable) anomalies

HOW TO USE

These templates are tools designed to supplement existing risk tolerance and decision making frameworks 👉 they should not be solely relied upon to allocate risk.

Follow these steps to use the Cryptosheets Sharpe ratio templates

Access templates directly from the add-ins or from the website anytime

For Excel

  1. Login to your Cryptosheets account (or register for a free account)
  2. Navigate to the templates section in the add-in side panel or from the website
  3. Search for “sharpe”
  4. Click one of the templates
  5. Click Load in new workbook

For Googlesheets

Access templates for Googlesheets directly from the add-ons drop down menus
  1. Login to your Cryptosheets account (or register for a free account)
  2. Navigate to the templates section in Googlesheets from the add-ons drop down menu > Cryptosheets > Templates
  3. See this article for detailed tutorial about using Cryptosheets templates in Googlesheets

EXAMPLES

The underlying data for these templates comes from ParadigmAPI which you can access directly on the Cryptosheets Docs Portal. You can also try them by simply copying + pasting the below example formulas into your spreadsheets

Excel: =CS.QUERY("Paradigm","performance")
Googlesheets: =CSQUERY("Paradigm","performance")

Example 1:
Find the top 100 risk adjusted performers by filtering for the highest Sharpe ratios using sharpe6MLibor

With the Crypto Sharpe Ratios Screener template you can use simple drop down menus to select which performance metrics to sort by then enter custom values to filter the results

Example 2:
Filter & identify outliers caused by erratic or inflated underlying price data

Example 3:
Overlay Sharpe ratios against your crypto portfolio positions for enhanced valuation analysis and decision making

Pull your entire Cointracking.info portfolio in w/ 1x formula → then pull in Sharpe ratios for all of your positions w/ 1x more formula or use the Cryptosheets template. 2x formulas that’s it!

METHODOLOGY

Formula and Calculation of Sharpe Ratio

where:
Rp
​=return of portfolio
Rf​=risk-free rate
σp​=standard deviation of the portfolio’s excess return​

Data

  • Crypto pricing data & Sharpe ratio calculations are provided by ParadigmAPI
  • Historical & current benchmark rates are provided by the St Louis Fed (FRED)
  • Sharpe ratio calculations are performed nightly in batches but can also be performed in real time using Cryptosheets custom functions

Considerations

  • Sharpe ratios rely on a benchmark risk free rate (RFR) as a baseline for volatility and risk profile. When applied to crypto assets this is admittedly & inherently flawed for several reasons
  • The RFR’s used include multiple term US Treasury T-Bill & LIBOR rates for redundancy & visibility into how recent historic short term volatility can substantially skew metrics like sharpe ratios
  • LIBOR is also being phased out internationally following the historical Brexit legislation. Fun stuff right?
  • US treasuries, T-bills & LIBOR are all actively traded or quoted ~5–6 days a week creating a weekly gap in data comparisons vs 24/7 crypto assets

OTHER RATIOS

For those interested in other ratios, I would strongly encourage reading up on the basics (standard deviation, variance, beta, max drawdowns, VaR) followed by more advanced metrics (Sortino, Treynor, CAPM, CCML). Help the crypto community by educating yourself as much as possible.

The Sortino ratio is a modification of the Sharpe ratio but uses downside deviation rather than standard deviation as the measure of risk — i.e. only those returns falling below a user-specified target, or required rate of return are considered risky.

The Sortino ratio is a slightly more involved and potentially powerful variation of the Sharpe ratio because you can define the downside measure to better fit the specific analysis. This can be a double edged sword if you don’t know what you’re doing and in crypto you may inadvertently filter out valid results.

Pop quiz: when Coinbase crashed in 2017 and ETH traded from $150 to $0.73 in seconds then bounced back 👉 did (your) Sortino ratio account for that?

The Sortino ratio focuses strictly on downside volatility. The theory is that an investor should welcome wild swings to the upside, so upside returns are omitted. As a portfolio manager ‘wild swings to the upside’ may unintentionally (or even auto-mechanically) force portfolio re-balancing that involves forced sales or liquidations of otherwise cornerstone portfolio risk allocations.

I would generally consider Sharpe ratios the standard pre-requisite for users to learn and understand before moving on to Sortino.

👉 For an in depth tutorial and template for calculating Sortino ratios, you can checkout our CTO John Young’s piece “Crypto War Zone Top 100 Cryptocurrencies Ranked by Risk-Adjusted Return” here

SUMMARY

  • We leveraged several powerful (free) tools to create some basic insights from crypto datasets about risk adjusted performance
  • We only scratched the surface
  • If you’d like to explore this dataset and draw your own conclusions, feel free to load your own copy, save it and customize it

I get questions about crypto investing, trading, analytics and valuation all the time. I usually tell people to “start with Sharpe and take it from there”. One of the only universal truths I have found in my experience is that crypto investing will break every assumption, textbook, mathematical and logical framework you can throw at it.

I would bet good money that by the time my kids read this article there will be at least a handful of new viable contenders for most widely used universal risk adjusted performance metric. The intellectual capital, talent & sheer computing power in the crypto industry is simply too great to remain satisfied with metrics designed for assets that only work 5 days a week.

RESOURCES

▶️ Cryptosheets is free to sign up and start using now.
What are you waiting for? ➡ Get crypto data in 30 seconds or less

Get the Excel add-in now

Get the Googlesheet add-on now

ADDITIONAL RESOURCES

Website | WebApp | Excel | Googlesheets | Help Center | Blog | Twitter

TAGS : sharpe, sortino, treynor, roi, returns, performance, best cryptocurrency to invest 2020, best cryptocurrency, best cryptocurrency performer, metrics, standard deviation, beta, drawdown, risk, volatility, variance, api, excel, googlesheets, spreadsheet, crypto data, crypto api, bitcoin, blockchain, metrics, data, bitcoin price, template, tools, models, portfolio management, miracles, paradigmapi

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store