Live Crypto Tracking: Build a FREE Google Sheets Cryptocurrency Portfolio Dashboard

1. Welcome to Class: Welcome to this course in building a real time cryptocurrency tracker in Google Sheets. If you've ever wanted to monitor your crypto holdings, track your profits and losses, and have everything automatically update all in one place, this is the course for you. As a crypto investor myself, this is the same spreadsheet I use to track all of my digital investments. By the end of this course, you'll have a fully functional crypto portfolio tracker that automatically pulls live price data from coin market cap, calculates your profit and losses, and helps you track your investment goals all without needing any advanced coding skills. In this course, I will walk you through a simple process to set up your cryptotracker step by step quickly. This course is designed to be straightforward and hands on, so we'll get you up and running as fast as possible so you can start tracking today. First, we'll set up a Google Sheets template so you have a working starting point. Next, we'll get your own API key from Coin Market Cap and connect it to the sheet. Then we'll add the API script inside Google App script in automate updates. Finally, we'll go over how to interpret your tracker, customize it, and improve it over time. This course is perfect for crypto investors of all experience levels, whether you're a beginner who just started investing or for someone looking for an efficient way to manage their holdings. If you can copy and paste, you can do this. To get started, all you need is a free Google account. If you already have one, fantastic. If not, go grab one now, and let's get started. 2. Setting Up the Google Sheets Template: In this lesson, we're going to set up your crypto portfolio tracker in Google Sheets. To make things easy, I've created a Google Sheets template for you so you can start with a fully structured worksheet instead of building everything from scratch. So the first thing you want to do is if you open up your document crypto tracker reference sheet, at the top, you should see a link. It says, click the link to access the crypto tracker template. Click that link, and you should be brought right here. Don't worry, this is not a functioning sheet yet. We need to make a few adjustments, and then pretty soon it will be working perfectly for you. But the first thing you want to do is make your own copy. Let me show you how to do that. If you just go up here to file, go down to make a copy, and you'll be able to save this as your own copy so you can do with it as you please. So put in whatever name you want. I just suggest just take out copy of and put in crypto tracker spreadsheet and take out the word template, let's say. So, for example, take these out. And you'll be able to save it anywhere on your Google Drive. Click, make a copy, and then we can start from there. So let's assume that you've done that. Great. Now you have your own version of this tracker, and this means you can edit and customize it easily. Now let's go over a few sections that you'll be able to make changes now before we add in some of the programming. So just a real quick tour. We have our title. We have the top three coins from coin Market Cap, Bitcoin, Etherium, and XRP. These will automatically update later. Right here, we have a last updated, which I will show you when it was updated last. So you'll have an idea of how often it updates. Right here, we're going to have a chart. Down below are all of our coins. And real quick, we'll have our coin name, symbol, date of purchase, exchange where you purchased it from or where you're holding it. Your initial investment, the amount of the coin that you're holding your initial purchase price, the current price, which will automatically update the total value, your profit and loss, and your profit and loss percentage, which all of these will automatically update later. Up here in the middle, I have something special for you, a quote of the day. Each day you will get an automatic quote that serves a little bit as some inspiration for your investments. And over here at the top, we have your total initial investment. And then really to display, clearly, this is one of the things I think is most important that I like to look at when I come to this spreadsheet is the current total value. And over here a crypto goal. Now, there are two things that I think you should change before we continue. Number one, your crypto goal. What is your goal? Take a moment and think about what goal that you would like to write here in this box. My example here is to surpass over $10,000 worth of crypto because our example here is going to be an initial investment of about $5,100. So essentially, double your money. So when you come up with an idea, just click here, double click, or you can go up to right here and type in whatever you want, and you'll have your crypto goal. Secondly, you might have noticed down here that all of the currency is in USD, US dollars. I'm an American. So I follow dollars. But I suggest if you're not investing in dollars, if you use a different currency, you can easily change everything now. So, for example, in this area here where we have numbers or says name, actually, these are going to be different numbers, currency numbers. So there are a couple of different areas, and you'll see these easily later. But let me show you where they are now, and they'll be down in your initial investment in all of these sections that I'm going to highlight right here. These will all be currency numbers. So all you need to do is in each of those sections and also up here your initial investment and your real investment here. I'll become obvious a little bit later. All you need to do is highlight that area like I'm doing now, go to format, go to number. And then scroll down to custom currency, and you'll be able to find whatever currency that fits you. Click on that, click Apply, and then everything will change. Let me show you an example. I have already another currency I was using format. Number, and I had South Korean one. So if I click on that, notice how everything that's highlighted will change. There we go. These numbers are in now and Korean. Now I'm going to go back simply by on my Mac, hitting Command Z, and I'm back to Dollars. So before we continue, I suggest making those two changes. Number one, give yourself a crypto goal. It's inspirational. And to reformat any currency if you're not going to use US dollars. Great. At this point, you should have your tracker set up and ready for the next step. The next thing we need to do is connect it to realtime crypto data using coinmarketcap.com. In the next video, I'll show you how to connect your coin market cap API key, which is the key to pulling in live prices in real time. So click on the next video and we'll get that started. 3. Getting a CoinMarketCap API Key: Now that you have your Google Sheets tracker set up, it's time to connect it to real time data. To do that, we need to get an API key from coinmarketcap.com, and it's a free and easy process. So the first thing you do is go to your crypto tracker reference sheet, once again, and you'll be able to see getting your coin market cap API key. Here, step number one, we'll see a website address for coin market cap. Simply click on that and you should come to this page. The best thing to do is click on the Get Started for free button and sign up. I'll use a new email address to show you the process. Make sure you choose the basic free plan. You might not want the newsletter, but you'll need to agree to the personal data and click I'm not a robot. Go through this process. And create your account. You might need to go to your email address and get a verification code, so go ahead and do that. Great. Once you've set in your verification code, you should see a screen like this. And right here is going to be your API code. Simply click Copy Key and then paste it somewhere safe so you can always access it easily. Of course, if you lose it, you can always come back to this site and copy it again. Notice right here a few things with your API key usage, it's nice to know that you're going to get at least at the time, 10,000 credits per month. I'll show you your credits today and yesterday. It's nothing you need to keep track of, but just keep in mind that it's enough usage on a free plan for you to use for you to use this tracker worksheet fairly regularly. And it's also a good thing to remember that this API key is something you should keep private. Okay. Having said that, now that you have your API key from Coin Market Cap, we're ready to connect it to your Google worksheet. In the next video, I'll show you just how to do that. So click on the next video, and we'll get to it. See you there. 4. Adding the API to Google Apps Script: Now that you have your API key from coin Market Cap, we're going to connect it to your Google worksheet. And it's a really easy process. Just follow along. And it's best to open up your reference sheet just in case. And if you have questions, it's a simple process. But my instructions should help just fine. However, in the reference sheet, we have a little bit of code that you only need to copy and paste. So let's do this. Let's first go to your reference sheet and I want you to copy this entirely. At the beginning, it says function, go all the way down here, copy. Command, copy or right click and copy, it's up to you. And then let's go to our crypto tracker spreadsheet. This is what you should see. And let's go to our extension tab. Click Extensions, then Apps script. Once you click on that, you should see a page like this. We have code dot gs. There might be something else in here. Just click in here and highlight everything and delete. If there's anything in there. And then just paste. You can right click and click Paste. And you have everything in here. Now, a quick note. If you're using US dollars, you don't need to change anything. But if you want the website, coin market cap to use a different currency, do something really quick and simple. Number one, understand the call letters for your currency. For example, Korean an is KRW. Let's assume we're going to change it to Korean an from coin Market Cap. If you look for the USD right here, highlight and type in KRW or whatever your currency is. Do it once here. And then once down here where it says USD again. Type in those capital letters, and then click Save. And you should see everything work. Now, it should still look like this, but there should not be a warning sign. Great. The next thing we want to do is add what is called a trigger. That means it's going to refresh this data every so often that we program. The best thing is 10 minutes, ten or 15 minutes. Now, to do that, to set a trigger. Come down to this little icon of a clock, and it says triggers. Click Triggers. Then go down to your bottom right here, add a trigger. And I should say get crypto price. Now, there's nothing else really here, but we want get crypto price. You don't need to choose anything in this head section. Scroll to not from spreadsheet, but to time driven. Scroll down a little bit more. Instead of the hourly timer, let's use the minutes timer. And then choose every 10 minutes, maybe every 15 if it doesn't really matter to you. But do not choose every minute or every 5 minutes because you might use up what you've been given in the API from coin Market Cap. So I'm going to go with 10 minutes. Great. And then all we need to do, don't worry what's over here. All we need to do is click Save, and you might get an error. So let's see. Click Save, and great. You've got your trigger working. However, for the first time, you might get an error message, and there's a quick workaround. Let me show you that. It's on your cryptotraker reference page. Let's scroll down here. And here instructions on how to bypass Google's unverified app warning. If you get this warning, it might say, Google hasn't verified this app. This happened to me. It's not happening now because I've already gone through the process. But on here, there should be a little advanced, let's say, link on the bottom. Click on that and then click the GT and then it says your script name Unsafe. Google will warn you don't worry about it. Just click AOL, and it should start working for. Take note that this warning appears because the script is custom made and not publicly verified by Google. Since you're the creator, it's safe to proceed, so nothing to worry about. Also, M crypto tracker is now fetching data live and making automatic updates. In the next video, we'll go through a more extensive tour on what you will see in your crypto tracker, as well as how to interpret the data and make sure you're using it effectively. So see you in the next video. 5. Understanding Your Crypto Tracker: Now that your tracker is fully set up, it's time to understand how to use it and understand it. At the moment, you should see something similar to this, but by the time you're watching, certainly the prices will have changed. But notice that everything should be working. Let's take a look again from top to bottom and see how to use this tracker. At the moment, we have our top three coins which are displaying real time numbers. Right below that, this is the time it was last updated. Down here, we have a Pi graph that shows the percentages that are owned from the data below. Again, we have our quote of the day. Up here, the initial investment, the current value, and to the right, our crypto goal. Now, what's important is the data below. You're going to want to change all of the data really right here in blue. Take note. If you can see right here, enter purchase data here. So everything under here in blue is something that you want to enter. Notice in the black, you don't want to change anything. So the purchase price, the total value, your profit and loss is here, don't adjust anything. However, you will want to make one small change in this column. So let's go over what those are. Number one, coin name, bitcoin. Give us the name, enter the name. Scroll over and enter the three or more letter symbol. For example, Bitcoins BTC. Make sure it's capitalized. If you want put in the date of purchase, this is just simply good record keeping and good for taxes. Over here, your exchange or wallet, where are you keeping it? Whether it might be finance or a wallet, like Trust wallet or phantom wallet? Here, when you make your purchase, you're going to know three things. You're going to know your initial investment, which is how much money did you spend, the amount of the coin that you actually purchased, which is your amount held, and the purchase price. Now, to make it as simple as possible, once you've made a transaction, for me, it's easiest to know how much money did I actually spend and how much of the coin do I have? Don't worry about the purchase price, it'll automatically be calculated. If you look here, if you click right here, then you scroll up to this formula, it'll automatically calculate. If you don't want to do that, that's okay. Just enter in the purchase price. But to make things simple, leave it as is. And when you enter a new coin, take a look at your purchase or take a look at your coin symbol. You're going to want to come over here, click on that sell. Go up in here, and you'll need to type in in capital letters that coin symbol, for example, BTC. It's really important that you get that right, so it will generate the current price. So let's assume, let's pretend that these are our coins or your coins, and you want to make a new transaction or you just purchase one or more coins and you want to enter that data. So let's do this. Let's do one for Bitcoin. Let's say we purchase Bitcoin again. So I'll type in Bitcoin. B. Then BTC for its symbol. Then it's just any date, I'll just scroll down arbitrary date. Let's say I bought it again on Binance, for example, or maybe coin base, wherever. Let's do coin base. Doesn't really matter. Now, let's put in our initial investment. Let's say we purchased $1,000. Put in $1,000. And how much Bitcoin are you holding from this transaction? I'm gonna put it in here. Let's say it's 0.011 example. Now, this is where we want to do something a little different. We want to highlight this one row. Click scroll over right here where you get this plus sign, hold it down and pull down to it automatically propagates. Now, this data, we need to change only a little bit. See, it's really updating for Bitcoin. So we need to come down here where the current price, click in this cell Go up here and just type in the symbol. Capital BTC, press Enter. You notice everything adjusts to its correct state. So what we see here now is the purchase price has automatically been calculated. The current price is displaying. The total value is now displaying for that purchase. There's been a profit. We have the profit amount, the dollar amount or the currency amount that you're using, and we have the percentage, whether it's gone up or down. Something nice to note, you may have noticed that here we have green and red in the profit and loss areas. If your transaction is making a profit, it'll show up in green, and if it's making a loss or if it's losing, it'll show up in red. So it'll be a quick easy reference for you to see how well those particular transactions are performing. I would like to take a quick moment to mention these other two tabs down here, coin summary and quotes. If you click on coin summary, please take note that you should not alter anything here. This is where the information that you enter on your spreadsheet that we covered, it will automatically update your pie chart. So don't touch anything here. Don't worry about it. And over here, quotes. These are I have those 30 quotes that will automatically display once a day. You're welcome to put in your own quotes or change these. Just follow this format, and it will automatically propagate on your coin purchases tab. Just keep those two things in mind. And that's just about it. Now you have a working tracker with all of your coins and investment data entered and ready for you to track as you so please. However, you might have questions as you progress. So what I suggest you do is download the FAQ tips PDF. Take a look at some of the questions, and it may help answer whatever questions you have and provide some insight into this spreadsheet. Great. Now that you've built a real time crypto portfolio tracker, let's share what we've created in our project section here on Skillshare. Your project is simple. Number one, take a screenshot of your tracker. Don't worry. You don't have to show real investment values. Feel free to use sample data or blur the amounts. And then simply post your screenshot in the skill share project section to share with others. So go ahead and post that now, and I can't wait to see what you've created. I hope that you've found some value in this course and this spreadsheet, and I wish you the best of luck in all your future investments. Bye bye.