Cryptocurrency Tracking via Google Sheets
Using Google Sheets to Track Cryptocurrency Prices
One of the biggest obstacles that people face when trying to get into cryptocurrency trading is having an efficient way to track the value of the coins that they hold. While there are several premium software options available to track cryptocurrency prices, most come with a premium price tag attached to them. To make matters worse, the software solutions that are available rarely include cryptocurrencies themselves. This forces most users, who need cryptocurrencies added, to incur even further charges paying to have custom plug-ins created for the software that they purchased.
Being that the majority of the people who are just getting into trading do not have a large budget to begin with, most come up with personalized solutions utilizing applications and software that they already own. In most cases this includes Microsoft Office, Google documents, and a variety of other applications from various vendors and software developers. While this undoubtedly works for many, it takes an extremely large amount of time to get the personalized solution created, developed, set up and ready to run. As changes are needed, updates are made, more time is taken. A solution that is both efficient and provides the information that the user needs has left the hole in the industry that no one has attempted to fill. Until now.
As was mentioned above, many people utilize Google documents, namely Google sheets in order to create a spreadsheet that gives them the flexibility that spreadsheets offer these type of situations, along with Google’s excellent API environment. While the solution is not a built-in part of Google sheets, the third-party add-on, is both free and easy to use. Using Google sheets, and the CryptoFinance add-on, you can track current Cryptocurrency prices, in real time all from one page. Not only that, the page can be set up to be dynamic, refreshing at the interval of your choosing, and can feature only those coins that you wish to know the price of. The CryptoFinance add-on makes it very simple and easy to get all of your Cryptocurrency prices quickly and efficiently, finally filling the gap for a much-needed solution to the problem.
CryptoFinance Add-On for Google Sheets
The CryptoFinance add-on for Google sheets specifically designed to get the current Cryptocurrency prices from the Coinmarketcap API. While it does have the ability to tie into other exchanges as well, no other exchange offers the sheer quantity of cryptocurrencies available. With over 1200 Cryptocurrency prices and other real-time data, all available in real time, Coinmarketcap is by far most all-inclusive source of information on the subject.
The ability to get Cryptocurrency prices in real time, through the CryptoFinance add-on in Google sheets, was created by a man named Julian Buty. Julian, or JButy, which is a screen name developed CryptoFinance for Google sheets because he faced the same problem that many others at face before him. However, unlike most software developers, he decided to give this away for free. There is no charge at all to add this to your Google sheets, and there are no upsell attempts, in app purchases, or other monetization efforts made while using the CryptoFinance add-on. It is simply free. Plain and simple. You can now get your Cryptocurrency prices, as well as other data, all for free utilizing the CryptoFinance add-on in Google sheets.
Using Google Sheets for Cryptocurrency Prices
in order to utilize the CryptoFinance add-on in Google sheets, you obviously have to have a Google account. If you do not have a Google account as of yet, simply had to Gmail.com and sign up for free email box. With Google, one login will log you into all of their services. Once you have gotten the Gmail email address, simply had to the right, upper section of your inbox, next to the initials of your name that appear in the colored circle. To the left of these initials, you will see 2 icons, one a circle and the other a square shape. If you click on the squared shape made up of several smaller squares, it will open a drop-down that will allow you to navigate 2 different Google services that are available.
If it is not listed within the drop-down, there will be a more link, at the bottom clicking this will bring you to another section of the same drop-down and you are looking to find “Docs”. Once you click on the dots icon, a new window will open up to Google Documents in the upper left-hand side you will see the ever present 3 lines that tell you there is a menu available clicking this menu will open up the left sidebar and you will see the 2nd option sheets, clicking on sheets will open Google sheets.
A Blank Slate That Will Become a Cryptocurrency Prices Tracker
Once Google sheets opens up, simply click on the 1st option which is an icon of a page with a + sign. This will allow you to create a new Google sheet. With Google sheets, your document is saved every time anything is changed on the document. If you need to share the document with other people, you may do so via link, and any changes made by anyone that you authorized to make changes to the document, are all saved in real time. This allows you to work with multiple people, on the same document, at the same time no fear of losing any data due to miscommunication or logistics, which would be a nightmare in any other situation.
The 1st thing you may notice about Google sheets, especially if you’re familiar with spreadsheet software, is that it bears a striking resemblance to Microsoft Excel. That is because it is almost identical twin to Microsoft Excel. Whether or not it is officially admitted I Google or not is unknown, but it is apparent that they drew a lot of the same features directly from Microsoft while creating Google sheets. Now that you have your sheet opened simply click on the name in the upper left-hand corner of the sheet in order to change it. You’re now ready to create your very own, custom built Cryptocurrency prices tracker. Don’t worry though, this is very simple to do, easy to implement and should be done rather quickly. The best part is, every time you want to see the prices of your cryptocurrencies all you need to do is open your Google sheet, and if it is already up and simply refresh the page.
The CryptoFinance Add-On
Now that you have your sheet pulled up and renamed, it is time to find the CryptoFinance add-on and make it a part of your Google sheet. To do so, simply follow these easy to follow step-by-step instructions:
- At the top of the sheet, in the toolbar, find the “Add-ons” menu, click it and then from the dropdown menu, click on “Get add-ons”.
- The Google Chrome Add-ons panel will open, at the top-right, search for “cryptofinance”, click on “+ FREE” to install it.
(Google will ask you which account you would like to have the add-on enabled for, so choose under which account you want to use to install the Add-on. CRYPTOFINANCE needs to connect to an external API, click on “Allow”.
- Making sure that the CryptoFinance add-on is activated in your sheet:
- Go to Add-on > CRYPTOFINANCE > Help
- Click on View in store, then click on “Manage”
- Check the box that says,“Use in this document:”
Time to Track Cryptocurrency Prices
More information on the different options are available and the capabilities of the CryptoFinance add-on, which are not covered within this short guide, can be found through the developer’s personal web space. The links will be posted below the article. For the purpose of this guide, we will utilize default and go with the CoinMarketCap API to create a small spreadsheet which will give us the current price, 24-hour high, 24-hour low, and volume of 50 different cryptocurrencies.
Setting Up the Cryptocurrency Prices Table
The total number of Cryptocurrency you can track it one time is unlimited, although depending on your current network capabilities and Internet speeds, if you try to load all 1200+ cryptocurrencies that can be found at CoinMarketCap, it would take forever for your sheet to fully load. Therefore, it is recommended that you only track the prices and other data of the Cryptocurrency you are most interested in and need information on, in real time. To begin, you should have an idea of the cryptocurrencies which you are interested and displaying within Google sheets already to go. To begin, enter the name of the cryptocurrencies, one per line in Google sheets starting in cell 2A. Now would sell one a give your column, such as “Coin Name”. Now decide the information that you would like to have. Obviously, if you’re creating this sheet you are interested in getting the Cryptocurrency prices. So for column 2B, your heading should be “Price”. And go down the rows adding titles for each column, in any order you would like, to display the information you would like to see.
For the purpose of this guide, are row headings are as follows:
Coin, Price, 24-hour high, 24-hour low, and Volume.
Depending on the number of different Cryptocurrency prices you wish to track, it may be easier if you were to use the Concatenate formula, along with the drag and fill features of the spreadsheet. If you’re using a large number of coins, it can be very time-consuming to type all of those formulas for each coin out by hand. Get together the list of coins you’re going to be using, both the full name of the coin, along with the market symbol which is usually 3 to 4 letters in all uppercase.
Cryptocurrency Prices Tracking Formulas Shortcut
Simply copy the list of coins that you’re going to be tracking from the cells within the spreadsheet. To do so simply click and hold from the 1st coin and drag straight down to the last. Now, right click with your mouse and choose to copy now scroll down the spreadsheet just pass the bottom of your list, giving yourself one or 2 lines just see don’t get confused. Paste in your coins. Now, paste in the market symbols for each coin next to the coin name but leave 2 to 3 cells in between the coin name and the market symbol, empty. In the following cell, next to the market symbol for the 1st coin in your list, simply enter a / and then click and drag it to the bottom of the list.
Now, depending on currency that you wish to get the value for your coins in, such as USD, EUR, GBP, etc. in the line next to the 1st coin you have on your list type in the 3-digit identifier for the currency. For this guide, we will be using US dollars, which is USD. So, in the 1st line, next to Bitcoin in our example, we enter USD. Now, hovering the mouse over the right lower corner of the cell that contains USD, you will see a small cross appear. When you see the cross appear click and hold your mouse and drag straight down to the very last cell that pairs up with the coin. Upon releasing the mouse button, USD will magically appear in all of those cells. Simple, right? Thought so.
A Little Trick in Formula Syntax
In order to let the spreadsheet know that it needs to perform some type of function or formula, the cell must begin with an = sign. However, we can I quickly paste in = signs to a cell without receiving an error from the spreadsheet because when you leave this out is going to try to perform the formula. In order to get around this redundancy issue, there is a simple method that you can use so in the empty cell, directly before the market symbol of the 1st coin, you will want to type:
Should have the market symbol of the coin, and the following cells should have the Fiat currency, in this case USD. In the cell immediately after USD simply type in the following:
You have just written the formula to retrieve the price for the 1st coin listed, which is Bitcoin in this guide. By clicking and dragging everything down you should have all of the formulas written rather quickly. The only issue is, that formulas cannot be in separate cells. They need to all be within the same cell, and all the to begin with the = sign. To accomplish this feat we’re going to use a built-in formula called CONCATENATE, which simply means to join together.
So, assuming that you entered the first coin into row 53, and C53 holds the first “1x1x=…” value, in the 1st empty cell after the “), enter the following:
=CONCATENATE (“C53, D53, E53, F53, G53”)
Now, hit enter. Like magic, you should now see your completed formula all together in one cell, except that it has the addition of the 1x1x at the beginning. What you want to do now, is click on the 1st cell containing the newly joined formula and drag down to highlight to the last. Right-click, choose copy, go up into the top of your spreadsheet and paste the list in so that it matches up with the coin under the price heading.
(Note: When you paste in the information, right-click and choose the option for values, which will be picture of a clipboard with the numbers on it. This is a very important step, you do not paste the values in and simply just paste, you will get an error and have all kinds and spreadsheet malfunctions that are not fun to try and fix. In fact, you would just have to start all over again.)
Now depending on your methodology, you can use search and replace to remove all of the 1X1X’s at the same time, or you can simply double-click in each cell and delete them manually. Once they are deleted, Google sheets will immediately begin executing the formulas and you will see the current real-time price of the coin in question.
Wash, Rinse, Repeat and Then Do It Again
This same method can be applied to any formula that you might use to retrieve other data, whether it be for this Cryptocurrency prices tracking situation or another scenario altogether. The idea behind it, is to end up typing as least as possible, and to utilize the drag and fill down feature of the spreadsheets, as well as the formulas to combine cells and replace text. It is fairly simple, and will also work in Excel as well as in open office spreadsheets.
Sometimes There’s Just Too Much Info
Depending on why you need the current Cryptocurrency prices, you may want to add other data, specific information and include things to fit your needs. The different information sets available include the price, the volume, the 24-hour high, the 24-hour low, the volume, the current market capitalization, total supply, available supply, maximum supply, change percentage, currency rank, currency full name, global statistics, historical price data, specific times, opening, high, low, close, and then you can get it from a different exchange if you choose.
The different exchanges available are Kraken, Bittrex, Liqui.io, Binance, Luno, Gemini, Bithumb, Huobi, HitBTC, Bitstamp, GDAX, BTCMarkets, Independent Reserve, Bitfinex, Cryptopia, and you can even get social media data from Twitter and Reddit. For the detailed information on how each piece of data needs to be formed and written, the developer has an in-depth, wonderful page that lays out all the information that you could ever need. Simply visit his page on the CryptoFinance website.
Google Sheets Cryptocurrency Prices Tracker Conclusion
CryptoFinance is a free and easy to use Cryptocurrency tracker for Google sheets that will give anyone who needs the ability to track Cryptocurrency prices a tool that they can use. While this simple tool does nothing more than retrieve data and display that data and customized layout of your liking, and it does not provide you with any analytical information, or give you automated alerts, it will help you to better organize with the information that you need, in order to better prepare yourself for times when clicked trades are needed. Having the data at hand in a nice organized fashion is a good starting point it should be expected that there will be many other tools that can be implemented, just like CryptoFinance, in the near future. With the price of Bitcoin surpassing $11,000 this past day, the world has no choice but to pay attention, and by doing so a need for these tools will be created. Thankfully, you’re one of the lucky ones will be able to get one of these tools for free in CryptoFinance. Don’t hesitate, don’t wait and don’t put it off because you just might find it if you do, it might not be free when you finally get around to creating your Cryptocurrency prices tracking document in Google sheets.
A Quick Cheat
For those of you that would like to cheat a little bit, I have included a copy and paste solution for you. This will return all the data in USD and will deliver the price, 24 hours high, 24-hour low, volume and market capitalization for the top 10 cryptocurrencies on the market. Simply go to my Google sheet at the following URL and either download a copy, or make a copy. For anyone who would like to pay for a custom solution, and would not like to do it themselves, you can contact me by email at [email protected]
Google Sheets Top 10 Cryptocurrencies: https://docs.google.com/spreadsheets/d/1LzTMbMNVu43eP8JY6gYjLyhpDLosBgs08CD_aGJI_ss/edit?usp=sharing
Important Links for CryptoFinance Cryptocurrency Prices Tracker
How to get historical rates: https://goo.gl/d3162Z#8696
How to get current rates from a specific exchange: https://goo.gl/d3162Z#231c
How to get Market cap, change, volume and more: https://goo.gl/d3162Z#3d03
How to get global data about crypto-currencies: https://goo.gl/d3162Z#7398
How to get social media count for coin mention: https://goo.gl/d3162Z#3349
Developer’s Twitter Page: https://twitter.com/jbuty
CryptoFinance Documentation & Instructions:
- Schnorr Protocol Signature Algorithm for Bitcoin Demystified - Nov 29, 2018
- ChangeNow Exchange Accused of $70,000 Theft - Oct 4, 2018
- Honeyminer Lets You Mine Bitcoin with Your Laptop or PC - Aug 17, 2018
- Bitcoin CPFP Experience – Bitcoin Child Pays for Parent - Jul 20, 2018
- Gagapay Offers Small Businesses DIY Blockchain Smart Marketing - Jul 16, 2018
Know more than others on any Blockchain Party!
Join more than 5000 others to receive the breaking news and weekly summaries! No ICO spam, we promise.