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

Published in

Spreadstreet

5 min readDec 15, 2017

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:

Google Sheets
Spreadstreet Google Sheets Add-in
StockTwits
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:

The tweets are very recent, preferably within the last hour
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

Click this link to download the Spreadstreet Google Sheets Add-in
Follow the installation instructions included with the add-in and log-in

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

**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.”
A reload of the entire worksheet fixes quite a few problems.
Deleting and re-pasting the formula in A1 of the “Data” tab fixes things as well.
If all else fails, drop a line to john.young@spreadstreet.io. He will get you running in no time.
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.