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

spreadstreet (25)in #cryptocurrency • 7 years ago

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](https://www.google.com/trends/explore#q=sentiment
analysis), 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:

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 [email protected]. 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 [email protected].