Track your cryptocurrency portfolio in an Excel Spreadsheet, with live pricing data

hey everybody it's paula here from the exile club and welcome to today's video in today's video you're going to learn to recreate this cryptocurrency portfolio tracker in excel this is a live tracker that will update the pricing with a simple refresh this sort of tracker is absolutely perfect for those that are huddling a couple of coins or a couple of tokens it's not the perfect situation for those that are trading and i will do another video at a later stage for those that would like a tracker in excel that are trading but for the moment this is a tracker for those that are huddling some coins now what does it show us well it shows us the currency amount purchased the data purchased the amount invested and then it's going to calculate or pull in the current value profit or loss return on investment 24 hour change current price and daily return on investment from this we're going to build this awesome little dashboard now if you have not yet subscribed to my channel i do hope that you will subscribe now and hit that notifications button so you don't miss any more of my videos if you want other people to find this video i do hope that you will hit that like button and feed that youtube algorithm because it's your likes that make sure my video gets seen by other people for those that are new to the crypto game there is a meetup on meetups.com it is the global blockchain crypto and trading community we have a meet up coming up this week i do hope that you will consider joining us joining us for that but that's a little side's point so let's get back now to this tracker now you will find as i said a link to the meetups group below but you'll also find a link to my website below and on my website you can actually download this tracker if you don't want to go ahead and build it it's going to take us about a half an hour to build which isn't too long but if you want to skip past all of that do go ahead and download the tracker from my website but you are going to have to watch this video to see how to update it with the correct coins the coins that you're using so it pulls in the right pricing for the right coins so if you're ready to get stuck in let's hop into a blank worksheet and get started so here we are now in a blank workbook and i'm going to take these first two rows and i'm going to make them a little bit bigger and i am then going to select the first row and press ctrl and one to get it into my formatting and in my formatting i'm going to select fill and fill effects now for my fill effects in here i am going to take these kind of yellowy goldy colors so i'm going to start with this one and i'm going to go down to the next lightest one and say okay and then i'm going to select these cells directly underneath these and again i'm going to press ctrl and 1 i'm going to go to fill effects and in my full effects i am going to take the third color or the second color and move it down into a lighter color so that will give us that gradient fill at the top of our spreadsheet i'm then going to take the rest of all of these cells down here and i'm going to go to control one i'm going to fill them in in this very light color so now i have my spreadsheet filled in next i'm going to increase the zoom on this to about 120 so now we have our color set up what we now need to do is insert a table and this table is where we're going to insert all of our purchases all of the coins that we actually have so we can start by saying coin we can then say amount purchased date of purchase total invested current value we have profit loss we have return on investment we have the 24 hour percentage change we have the current price and we have the average daily oral i now what we need to do now is make all of these columns a little bit bigger so that our data is spread out across our spreadsheet so that's just a little bit too big at the moment so now we have them spread out across our spreadsheet so i'm going to highlight all of these cells and i'm going to press control and t control and t is going to convert this data into a table for us now these are headers so i'm going to select my table has headers so now in here i need to do a little bit of formatting to the actual table so i'm going to go to my table design ribbon and in my table design ribbon i'm going to select this let me see which one i will select i am going to select this light one because the light one will then show all of the the colors behind and i'm going to take my headers and i'm going to make my headers bold and i am going to center my headers as well now i already have a little bit of data set up but how you're going to enter data into this is you're going to put the name of your coin in here you're going to put the amount purchased so how many of these coins have you bought you're going to put in the date the total invested the current value profit and loss return on investment 24 hour change current price and daily return on investment these are all values that we're either going to calculate or we are going to pull it into our spreadsheet so i'm just going to copy and paste a little bit of data into this table so we have some data to work with so now we have some data in here to work with and i'm going to just change some of these headers to include some symbols so my current values are going to be in dollars my profit and loss is also going to be shown in dollars now you can show this in different currencies if you want to and you just put in your currency sign here that you have so now we have our table of data setup and i'm going to copy the formatting in these cells i'm going to say home format painter and straight under the table i'm going to just add the same color formatting straight under the table to what we had earlier on so now we can start to set up our dashboard and the elements needed for our actual dashboard so in our dashboard we have some cards and some charts and we're going to start by creating the cards so we're going to start with acquisition cost or the cost after this we're going to put in the profit or the loss and then we're going to put in the holding we're going to put in the worst worst return and then we will put in the best return now from here we can do some formatting on all of these cells so i'm going to select all of these cells here and with all of these cells here i am going to go to my formatting ribbon so i'm going to go to home and in home i'm going to fill these cells and i'm going to fill them in in this kind of brown color and i'm going to put the text in a the bright yellow color i'm going to make the text bold i'm going to make it a little bit bigger size 14 and i'm also going to center it into each of these cells now the cells directly underneath i'm going to select all of these cells as well directly underneath and with these again from my home ribbon i am going to put a border all around these and then with these cells that we put our headings into i'm going to select these as well and i'm also going to put a border around these but i'm going to put a thicker border around these now row 15 i'm going to make row 15 a little bit bigger so we can put our values into these now our costs well our cost is going to be the sum of our total invested so i'm selecting a column from a table here so we don't see the cell references we see a table reference as well instead so we have our total invested amount now i'm going to take this and i'm going to make this bold i'm going to make it center and i'm also going to make it a lot bigger text i'm going to make it a size 16 text actually i'm not going to make a bold i'm just going to make it a little bit bigger instead of bold now the profit and loss well we don't have a value in but again we can take the sum of our profit and loss column for our profit and loss and then for our holding we can take the sum of our current value for our holding value our worst return and our best return then we will have to calculate later on so i'm going to use my format painter i'm going to copy the formatting into these cells so now we have some of our cards available on our dashboard is quickly starting to take shape i'm going to take a copy of these two here and i'm going to place them down here because we have a couple more cards that we want to put in so we want to put in our 24 hour change so it's 24 hour percentage change and this one is going to be our total roi so not our average or y it's going to be our total or away so i can delete the values out of here so now it's really starting to take shape so what we're going to do now is we're going to quickly move on and we're going to put in some formulas into our spreadsheet and then we'll connect to some data sources before we make the final charts to finish off on this dashboard on this portfolio tracker so our profit and our loss is going to be our current value minus our total invested our return on investment is going to be our profit and loss divided by our total investment our 24 hour change we're going to pull in our current price we're going to pull in and our daily return on investment is something else that we also have to calculate now the return on investment in here we want to have this as percentages so i'm just going to change this to percentage our 24 hour percentage change is also going to be a percentage column so i'm just going to make sure that it is labeled as a percentage column so next we're going to connect to the data source now in a previous video and i'll drop a link below this video in a previous video i showed you how to connect a coin gecko api and it's the coin gecko api that we're going to connect to now to pull the data into our spreadsheet and we need to do a little bit of transformation and we're going to be using get and transform data or power query for this so let's first hop over to the website so this is the coin gecko api and the api that i'm going to use is get coins and markets now when you're in here you have the option i'll just open another one here to try it out okay so when we try it out we can put in our different parameters so what i have entered into this is the fact that well i want ust as my base currency i then put in all of the coins that i'm looking for data for so we see we have finance coin litecoin polka dot these are all the coins that are available here on my spreadsheet so i have added them into into here now if you're unsure of what the coin names are you can run a coin list first and make sure that you have the right coin id now it's the coin id that you want so it might be worth running your coin list first and getting the right coin ids then putting your coin ids in here when you execute this it's going to give you a url path that you'll need and it'll also give you the response the body type so this url path here is what we need so i'm going to take a copy of this now from here in excel we are going to go to our data ribbon and from our data ribbon we are going to say from web this is going to bring up our from web connector and it's looking for a url so i'm just literally pasting that url in here and saying okay and this is going to go and search the internet for that particular url and get the data that's available from it and it's going to open it up in power query so this is power query opening up now what we see is it's found a list of records we need to first convert this list to a table and once it's converted to a table to table we just say okay to this we can then expand the records so i press this little button up here to expand the records now what we need is the id symbol name we need the current price and we want the price change percentage in the last 24 hours do we want anything else from there no select the ones we want untick use original column and say okay and this has brought in the details for us now into power query now the percentage we need to do a little bit of work on so from transform we are going to go to standard we're going to divide we're going to divide this by 100 because when we load it back into excel we want to show it as a percentage so now we can say file close and load and this is going to set up a new spreadsheet a new worksheet in your spreadsheet containing all of this data so we see now we have this new worksheet and we have our queries and connections i'm going to close our queries and connections and i'm going to go back over to our weekly sheet now in here we can now do some lookups and we can pull back in the values that we want so let's do an x lookup and we're going to look up our coin name so where are we going to look up our coin name or we're going to look it up in the name column and we are going to return the current price and close our bracket and hit enter and that's returned the current prices for us now i'm going to format these into a dollar so it's easy for everybody to see we also need to get the 24 hour change so again i'll do another lookup i'm going to look up my coin name i'm going to look it up in the name column of the data we've pulled in and i want to bring back my price change so now we can see we have our 24 hour price change pulled into the spreadsheet for us so now we have two fields left to fill we have our current value and we have our daily return on investment our current value is going to be our purchase amount multiplied by our current price and that's going to give us our current value so now we can see that we had bought fourteen thousand dollars worth of crypto we made nine profit and we're holding 23 000 so let's look at our daily return well our average daily return can be calculated with a little bit of date math so we know we can first calculate the number of days so if we say today and we just open close that bracket minus our investment date now that's come in in deep format so i'm going to change this from date format to number format so we can see so there's been 30 days since the first of february 21. now we're going to take this 30 and we are going to divide our return on investment divided by the number of days that we just calculated and that's going to give you our average daily return on investment so i'm going to change that to a percentage so now we can see that our this coin here our finance coin is giving us a 12 return on investment per day now we can go ahead and we can calculate our total return on investment or 24-hour change and our worst and best returning coins our average 24-hour change we can take our average function so we're going to take our average function and we're going to take our 24 hour change column and we're going to get the average of our 24 hour change column now i'm going to change this as well to percentage because that is in percentage our total return on investment well that is going to be our profit and loss divided by our costs and that again is going to give us a percentage so i'm just going to copy the format from this one over onto this one now i'm going to take a copy of this cell and place it here and i'm going to take a copy of this cell and i'm also going to place this cell here as well so what we want now is the worst return or the worst performer and the best performer so i'm going to say i'm going to delete the text that is in the bottom of these and i'm going to say equals 2 and i'm going to say equals to the main and the min is going to be the return on investment and that's going to return our minimum return on investment and i'm going to change that as well to percentage now using this i'm going to do an x lookup and i'm going to look up our worst percentage i'm going to look it up in our return column and i'm going to return the coin name instead so that now gives us the coin name and the return that it's on now we need to do a little bit of formatting with this so what size do i have this text over here it is 22 so we're going to make this 22 and this 22 so let's change these to 22 and see if they fit and i'm also going to center them now because this is the worst returning coin let us change the color of the text on this the worst returning coin isn't always going to have a negative value but it is the worst returning coin so we are going to change the color of the writing and our best return coin we're actually going to fill these in in a green color so i'm going to select this green color for the text so our best return is going to be equal to the max and we're going to take the max of our return on investment and we see we get 34 i'm going to change this to the percentage so we can see it's 300 and then we're going to do our x lookup our x lookup is going to look up that percentage in our return column and it's going to return for us the coin name so now we have our coin name here as our best performer and we also have our worst performer here as well just a little bit more formatting on that so i'm going to add it into the center so now we can see that with very quickly the coin that's performing worst is ether and the coin that's prefer performing best in our portfolio is our binance coin now from here there we're going to create some charts but before we create some charts i'm going to show you how to refresh this data so on your data ribbon if you go to your data ribbon and you hit refresh and refresh all this is going to update your current prices for you anytime that you hit refresh which in turn is going to update all of this for you if at some stage you add a new coin to your portfolio that you want to huddle you can just come along you can insert a row here you can add your new coin to the row but when you do this you need to remember to go back and change your data connection so let me show you how to do that now if we go to data and in data you need to go to queries and connections in queries and connections we're going to open that back up again so this is bringing us back into power query for a second because i want to show you how to add a new coin and up here in our source we can see in our source we have the url that we copied and we can go back to the coin gecko um api website add in the new coin name and then get a new or url and just paste it in here you don't have to redo the query you need to just over paste this https request over paste it with a new one alternatively if you have a new coin we can add you need to add the new coin name then percentage then two to separate the coin so let's say we were adding big bitcoin again i just copied it and press paste so let's say this time it was x or p we would say x or p after we have a percentage and a two and that would then bring x or p in as well once we have added in the new step you need to come over to applied steps jump down to the end to make sure nothing went wrong and then just close and load and it'll update your table in your spreadsheet for you which will then update everything in here so the last step now is to create some charts and these charts are going to show the holdings that we have available so i'm going to select the coins i'm going to select the total invested and i am going to select the profit and loss now the total the total invested plus the profit of loss is going to give you your current value i'm going to insert a chart from here and the chart that i'm going to insert is going to be a stacked column chart so with this stack column chart we need to do a little bit of formatting so we could leave this in down the bottom if we wanted to leave it in down the bottom so i'm going to change the fill so i'm going to format data series and from my dad series i'm going to go to my fill option and in my fill option i'm going to change this to a kind of a darker green color and then i'm going to select the top ones and i'm going to change them to a lighter green color i think i'll also add a little bit of shadow to these just to give them a little bit of depth so i'm going to select the both of the series and add the shadow to the series so i'm also going to remove the outline from this chart i'm going to have no line around the chart and i'm also going to have no feel and no fill means that the chart becomes transparent so now i can take my chart and we can drag it down here and we can quickly see then what we have of each coin we need to put a chart title in here the acquisition cost with the profit and loss and i'm going to just pull that down a tiny bit so it is not lined over so that is our first chart complete our second chart is going to show our return on investment so i'm going to take our coins and i'm going to take the return on investment and from here i'm going to insert a chart and this time i am going to insert a m bar chart so we see we have our bar chart here and again do some formatting so i'm going to remove these i'm going to remove the grid lines on these as well i'm also going to add in some data labels so we can add our data labels in there to see the actual percentages on these i'm going to format the data series as well and i'm going to format the data series more in line with the current colors that we are using so i'm going to go to my fill and i'm going to fill this in a green color and then i'm going to make the gap overlay smaller i'm going to change that down to 50 and that makes these a little bit bigger and then let's go ahead and add a little bit of a shadow to these as well to give it that little bit of depth and then finally in our plot area i'm going to say no fill and no border so now this chart is showing us our return on investment i never said no fail so now we have a return on investment chart as well so we can change the title and i'm going to overwrite that b or percentage or y so now we can see the percentage on each coin that we have this chart also seems to still have the line around it so i'm going to remove the border from around it so it is seamless with the rest of the spreadsheet now the final chart that we're going to insert so i am going to select my current value and my coins and i'm going to insert a chart here so i'm going to select the tree map chart and we can move this three map charts down here so now i can just delete all of these values we can come in here and we can add some data labels now with the data labels we're going to go into more options because we also want to show the value on these data labels now for the coloring on these i am going to go to color and i'm going to select a green color palette for the whole thing there's one green color palette let me go down a little bit more no i'd rather this one so now we have our and we can put in a title because that is our total portfolio and now just like that we have created a dashboard that it's going to track for us the coins that we're huddling now like i said this type of spreadsheet is no good for those that are trading and i will do another video with a dashboard for traders at a different stage but for those that are huddling this is absolutely perfect let's do some finishing up steps so we're going to put in a text box up here and with this text box we are going to say crypto crypto portfolio i'm going to center this so we're going to say home i'm going to center it i'm going to make it a lot bigger and then i'm going to format the actual text box as well so we select the text box format shape and i'm going to have no fill and no line in there too so now we have a little header on our worksheet so that's it that is our crypto portfolio tracker and to update it remember go to data refresh refresh all and it's going to refresh all the data for you and keep it up to date and you're always going to know where you stand with the coins that you're holding i really hope that you enjoyed this video if you did and you haven't yet hit that like button please do so now and don't forget to subscribe to my channel so you don't miss any more of my videos thank you very much for watching goodbye now

Channel: Paula's Web3 & Crypto

Views: 366,623

Keywords:

Id: WZ15gaHbmHA

Length: 29min 51sec (1791 seconds)

Published: Thu Mar 04 2021