Crypto Portfolio Tracker on Google Sheets

hey what's up it's nathan here in this video i'm gonna show you how to build a crypto portfolio tracker in google sheets now i do have two disclaimers here the first one is if you're not familiar with google sheets or excel like this might be a little bit technical um by all means go ahead and ask questions of me in the comment section down below and i'll try and help you out but yeah it's a little a little technical so if you're not familiar with excel or google sheets you know be patient with it the second disclaimer is i'll be using this service over here to help pull in the values of the cryptocurrency so that way you know i can see if i'm making money or losing money and all that type of stuff but this tool right here it lets you pull 50 50 uh times a day of crypto prices so if you're pulling more than 50 times a day or 50 prices a day you're going to need to upgrade to their paid plan which is 10 a month so i just want to put that out there if you're doing a lot of research or pulling prices for a lot of tokens like you might have to upgrade to the paid plan so don't you being surprised if you gotta pay money like i'm not charging you any money for this video and my spreadsheet over here is completely free link in the description down below but again if you're pulling lots of data you might need to upgrade to the paid plan if you want to of course but other than that yeah the spreadsheet and everything else is free so let's go ahead and get into it now so first things first link in the description down below if you want this spreadsheet but you got to go ahead and make a copy of it on your google drive don't try and access mine or edit mine i'm not going to give you permission to edit mine you got to make a copy on your drive so you come up here to file and you do make a copy and then you make a copy of it on your drive so you can go ahead and name it whatever you want you can go ahead and place it wherever you want inside of your drive so you can go to my drive you can create a new folder crypto stuff and that's cool so i put in this folder select and okay so now it's making a copy of the file on my google drive so now i can go ahead and edit it and do whatever the heck i want to do with it so there we go and i might as well close this other one so i don't try and mess with it okay so this one's mine i can do whatever i want so the first things first i recommend going and installing the this tool right here crypto finance so that way we can start pulling in prices um of our cryptocurrency so we'll do install crypto finance and it gives us instructions here so we'll just follow the instructions and you can follow along and or read the website so basically i'm going to go to tools and script editor so we'll go to tools and script editor and i'll pop up our little script editor here and then i want to go ahead and hit files and i want to go ahead and add it and do script right here and i'll go ahead and call it crypto crypto finance and this this is in the instructions over here so it says you know call it crypto finance in the script editor and we had to copy the content of this file here so i'm going to pop this open and links to this website in the description below as well so i i didn't want to include it with this file in case they they make updates to it or something to that effect but we'll go ahead and copy it all and paste it on in here and then i want to come up here and hit save and so there we go it's saved cryptofinance.gs and i can go ahead and close out of here now and then i can come back over to my portfolio and then what we want to go ahead and do is refresh this entire window so go ahead and refresh it real quick and we should see a option pop up here and so after refreshing the browser you should see an option for crypto finance uh if it doesn't pop up right away go ahead and refresh again or wait a minute or two it might take a second for things to you know boot in basically but eventually it should pop up here and you might also have your prices update so you might have noticed that it said like a notice or disclaimer warning or there there's some message in this field but now it's showing us the prices of bitcoin and ethereum so if you see crypto finance up here and these prices are showing now then it's you probably did it correctly okay so so thumbs up to you and so now that we have the crypto finance tool installed let's get into the spreadsheet here so it's pretty simple hopefully to understand basically there's two tabs right here we have the summary tab which we're on right now and basically it gives us you know our summary of the tokens that we have so bitcoin number of coins purchased how much we've spent on all the tokens so gives us our cost basis then so then we can know you know if we're in the green or we're in the red the current price of the coin the percent change so we know how much you know our tokens have gone up in value or down in value like down in value over here our current holdings so how much u.s dollars we actually have uh in our cryptocurrency our profit and loss and then a current multiple so basically you know are based off of cost basis times 6.41 equals our current price and then i just have a notes section over here and basically i'm trying to remember where the heck i'm storing on my cryptocurrency so in my notes i'm putting you know where my tokens are stored of course do whatever you want this is your spreadsheet um also on my spreadsheet i've gone ahead and like added conditional formatting so if my multiple is over five well that's like a sell signal to me that i need to sell some of my crypto because i made a 5x return on it so i i color code mine with conditional formatting and so if you're familiar with uh google sheets or excel you can do all this type of stuff and you know do what makes sense for you right and so i'm going to go ahead and set this up greater than we'll do five and then i'll hit done here and so there we go so if it's greater than five it goes ahead and highlights for me and i can quickly know you know what what the heck to do so all the data on this particular so all the data on this summary tab here is automatically populated so a lot of the information comes from the purchases tab over here and then the current price tab comes from the crypto finance plugin and also we could go ahead and import and i'll talk to you this about this in just a second here but basically everything on this summary tab is automatically populated and you should not be going in here and typing things in unless you want to it's your spreadsheet but the information comes from this purchases tab and you know what it should probably be purchases and sales because we're it includes both you'd want to include your buys and your sales sales purchases sales but it should be hopefully pretty self-explanatory here so every time you buy or you sell a token you come over here and you submit it or enter it under the form so let's say what is it 3 19 20 21 it is 16 47 and then the afternoon and let's say i'm buying some bitcoin actually let's do a different token here so let's say i'm doing celsius token and let's say i buy a thousand celsius tokens and i spend five thousand dollars on it and so my customer token is simply how much i spent divided by how many tokens that i purchased so let's go ahead and we'll just copy that formula and bring it down here and then the exchange so where i bought it from so we'll say unit swap here and i'll just say this is my initial buy so pretty self-explanatory stuff but it gives us enough information to make good decisions so let's come back over to the summary tab and we know that we don't have our cell token over here yet so let's go ahead and insert a a row here and i'm going to go ahead and type in sell and then coins purchase the the best option here is just to copy my formulas that already have so i'll paste it in here and then usd spent paste that in there cost basis i'm going to go ahead and paste that in there current price so here's two different options for generating the current price the first option is to use the crypto finance so this is that tool that we installed earlier was the crypto finance and this is the recommended way to do it because it's faster and um it doesn't have as many errors so i recommend using the crypto finance tool as much as possible but if you aren't able to use it like for example some alt tokens or alt coins aren't able to be found using the crypto finance tool so for example matic here and um for for whatever reason they're not on the the crypto finance tool so we have to use another method and that's to use the import xml function here and so hopefully this makes some sense or it's pretty self-explanatory but basically you're going to copy and paste the url of the token on coinmarketcap right in this field okay that's all you would have to do so for this cell token i'm going to do it both ways so you can kind of see how it works so first way preferred way is crypto finance so we're going to come down here and just paste it in there and it's going to load and we see that it found the current price so i would leave it at that okay it found the price that means it's in that crypto finance app and and we're good to go with our pricing but if a price doesn't show up here like it's just a bar or an error message or something like that that means it's not in the crypto finance app so what you'd want to do then is grab this formula right here and we'll paste it on down here and we need to go ahead and find the coin market cap link for the celsius token then so i'm going to come over here and we'll go to coin market cap and find the celsius token so come on down here i think it's in the 50s and so here it is down here at 74 so i'm going to click into here and i'm going to go ahead and copy this url come back here and then i'll just swap out this link right here and then i can go ahead and hit enter and it's going to go scrape the price off of coinmarketcap.com but sometimes it works sometimes it doesn't okay the the whole scraping off of coinmarketcap.com is like a 50 50 chance that it works so you might get error messages sometimes when you're doing the scraping method which is why i recommend as much as possible use this tool here the crypto finance tool because it's faster more accurate and less prone to errors so anyway these next columns i'm just going to go ahead and copy them real quick and paste them on in here and it'll automatically do all the calculations and math for me and then i need to go ahead and probably adjust these total columns and make sure that i extend them all the way down so let's go ahead and do that real quick to the g7 or this row seven there and it'll automatically update the profit and loss as well as the the percent return of my total portfolio down here and so for example let's say i make another purchase over here and we'll just say 3 19 20 21 and it's 16 54 and you can put whatever time stamp you want in here you don't have to do military time or anything like that or utc time or whatever you can do however you want whatever helps you keep track of it the main thing is to make sure that your coin your symbol here matches what you have on your summary tab because that's how it kind of connects things together is whatever your symbol is is how things are matched up and so let's go ahead and throw bitcoin in here bitcoin and let's say that i bought one bitcoin for about fifty eight thousand dollars so my cosplayer coin we can do that simple math right there let's say i bought it on voyager and regular buy all right cool so then if i come over here we can see that it automatically updated the number of coins that i purchased to one and we can see how much i've spent my cost basis current price percent change current holdings profit and loss current multiple etc now if i want to go ahead and force these prices to update you can come up here and you tick this box here and it'll automatically go back out there and grab the the latest price available for the token and then if you uncheck it it'll do it again so that's kind of how you toggle to force it to go check and not check and again you only get 50 free ones a day unless you upgrade to the paid plan and then you get like 20 000 or something like that so be careful toggling this back and forth because you might use up all 50 of your free price pools but yeah so that's pretty much how it all works of course you can get fancy with it and add some graphs and charts and do whatever the heck you want to do but for the basics that's how it works hopefully you found this video helpful or interesting again links in the description for the spreadsheet and everything else so if you have any questions as well please don't hesitate to ask them in the comment section below and also make sure that it's me replying i will never ask you for bitcoin i'm never going to give you my phone number or my whatsapp i don't want to talk to you at all and i don't want your bitcoin or i'm not going to give you extra bitcoin so don't so be mindful of scammers in the comments section it should have a little black box if i'm replying to you but anyway have a great rest of the day

Channel: Nathan - Crypto Investing

Views: 52,338

Keywords: n8hanwilliams, n8hanwilliams.com, bitcoin, investing, financial management, crypto, crypto currency, crypto zombie, digital asset news, altcoin daily, ethereum, google sheets crypto, google sheets cryptocurrency, crypto portfolio, track crypto, how to track crypto, track bitcoin, track ethereum, coinbase, binance, uniswap, coinmarketcap

Id: i221k1vBhe4

Length: 12min 20sec (740 seconds)

Published: Fri Mar 19 2021