How To Track Cryptocurrencies In Google Sheets

what's going on guys in this video i'm gonna show you how to track your cryptocurrencies in google sheets and this will be pulling live crypto data you can see right here i have bitcoin litecoin a whole bunch of other popular uh crypto currencies and this is the current price as i'm recording it if we were to change this to how about litecoin you could see the price updates the chart updates the market value everything updates so let's change that back to bitcoin so btc usd and you can see right here we can add coins too everything is dynamic everything updates so it took some time to figure this out but i am now going to reveal it to you guys so you know exactly what you need to do to track your cryptocurrencies in google sheets and before i do that the only thing i'm going to ask of you guys is to smash that like button and subscribe if you haven't already i have tons of videos tons of playlists going over google sheets a whole bunch of other different tips and tricks and tutorials in the stock market uh and if you want you could support me on patreon or you could even check out my uh discord i finally got a discord trying to grow that as well but let's jump right in so the first thing i'm going to do is actually copy all of this we're going to jump over to a brand new slate and we're going to paste this right here to get things started so there's one thing that we need before anything else and that's going to be our price we want to know where are we pulling price from and it's none other than yahoo finance so here's bitcoin it's currently 26 000 628. we're going to be pulling from yahoo finance now here's the only thing it's not perfect yet we're actually pulling the previous close price which is down here can't get the life price unfortunately it'll have all this after it and then it just doesn't convert well uh so this is the best i got for you guys at least it's something uh there really is no good place to pull cryptocurrencies yet hopefully in the future it will make it easier we could find it on finviz and then that'll solve all of our problems but we're going to be pulling from this i'm going to be pulling this number right here so we'll have a little delay but it's the best i got so let's jump right in on how to do it so the first thing we want to do is do equals if is blank so if you guys have watched my other videos before you already know about that uh so pretty much what this is saying is if a3 which is where we're putting in our coins is blank then we want to leave this cell blank this is so we don't get a bunch of n a's going all the way down it looks cleaner i like it that way so after that we're going to do an index and then we're going to do import and you want html not xtml so i'm going to do html and if you guys have watched my videos before you already know where this is gonna go we're gonna do concatenate so appends strings to one another it's a little bit of coder language for you guys that don't know when you put anything in quotations like that it makes it a string um and then the concatenate will allow us to add stuff into that string so what are we going to be adding into this string well yahoo finances uh address right up here so https yahoo finance quote all the way before you see btc so before we pull that this is what we want so you could copy that go back into here we could paste it close that off hit comma and then we click on a3 then we could close that so what do we do from here well now we need to pull from a table so you're going to type in table close that so what table are we pulling from well let's jump back to yahoo finance there are two tables here this is table one this is table two we wanna pull from table one and we want the previous close which is right here that twenty six thousand six hundred and twenty eight so to do that what we're going to do is we're going to go back up here and we're going to hit 1 that means we're pulling from table 1. we're going to close that off and then we want to do 1 comma 2 close both of those off so that one comma two represents row one column two so that's column two and this is the first row this is row one row two row three so you can pull any of this data you just gotta mess around with where you want to pull from within the table so we got table one row one column two we hit enter and now it's blank you must be wondering what i do wrong well nothing that is the if is blank so if a3 is blank so if we go back to here if we type in btc dash usd hit enter you could see the price updates and then if you want we could add a dollar sign onto it so then we could convert it to a currency we could center it as well i always like to center everything we'll center bitcoin as well and we got the price that's the hard part i mean the rest is going to be smooth sailing but from here what you want to do is actually click on that go here and depending on however many coins you want or plan on owning whether it's 10 20 100 let's just scroll down to how about 20 for now so now i just put that code in all the way down to 20. so you can see all of these are updating a5 a6 a7 a8 a9 so if we go here let's add some litecoin there you go litecoin is now in there as well so you could keep adding all the coins you have if we want to go over here maybe just copy what i had here so let's copy and let's put that into here great now we have all the different coins we move on to the next step and that's going to be market cap so how do we get market cap well much like price we're going to be pulling from yahoo finance and it's just going to be a different place so market cap isn't here market cap is over here so we're not pulling from we can't do table one column one or whatever and that's because this is table two so we pulled from table one so now we gotta change it to table two column one ah sorry row one column two so we're taking table two row one column two so if we go over here we could just kind of copy this and then paste it there you just want to make sure this stays a3 and a3 you don't want that changing to b3 that'd be pulling from the coins but if we do that and we go over here and just change this to table two row one column two hit enter auto fill now you can see all of this is right this is the market cap so if we jump over here see it says the market cap is 494 billion it says 4.95 there's going to be some slight variance but it's uh it's pretty close for the most part uh now circulating supply so if we go to yahoo finance you can see the circulating supply says n a i don't know why it says that on yahoo finance but the code is actually there this is what i mean so we know we're going from table two now we're going row two column two so two two two so if we jump over here we're gonna copy this again right so you can copy all of this we go to circulating supply paste that so change this one to a two and look at that it's actually there so let's just center that make the text the same make that eleven okay so you can see that it's there i mean the code is there for some reason it doesn't show up on yahoo finance's end but this is currently how many coins are out so that works again i don't know why it's not showing up on yahoo finances and but who cares our google sheets are better than yahoo finance here it is so now we're almost done with at least plugging everything in so we could have a fully functioning uh tracker and then we can start to add in some charts so now we need to do market value and coins let's say we own one two hundred ten five thousand one thousand one hundred and fifty all right that's just making it exactly the same for what i already have over here so now how do we get the market value well this is the easy part you guys should already know how to do this we're going to do equals if is blank if is blank a3 close comma parentheses you see that it says false so it's not going to do that um and then what we want to do is our price times however many coins we have it's very simple hit enter that's it auto fill and there's all of our market values and if we just jump over you see all of those if we jump over to this one you can see everything's right all it's doing is just taking whatever the current price is and multiplying it by however many coins you have and that's what you're getting there so it's very straightforward very simple so if you want you can take this make this a color so we'll just kind of i think i had it like a yellow or something like that that looks good now we could move on to the charts guys we already did the hard part this is now done with we could jump over to the charts but before that again i'd like to remind you guys smash that like button and subscribe if you haven't already anyways let's jump over to the charts so you're gonna go to insert and you're going to go down to chart you're going to click on that and what we want is a pie chart so we're going to do a pie chart and we're going to go to label it says select data range well where do we want our data range that's going to be all of our cryptos so just go down the chart like that hit ok and then value well what's their values you're going to click on value go to your data range and it's going to be the market value so that's going to be row c go all the way down hit ok and then there you go now you might be thinking hold on what i have over here is this nice 3d how do i do that that's actually simple you just click on this to bring up this where it says donut chart and then if you click on donut chart and then click on 3d it makes it 3d with the donut so it's a nice little trick to know but now we could go to customize and let's add in titles so your title about my wallet or my crypto wallet i think i had it you could bold it we'll make the color black and we will center that maybe make the text a little bit bigger that looks good and then if you want what you could do and you could see over here i made the background purple and i made all these different colors uh to do that it's really simple if you go to customize and you go to chart style background color change that nice uh purple that i had it and then how do we change all of these if you double click on them i just clicked on where bitcoin was this pops up your pie slice so i could change this to whatever color i want so i had that one like a nice orange add litecoin a dark blue something like that this one was a grayish this one was black and i think i just kind of match the colors with uh you know whatever the color of the coins are uh let's see this one was like a dark gray we had a light green for the neo one something like that and this one was kind of a dark blue something like that anyways you can mess around with the colors make them whatever you want we could close this up make it a little bit smaller there we go let's x out of that and we could take this and bring it over here and then the very last one actually think i had this even smaller maybe something like that let's take a look that was even smaller i guess there we go so now the last part is now adding in that bar chart as well so to do that it's very simple it's going to be the exact same thing essentially we're going to go over the chart uh but instead you're going to go to bar chart well that's a stacked one it's a combo here we go column chart and same deal our x-axis are going to be the cryptos so highlight all of that and our series is going to be our values so highlight that we now have that created we double click on that there is a way to make them 3d right here you click on 3d there you go and then again for the background i had it that purple um for all of these i just had them the same color and it was that like orange color i believe there we go and almost done here guys the last thing is going to our title again and we'll add my crypto market whoops mr c my crypto market value and again we can make that black bold it make the text a little bit bigger and center it x out of that shrink that a little i shrink that a lot more and there we go that is your custom cryptocurrencies tracker we are pulling from yahoo finance it is not perfect because it's not the current value but it is the previous close value at least this is a better way for you to track it in google sheets to pull the data again if you guys know how to pull it from elsewhere drop those comments below reach out to me and let me know i can make a video of that so we could all learn together but other than that uh you guys rock smash that like button subscribe if you haven't already as always i will see you guys in the next one

Channel: Think Stocks

Views: 41,113

Keywords: Think Stocks, How to track cryptocurrencies in google sheets, cryptocurrency in google sheets, cryptocurrency excel spreadsheet tracker, cryptocurrency tracker excel, bitcoin tracker google sheets, bitcoin price in google sheets, how to pull bitcoin price into google sheets, how to track my crypto portfolio, crypto portfolio tracker, crypto portfolio tracker google sheets, crypto portfolio in google sheets, bitcoin portfolio google sheets, track your cryptos in google sheets

Id: Iev_tGGFoGs

Length: 16min 35sec (995 seconds)

Published: Fri Jan 01 2021