Connect Crytpo Portfolio to CoinMarketCap API

hey there everybody this is xrp lazarus and i wanted to give you a quick tutorial of how you can connect an excel portfolio to the coin market cap api and i'm not going to walk through how to build the whole thing this is just going to be a video for people who don't really want to learn how to do it but want to learn but want to be able to use it for their own coins so just going to be step by step so step one in the description of this video i'm going to have a google drive link here so go to your web browser paste the link in it's going to take you to this spreadsheet over here on the top right you're going to see a download button click that download button i'm going to close this down so you can see exactly step by step how to do it so once you get it downloaded it might show up in your downloads folder up here or it might show up like this i'm just going to download it and save it to my documents now you have the spreadsheet um make sure to enable all this stuff in the spreadsheet so this spreadsheet is set up with a coin market cap api that i just generated um don't use the one in this spreadsheet because i'm going to disable it later on so i'm going to show you right now how to get your own so the first thing you want to do is you want to go to coinmarketcap.com or pro.coinmarketcap.com or you can google coin market cap api and then once you get to this page here you click get your api key now and once you click that it wants you to put in your email address and all that information i already have an account so it's not gonna let me do it but put in your email select that you want the basic version which is the free version and then you're going to get an email in your email address asking you to verify your account so obviously you're going to want to go in there verify your account come back to pro.coinmarketcap.com sign in and once you sign in here on the overview page is going to be your api key down in this box you're just going to click copy once you get this api key you're going to want to go back to the excel spreadsheet go to the data page go to queries and connections double click on this connection here once this query editor loads up you're going to want to click here where it says data source settings and then you're going to want to click change source now basically um this is the link with the api key that i'm using right now but you're going to want to basically take everything after the equal sign and everything before the and sign here and then you're going to want to delete it and then paste in your own key mine it's going to be the same key because i'm using the same one and then you click ok you click close and you click close and load it'll probably ask you do you want to save changes i didn't really change anything so it didn't ask me but if it asks you if you want to save say yes um and then once that's all up and going you can just come in here um delete these coins out i'm going to just give you a quick walk-through of how this works these are just random coins i put in here to show you it works so over here on the right side you have two tables this table is going to be essentially where you put all your purchases into crypto you can do it however you want i like to track everything even the stuff i've profited on that's not in this portfolio here so for example if i bought coin sold it for a profit and i'm not holding it anymore it wouldn't be here because i'm not tracking it but i want to see my total profit not just my current profit on whatever holdings i have so put all your purchases in this one and it's going to take the total here and it's going to put it here for purchases and then all your sales everything you sold to make a profit on in this box um you can put the date that you sold it the amount of coins the dollar amount and then it's going to take the total from this box and it's going to put it here so as you can see in this template portfolio the total amount invested is 13 105 but i really only put in 9 000 of these dollars because i've already taken some profit out before or used profit to reinvest into these coins so it's something that you probably want to know so now for your own coins here what you can do is you can just delete everything except the top row starting everything in this table go to delete go to delete table rows and then you can just hit tab now you can change this one if you don't have this coin you can change it to btc and it'll put all the information for btc here now obviously you're going to want to change the invest amount to whatever you have so if you put 500 in and you got .03 or something like that or probably whatever it doesn't make a difference but yeah we'll say you bought it at this price then it's going to tell you what your roi is so um to add rows here you're just going to want to click into the last one and keep hitting tabs so if you have 10 coins you can just keep clicking here and hitting tab until you get about 10. then you're going to want to drag all these formulas down so you don't get the reference errors now you can enter whatever coins you want so like xrp um uh what else litecoin basically any coin this is gonna this uh tracker here as you can see on the queries and connections tab it has thirty thirty one hundred rows of data so that's how many coins it's tracking i don't think it has everything but but it may it has the vast majority of everything and as well there's some hidden columns here that i hid that you may want to see this one shows the name i want to be able to see um the tables and everything in one view so i just hide this i don't like to see it but essentially you just keep putting your coins in you put the amount so a hundred a thousand um and it's going to track everything now this one's showing up black because um for example if you have a lot of stuff in this portfolio um if you have like 20 coins you might want to highlight one so you can just look at that one so you could go down here and highlight and then you can just pick whichever one um whichever one you want to highlight so btc it's not really helpful if you only have four coins so if you have low coins um you can just go down here and select total and um that's basically it once you get all the coins in here i'll put a couple more then everything should show up as it's supposed to but of course you still need to fill in these i'm just going to put random numbers in here because it doesn't matter but yeah you pretty much get the point here everything that you put in here is going to show up in this portfolio right here um if you don't fill it out right here it's not going to show up right and then over here it's going to show your profit for each coin and all that so if you don't have the new version of excel you could run into some problems with these tables here i'm not sure if these tables match exactly how in every version but if you do have any problems you should go back and watch my previous video where i showed how to set these kind of tables up step by step and i could i can show you a quick one now basically you could do an insert bar chart 2d column here insert bar chart 2d column now i have a table then go to select data you can select the ticker and then you can select the profit and then basically let me try that again yeah there you go and then that would pretty much fill your coins and then after you get that set up uh you could go to um chart design and then look at the chart styles and then pick uh pick whichever one you like best so that's pretty much it that's a really quick tutorial of how to set all this up um i was just gonna explain um this uh this is the amount of money you put into crypto this is the amount of money from your sales into cash um this is how much you're currently exposed to based on your purchases and your sales this is your net profit and this would be your total ri this is the total current value of your portfolio and this button here is what you would click to refresh the prices so if i click this um this total 1655.78 should update there we go so 16 35 12. so it updated um and just in case you need to like if you want to see the data here's the page that has all the coin information now you could run into some issues um where some of these coins are named the same for example vlt you'll see there's two vlts here so i can give you a quick step by step of how you can get around that so as you can see here with the name column that's here it says velcro that's not what i want um the one i'm interested in is the other one bankroll vault so i can just copy this paste it here and then i can change the lookup that looks up the price instead of come looking at vlt i can change it to look at vault but that means i need to also change this so that it looks at the correct column which is c and then change this to 12 and then boom 61 cents so if you come back and look bankroll ball if i go over to the price here 61 cents so that's how you do if there's duplicates i'm sure not many of you will run into that problem but one thing to note is if you do that make sure that this formula doesn't stick to this one and instead what you have to do is you copy this one whichever one is looking at the right column so for example uh this one here is looking at um bitcoin or you could keep it like this this works too actually you could just leave it the way it is because everything's going to run off the name so that's fine you don't actually have to change anything else except update this one and that's it you're pretty much done if you put your coins in here everything will show up as it's supposed to and then delete this out that's the whole tutorial um if you got some use out of this video go ahead and hit the like button that way um other people who might want to see this can happen across the video other than that that's going to be it thank you guys

Channel: XRP Lazarus

Views: 21,160

Keywords: Cryptocurrency, portfolio, dashboard, excel api, coinmarketcap api, excel portfolio, excel, coinmarketcap, Crypto

Id: tOsIyAb-0T0

Length: 12min 35sec (755 seconds)

Published: Fri Aug 21 2020