Financial Modeling for Cryptocurrencies: The spreadsheet that got me my first 1,000% gain

John Young
Dec 15, 2017 · 5 min read

TL;DR: I created a spreadsheet that automates pulling trending cryptocurrencies, recent tweets, and bullish/bearish sentiment into Google Sheets. You can find it here:

https://docs.google.com/spreadsheets/d/1QtxaDWCoXkIqjPVVkU8DNsx8GBaBd180COwn1CIasa8/copy

I know some crypto users prefer to work in spreadsheets (including myself) so I created a spreadsheet that looks at trending symbols and estimates bullish/bearish sentiment. I never had the time to develop software engineering skills, but the recent launch of a few add-ins has allowed regular joes like myself to harness the power of APIs.

The Beauty of Sentiment Trading

According to Google Trends, the word “sentiment analysis” has been gaining steady traction over the past 5 years.

Sentiment refers to the attitude expressed by an individual regarding a certain topic. This is especially relevant in trading, where so much of the change in price is dictated by emotions:

Image result for sentiment trading
Source needed for picture…who made this?

When it is applied to trading, sentiment can be used (with great potential windfall) as a directional signal to figure out whether you should enter a crypto position within your portfolio.

A way you can utilize sentiment when trading crypto, is to measure the positivity or negativity of a tweet. If recent tweets have been overwhelmingly bullish (aka, the person expects the crypto rise) and movement is beginning to happen in the currency, chances are good that the trend will continue.

Luckily for you, StockTwits already does this with around 225 different cryptocurrencies. And if you can combine the sentiment with trending cryptocurrencies, you can catch the wave right as it forms.

Services Used:

  1. Google Sheets
  2. Spreadstreet Google Sheets Add-in
  3. StockTwits
  4. CoinMarketCap API

What it pulls:

  • Trending Symbols (30 at a time)
  • Historical Price Data
  • Recent Tweets
  • Bullish/Bearish Sentiment of recent tweets

AN OVERVIEW OF THE SECTIONS

The Dashboard

Changing the timeframe

Changing the cryptocurrency

Instructions Tab

Summary Tab

How to use the spreadsheet to spot trending cryptocurrencies

You will spend most of your time in the “Analysis” tab, where the trending cryptocurrencies reside.

I am almost always looking at the “By Hour” timeframe, as the “By Day” and “By Week” is usually a missed opportunity…however, those time frames can still be very useful (albeit, for different reasons).

You want to ride the wave on a cryptocurrency under one or both of the following conditions:

  1. The tweets are very recent, preferably within the last hour
  2. Overwhelmingly Bullish sentiment

A Great Example:

A Bad Example:

Getting the spreadsheet to work for you

1. Install the Spreadstreet add-in for Google Sheets

2. Get sheet ready for use with the add-in

  • **Important** Open the template, click the menu Add-ons / Spreadstreet / Help / View in store, and then click Manage and in the dropdown menu click Use in this document.
  • Numbers in row 26 and below should populate. If they do not, refresh the sheet

3. After logging into the add-in, refresh the sheet

  • In the analysis tab, change the “TIMEFRAME” dropdown to one of the other choices…this refreshes the pull
  • Note: CoinMarketCap API has limits. Be careful when attempting to refresh the sheet too many times

Troubleshooting

  1. **Important** Open the template, click the menu Add-ons / Spreadstreet / Help / View in store, and then click Manage and in the dropdown menu click “Use in this document.”
  2. A reload of the entire worksheet fixes quite a few problems.
  3. Deleting and re-pasting the formula in A1 of the “Data” tab fixes things as well.
  4. If all else fails, drop a line to john.young@spreadstreet.io. He will get you running in no time.
  5. The “TIMEFRAME” cell in the Analysis tab (B3) refreshes the pull. Change the results for new data.

Some trending coins are not pulling correctly…what’s going on?

StockTwits has a list of ~225 coins it currently supports. See the “TweetInfo” tab for the full list.

I have tried logging in, activating the template with “Use in this document” and refreshed the sheet…still nothing.

Head to the “Data” tab. Delete the formula in cell A1, and repaste the following: =SS(“tickers-coinmarketcap”, headers, “0”, Currency)

Hoping this is something that is useful to everyone, and I am more than happy to help peeps setup the sheet so they can use it. Just send me a message on here or to john.young@spreadstreet.io.

RESOURCES

Download the add-in: https://spreadstreet.io/tools/google-sheets-add-in

Help: https://spreadstreet.io/docs

First time install and login: https://www.youtube.com/watch?v=aLjtPR4T2bg

Bitfinex Candles endpoint help: https://spreadstreet.io/knowledge-base/bitfinex-api-candles-endpoint/

RELATED POSTS

Top 12 Cryptocurrencies Ranked by Risk-Adjusted Return

Is Genesis Mining Worth it? A Genesis Mining Profitability Calculator You’ll Actually Use

How to Create an Ethereum Mining Calculator from Start to Finish

10 Statistical Price Predictions for 10 Cryptocurrencies

Bitcoin Madness: How to Simulate Bitcoin Prices in Google Sheets

Bio for Spreadstreet
Bio for Spreadstreet

Spreadstreet

Spreadstreet makes it simple to connect cryptocurrency services to spreadsheets.

John Young

Written by

Founder, Cryptosheets.com and Spreadstreet.io. Husband to the most beautiful woman in the world. Runner-up in 6th grade spelling bee. Super-nerd.

Spreadstreet

Spreadstreet makes it simple to connect cryptocurrency services to spreadsheets.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade