Think Stocks Crypto Tracker | Coin Market Cap Data

what is going on guys coming back at you with yet another portfolio tracker this is my second crypto tracker a lot better than my first crypto tracker we were pulling from yahoo finance this one we are pulling from everyone's favorite coin market cap it's quite an easy table to pull from to scrape data from uh and you could see we have a decent amount of information here if i zoom in we drop down i'm pulling all these coins we've got bitcoin ethereum litecoin file coin chain link polka dot cardano dogecoin and you can see we are getting the price the circulating supply the market cap the max supply the rank and the market dominance we are not limited to just that we can also pull let's jump over to this little screener i made we could pull all of this data as well and you can see we have a nice little drop down let's go to ethereum all this will load boom there it is we could zoom in just so you could kind of quickly glance at all of the data that we could get but it's uh it's a pretty decent method of scraping i do have a love-hate relationship with it and i'll get into that a little bit later in the video but a lot of you guys want to know exactly how to pull from coin market cap so that is why i'm making this video because you guys have asked over and over again and you can see obviously it's a pretty simple portfolio tracker we got here we just have one main dashboard everything's right here but the point of the video is to really learn how to pull from coin market cap and you can make trackers that are much better than mine but i i think this layout is very sleek sexy whatever you want to call it i love how it looks and you can see everything's dynamic if we go down here we've got my coins my costs my gain let's say instead of five thousand dollars cost me ten thousand dollars to buy uh 0.25 coins we hit enter see how everything just updated this updated my profit up here updated let's say instead of 20.25 i had one bitcoin i mean you can see all of this updated now that is through the roof my profits through the roof uh bitcoin would be 55 of my portfolio let's change that back and change that back i don't own this much bitcoin or crypto currencies uh i don't have 56 000 in cryptos it's more along the lines of around 7 000 but i just kind of made this just so we could get an idea of how to make this kind of tracker so we are going to start fresh like always i'm going to right click i'm going to go to new google sheet we're going to open this up and the first thing we're gonna do is i guess create that little screener this screener that i had right here just so you could get an idea of how we're pulling from coin market cap because it is pretty simple and this won't take long at all and then we could dive into the dashboard but new sheet the first thing that we have to do right up here is we'll type in bitcoin so there's bitcoin and we want to be able to pull the first table within coinmarketcap so it's a pretty easy equation it's going to be equals index open parentheses we're going to import html and i know a lot of people at least on youtube you can see a lot of people are using um apis to pull from coin market cap which those do have their benefits but a lot of them are paid and you can only get so many calls a day so that's why a lot of you guys like scraping instead because it's like it's free but here we go so it's going to be index import html concat open parentheses then we're going to do and we can actually just pull right from coin market cap so if you go to coin market cap and you want to obviously copy this i'm going to hit right click copy and actually we can even go as far as clicking on bitcoin and that will just get us the currencies we want currencies as well all the way up to the forward slash so ctrl c jump back open quotation control v so i have https colon forge less flow slash coinmarketcap.com forward slash currencies forward slash throw in another quotation then do a comma we want to pull from a1 and then we are going to close that off hit comma open quotation we're going to pull a table and what do we want we want the very first table which is table one close two parentheses hit enter it's loading boom there we go so what do we have here let's kind of clean this up a bit we could expand this cell and this cell uh we could center everything just so we could see what we're pulling here but i mean this is this is the formula it's right here it's a pretty simple one like i said all we're doing is just kind of import html concat maybe that's the only advanced part but it's just like anything else where we're pulling from the website we are saying we're plugging in the cell we want to pull from what table and this in this case it's table one so you can see everything is laid out here we got our bitcoin price we've got the price change in the last 24 hours we've got the 24 hour low and high let's jump back up to cell a2 if we change this from 1 to say 2 we hit enter here's new data so you can see here we have the market cap uh the and the fully diluted market cap let's jump back up here let's see what table three is it's loading we got yesterday's high and lows all of that good stuff so you can see these are the tables we have i think five or six tables so we're gonna plug one back in and here's what we're gonna do so we have one and obviously that's good and all but now we're gonna copy all of this hit control c we're going to drop down to we'll drop down to 10 hit control v and we're going to change this from 1 to 2 and then we are going to go right down to 12 hit control v again we're gonna change this from one to three okay and now we're going to go up here we're going to go to c hit control v we're going to change this to 4 go we'll go down to uh on that 10 i'm hitting ctrl v i'm going to go to 5. actually don't need that gap i'm going to delete row 9 just because there's no data in there so i'm going to delete that just so everything's closer together and i think that might be it we might only have four let's let's see if we have a fifth one then we pull one two three four i think we already pulled five right let's see here yeah that was five so let's see do we have a six table six we don't so there's no table sex there's only five tables which is fine that's more than enough data so we have all of this now this looks very ugly we could obviously clean this up to make it look nice like how i had it so i'm just widening all of the cells here and after that i want to go to a i'm going to bring it to the right b i'm going to bring it to the left c bring it to the right and d we're going to bring it to the left we could widen that there we go could widen c a little bit more we could widen b just to space everything make it look nice we're going to merge the first four cells up here we're gonna bring bitcoin to the middle and we will make the text i don't know how about 36. nice and big we'll bold that as well we'll actually bold everything we'll go all the way down we'll bold everything and at this point we're almost done with this and then we can move on to the dashboard i just kind of want to neaten it up we'll line it on the center there and let's add in that nice color that i had so if we go to here i'm gonna go to custom and i'm gonna drop this down to like really dark gray that looks good and i had a nice neon looking green so it was i actually want the exact one that i had so i'm going to cancel i'm going to jump over here because i really like what this green was what was it 0 0 ffc7 which we're going to go back to here add go up here we got 0 0 ffc7 hit enter boom there's our lovely green i'm going to go all the way down here we're going to make that also green and i'm going to go here and we are going to make that green as well i also liked the purple i feel like the colors just matched well together what do we got 9 c 8 a f f see if i can remember that 9 c 8 a f f 9 c 8 a f f boom that's good highlight all of that make it the same color highlight all of that make it the same color uh we could probably bold everything and the last thing i had is a off-white border right above bitcoin just kind of separate everything and actually if we go to a and add one to the left and make this very small like that and then we can make f very small like that and then g all the way to z we could delete right click here so we see what we're doing i'm going to delete missed a couple right click delete there we go so we're done with that uh great let's name that rename crypto screener all right and you can see now it's completely dynamic if we type in ethereum hit enter this all loads boom new data great let's now move on to the fun part and that is the crypto dashboard so we're going to rename this rename crypto dashboard and the reason why i made you make that screener is because we're going to be stealing this function here we're going to kind of be adding to it making it bigger so the first thing we're going to do in the dashboard we're gonna start to write everything out so we want crypto my coins my cost my gain and you guys don't have to have the exact same stuff up here my value i have the euro in here uh you can put in whatever currency you have so maybe it's not euro maybe it's british pounds maybe it's rupees whatever it might be maybe it's usds and you don't even need that one because we're going to be doing this all in usd but if you are not trading on usds and you want to see what it is in your currency then definitely throw that in max supply we want market rank and then we want market dominance i'm just going to go to tools spelling spell check all right we add supply wrong all right great now that all of that is done we can well we can make supply a little bit bigger circulating supply so we can see that same with market dominance we can make a little bit bigger great so once we have all of this make this a little bit bigger we could start to uh add in all of your positions so let's pretend i have bitcoin ethereum and obviously everything needs to be spelt right here when we're pulling from coin market cap uh don't put in the kryptos ticker like btc you actually have to type it all out bitcoin ethereum litecoin file coin chain link doing polka dot cardano and dogecoin everyone's favorite now the one thing that sucks about this code or scraping i tried guys but i cannot get any currency that has a space so by nance coin i cannot pull uh bitcoin cash i cannot pull warped bitcoin all of the usd coin i can't pull these for whatever reason the scraping doesn't work if you guys can get it to work drop that in the comments below let us know because that would be pretty helpful any while you guys are at it smash that like button and subscribe if you haven't already to boost my algo and boost this video so everyone could see it and know how to pull from coin market cap uh and if you don't want to write this all out guys you can check out my patreon all of my portfolio trackers are on my patreon links in the description done self promoting myself now we're just going to type in how many however many coins you have so i put in let's say 0.25 five 25 25 150 200 5 000 and 10 000. again i do not own these i do have some bitcoin and some ethereum but it's more along the lines of .04 bitcoin and ethereum i think 0.6 again just getting my feet wet into uh kryptos and now for cost you're going to put in what your dollar cost average is for however many coins you have it's not like my portfolio trackers for the stocks where we're putting in our our purchase price per share and then it averages our cost if you want to go ahead and make another row and do that so maybe you want to do create one to the left and you want to put in maybe your dca your dollar cost average or whatever so let's say you bought 0.25 uh coins at 50 000 and then my cost would be equals 50 000 times 0.25 that'd be you'd be dropping 12 12 and a half thousand dollars in that's how that would work i'm not going to do that for this video but again guys if you want to do that go for it but i'm just trying to make this a little bit more simplistic so five thousand seventy five hundred four thousand uh we're gonna do two two five zero three five zero four thousand two five zero zero and fifteen hundred so after we have that now for my gain we're going to hold off on that because we need to go to price we're going to jump over to price now and it is going to be a little different than what we have going on here because this is what we want to pull but you can see if we plug this in we're pulling all of this and we don't want all of this we just want this cell so to do that it's pretty simple we could copy this jump over here and go back to price control shift v so it's loading we want to change this b1 instead we want a2 hit enter loading and we're getting an a because i spelled bitcoin wrong bitcoin hit enter and it loads but again we don't want all of this so what do we have to do well we need to tell the function what parts of the table we want so we want let's see we want to be in the first column or first first row so this is the it's in the first row so row one and column two this is column one this is column two so row one column two is the price so this is what we wanna do we're gonna hit comma one comma two hit enter boom look at that now we just pulled row one column two but that's not all we wanna do because if we drop this down we can uh look it'll pull all this but now we have hashtags for whatever reason we don't want that we want that to be nice and clean we want it to be clear so we gotta put something in front of index so this is what we're gonna do between the equals and the i or equals an index we're going to do if open parentheses is blank open parenthesis we're going to hit right here a2 close comma if error open parentheses zero comma zero forward slash zero or zero divided by zero close that hit comma so we've got if it's blank a two if error zero dash zero we could close that space if we want hit enter and now when we drag this down drag it down i guess 50 cells look at that now this is blank so that is good you can see you might be wondering what's if error well this is just more proper code or best practice code instead of just doing uh is blank comma quotation quotation comma again this is just more best practice thanks to someone pointing that out in one of my past videos but now we have price so because of that we could jump back we'll jump to value so now that we have price we can jump to value and we're actually going to go back to price we're going to copy if it's blank a2 if r0 we're going to copy all of that up to the comma we're actually going to get the comma hit ctrl c hit enter we're going to go to value hit ctrl v and we're going to go into this cell and what we are going to do is c2 b2 so the amount of coins that i have and we are going to multiply that so ctrl eight to do the little multiply button price so that's how we're getting our value pretty straightforward the amount of coins so 0.25 multiplied by 53 000 equals 13 464 that's how much i would have we're going to do the autofill great and we're just going to drop this down you know again go down like 50. i doubt you have more than 50 coins maybe you do uh but what we could do here now just bring it back to the default 10. so now my value's done great now we can move on to my gain so my gain we're gonna do the same thing we're actually gonna copy that if error all the way up to the zero with the comma hit ctrl c hit enter we're going to go to gain hit control v after the comma we are going to want my value which is in the way so my value is it's in the way the function is covering it but we want e2 so we're going to type it in e2 uh and then we want to subtract that from our cost so again pretty straightforward math hit autofill all we are doing here is we're taking our value and subtracting it from our cost and that's how we're getting gain pretty simple stuff we'll fix the format later now we're going to jump over to currency in case you are not in the us and same deal we're going to want all the way up to that comma the whole is blank if error we're doing the same thing we're pasting the if is blank into there and then after the comma we're going to want obviously our value so that's e2 so type in e2 then we want to multiply that by google finance open well we already have an open parenthesis we want to do quotation type in currency currency colon usd because that's coin market caps price we're gonna hit another semicolon hit the end and then we want to do dollar sign and we want right here so we want the euro right so we're going to do f dollar sign one then we want to hit end again then we want to do quotation quotation close close hit enter it's loading and that's how much it would be in euros let's drag that down and all that's filled in now now maybe coin market cap i don't know how it is maybe it's the same as yahoo finance where it just kind of will pull whatever your currency is in but if it is in usd's and you want to change it into your currency now you know how to do that it's pretty simple pretty straightforward we can now jump over to circulating supply so circulating supply was a pretty interesting one for me to figure out but what we're gonna do is the same thing make sure you have i think i already have a copy and pasted so we're gonna do controls yep control v we have the if is blank error and what we want to now do is trim so trim open parentheses split open parentheses then we're going to want the index uh import html all that good stuff so i'm going to hit enter i'm going to jump over to price i am going to copy all of this all the way up to the first closed parenthesis well actually i'm going to copy all of it she's going to copy all of that index import html concat with the web address a2 table 1 1 2. hit control c we're going to jump over to name and then we're going to delete those and then right after split we're going to hit ctrl v it's going to copy all of that but we're not done here now we actually want to hit uh we want to go in between the two parentheses we want to hit comma we want to do quotation space quotation two close parenthesis and we're gonna change one to five we want table five and we got a red line because i actually spelt split wrong so we changed that to split there we go no red line underneath so we should be good we hit enter it's loading there we go circulating supply 18.69 million coins we'll drag that all the way down it's loading now we got all of that we can move on to market cap market cap is we're kind of cheating here so if we jump over to here we can see here's let's see where is market cap so market caps right here now for some reason it breaks it into uh there's this and then right below it there's that 12.19 i don't know why it does that i tried to get just the dollar amount alone without the percentage i wasn't able to do it so instead we're going to kind of cheat here and well i guess it's really not cheating i'm going to copy the if is blank error i'm going to paste that in and after the comma what we are going to do is we're going to take the price and we're going to multiply that by the circulating supply because that's essentially what the market cap is that is what the market cap is it's the current price multiplied by how many coins are available i'm going to hit enter do that and we can drag this down and there you go and the one thing that kind of sucks is it will be slightly off so if we type in bitcoin hit enter here's what i mean so the market cap this is one comma zero zero seven this says one comma zero zero six so it's ever so slightly off but at that point guys i mean do you really need to know if it is one point zero zero six trillion or 1.007 trillion i don't think so you're still within the same ballpark for everything so that's uh that's the only downfall but it's good enough it works we're going to move on to max supply and max supply is going to be very similar to circulating supply we're going to copy all of this we're going to then put it into max supply but instead of 1 we're going to change this to 3. everything else could stay the same we're going to hit enter and there you go you can see it says no for any coin that doesn't have any mac supply if you're curious what it actually says we are pulling from the table and then we're cutting up one of the words it is saying no data so we're cutting data and it just says no but it still kind of works max supply no so i'm going to move on to market rank which i think is a cool one to have this one's not going to be as difficult as these we could actually copy from just the price so we're going to hit ctrl c hit enter we're going to jump over to market rank hit control v and instead we're gonna be pulling from row seven so seven comma two hit enter auto fill that there we go we could drop that down and the last one is market dominance another easy one guys it is going to be six comma two hit enter boom we could drag this down there's the data i mean again you know how to pull the data now so if there's any other data you want go for it but we're going to start to clean this up now so i'm going to click on this up here and we are going to do default 10 we are going to want everything centered that looks nice and neat now uh we're going to want the market dominance a percentage so we're going to change that let's see market cap i want in dollar signs price i want in dollar signs uh euros we don't want dollar signs so i'm going to drop down to more formats more currencies there's the euro hit apply there we go that's in euros now dollar sign dollar sign we want my cost in dollar signs great so that's all complete uh now let's kind of well we'll worry about the color later we could make market cap a little bit bigger we can make market rank a little smaller my coins smaller not that small though bring that out a little bring this in a little we'll bring the price in a little that looks good we could highlight the first row bold all of that just uh so we could see the difference here and now we're actually going to copy all of this it will not copy we're going to cut so hit ctrl or command x i'm going to drop down to row 24 hit control v and that brings everything down there that's where we want it now i'm gonna zoom out a little because i am working on a laptop that's a little bit smaller so now this might be kind of small to see but it's fine we'll make it work i could actually bring a decent amount of this stuff out i'm just going to kind of highlight all of this bring out a little bit like that maybe a little more all right that's good enough for now so once we have that the very first thing we're going to do is we'll put in the ah we'll wait on the charts we're going to put in we want some nice text so i'm going to go all the way to g and i'm going to select maybe three rows we're going to merge all of that i'm going to type in think stocks crypto tracker hit enter and now i'm going to take h and i those three i'm gonna merge that and then j and k i will take l as well jk and l will merge all of that and then in here i'm gonna type in my profit colon and then right here we're going to want to do equals sum and we want to take the sum of 25 and then go down close that hit enter so that's what we want we want the sum right there of my gain obviously oh and another thing we could do is we don't need this many cells so i'm going to delete i'm going to go down to 1000 go all the way back up uh i'll go to about i guess maybe 50 or 51 and delete that i would never see myself owning more than 20 cryptos but after that we can now kind of change these three make them bigger maybe 36 crypto tracker can be centered this can go all the way to the right this could go all the way to the left that's starting to look good and now we can get to the fun stuff and that's the two charts so before the charts we'll change the color here we'll make that the nice gray we'll start by changing all the text to an off-white we'll jump over to this and make it our lovely whoops our lovely neon green and what we want now is i want my gain so i'm going to highlight this entire row i'm going to want it to also be green and then i'm going to want my cost to be purple again you don't have to have these colors i have whatever colors makes you happy i think i'm going to bold everything all right and then i'm actually going to click on think socks crypto tracker up here i'm going to make sure i have that off white i'm going to make sure it's as thick as possible and i'm going to do the right border so it adds that nice dividing line uh we're going to go to view get rid of the grid lines i'm going to go down to crypto all the way to market dominance and we are going to add another border so bottom border like that to separate everything same thing i did in the screen i'm going to go to a and i'm going to hit 1 to the left i'm going to make a very small like that just kind of for spacing purposes we'll take n do the same thing make it very small like that and then from o all the way to a a we're gonna delete all right now all that's left are the charts so for starters we're gonna do the pie chart pie charts pretty simple we're just going to highlight my value right here we're going to highlight this entire row well actually we're not going to highlight anything yet we're going to go to insert we're going to go to chart i'm just going to insert a chart no data that's okay we're going to go to pie chart label we're going to click on this for the range we're going to want right here all of our cryptos hit enter value we're gonna then want to take our value obviously so go all the way down hit enter there we go there's that so what do i have well first i had all of these that green so i'm gonna make all these green and then after that where is i gotta find it i think it's what the slice is no here we go border i'm going to use that the same dark gray that we've been using to section everything off for our text i'm going to want to bold it i think i use wide we could probably make it bigger 18 18 might be good might be a little too big maybe 16. uh we're going to make it white or at least off-white yeah we'll keep it off-white and now what we want to do is we want to where is it go to pie chart donut hole we want 20 not 25 we want 75 so 75 percent we want to go to chart style background color we want to hit none border we want to hit none and that is good now we can place this right over here or whatever we want it could probably be a little bit smaller maybe like that i want it right behind right below d and let's see d all right so i'm going to move this over now you'll see why so i'm going to go to d12 the right there that's d12 i'm going to type in my account value hit enter we will make sure that that's centered bold probably make it bigger like 12 or probably even bigger than that maybe 14 all right and then we're going to go right here we're gonna hit equals sum open parentheses and we're gonna go to my value highlight all of that close hit enter go up we'll make this 14 as well so that's looking pretty good and now we just take this and look at that because we got rid of the background we could drag it right over try to bring that right into the center where it looks nice we may even uh be able to bring this down one control copy i'm gonna bring it down one delete that let's see how that looks yeah i think that's better so put that right there boom there's that the very last thing that we need now is our other graph so we're going to go to insert chart we get that and the data that we want here we want to go to let's see stacking so stacked column chart our x-axis is going to be right here all of our cryptos so we're going to hit okay and then our series we're going to want to be my cost and my gain we're going to grab well well we can't do that just grab one at a time my cost and then we want my gain that didn't work oh no double click on this again to open it up i'm going to go to setup go down to add series click on that there we go now we can grab my gain hit ok so we have that and now we can plug everything that we need to plug in so let's see we will start with go to customize chart titles we can name it name a crypto wallet performance bold that we'll worry about we'll change the color now so that off-white we can't see it that well but that's okay bring it to the middle we can click on all of the cryptos down here same deal click on the money change the color as well we can then go to customize chart style no background no border so now we're starting to get somewhere we can move this off to the side so we want to go up here to our legend got to figure out where to add titles for the legend actually i'm going to jump back over to the series and we're going to change the series you'll see why so i'm going to remove and remove go back to add series and i'm going to highlight where it says my cost and then go all the way down and hit ok then we will take another series click on that and we want to get my gain all the way down so from my game make sure we get cost and gain in hit okay that adds in the actual words my cost of my gain we could click on them and make it the off white so my gain is red and my cost is blue but we want to obviously change that to our lovely colors so there's that the nice green and we will change this to the purple there we go guys we are almost done here now we can just kind of widen this widen that maybe make it a little bit smaller center that i feel like we can make it even smaller maybe like that and yeah i mean i think that looks pretty good that's everything that we need now everything is completely dynamic here and one thing i did notice is it says 43 that's wrong because i am missing bitcoin i didn't get uh f25 which is my bitcoin right here hit enter there we go 56 000 drag that right there and we are looking good guys that wraps it up again everything's dynamic so instead of two five let's say i had 1.25 bitcoin see how everything adjusts uh we could bring this back down to 25.25 now here's the one thing that i don't like about coin market cap i guess any time the website updates it can break this and this is what i mean so here was the one that we were looking at see how everything just broke uh and by broke i mean the data's not loading you could try to hit refresh and it still says n a it might take a while i kind of found a cheat around it if you go to file make a copy and if you just make a copy of it then it refreshes i'll give it a second it refreshes with all of the updated data which kind of sucks in my opinion so you see i i already was recording i already had to make a copy of one so we could delete that so that's what you have to do i mean you guys want a coin market cap here's how you pull coin market cap but this is uh it's a double-edged sword yahoo finance isn't the best google finance only has like three cryptos you could pull from coin market cap seems the best with the data but it breaks a lot and if it breaks you just need to kind of do that little sheet the file make a copy and then it makes a copy for everything and then you just go back to your original one and we could delete it so here's the original one that now broke we could remove it and we have the copied one that works now again kind of sucks but it is what it is but that is it guys if you like this make sure to smash that like button subscribe if you haven't already uh if you have any questions drop those comments below i hope you guys like this video check out all of my other stuff and as always i will see you in the next one

Channel: Think Stocks

Views: 19,879

Keywords: crypto portfolio tracker, track crypto in google sheets, google sheets crypto tracker, google sheets cryptocurrency tracker, portfolio tracker in google sheets, google sheets portfolio tracker, crypto in google sheets, bitcoin in google sheets, track bitcoin in google sheets, how to track crypto, how to track crypto portfolio, portfolio tracker crypto, excel portfolio tracker, track cryptos in excel, pull crypto data from coin market cap, coin market cap in google sheets, Bitcoin

Id: z8tnM1UDKEw

Length: 41min 53sec (2513 seconds)

Published: Fri May 21 2021