Build Your Own Portfolio Tracker | Crypto, Stocks, & ETFs All In One Place!

this is the best way to track your investments from multiple platforms crypto stocks etfs all in one place a place where you can get a summary of your overall portfolio performance as well as your individual investments however this goes beyond tracking performance it's also about analyzing your portfolio location your dividends or crypto interests as well as a breakdown of your platforms and other insightful statistics let's quickly go through this tracker so you can get a glimpse of all its capabilities and then i'll teach you how to build it step by step so let us begin with the backbone of this tracker the transactions tab is where you enter all the data movement of your investments for demo purposes i've populated this tab with hypothetical transactions using platforms from multiple countries and currencies so you can see how anyone in the world could use this tool to track their investments transactions include options to enter cash deposits buy and sell orders cross-platform transfers cash dividends and reinvestment plans crypto interest and cash withdrawals every movement in one single place that might come handy during investment decisions or our beloved tax audit every investment is then automatically transferred to the portfolio tab here you can get a summary of each of your investments with up-to-date statistics kpis benchmark comparisons actual versus target weights asset class and sector details as we move to the dashboard you can see an overview of your portfolio combined with dynamic charts and a range of screenos there's a table that shows top and worst performers another one with a snapshot of your account value and a dynamic statistics table that changes based on your period of choice on the right side there's an investment screener with classic trendy charts where you can either screen your current investments or potential candidates it gives you relevant information with even a formulated signal that tells you whether it's time to buy sell or hold there's an individual performance screener to check out current performance and analyze buy versus close price orders these colorful pie charts visualize your investment allocation per market value asset class and sector as we scroll down there's an income breakdown section to see any crypto interest earned as well as dividend income both of these are interactive based on the year that you choose from this drop down further down the rabbit hole things get interesting although i think everything is interesting even this dot this is a platform breakdown that separates multiple calculations per platform very useful to reconcile cash balances and to see where you being the most active or being screwed subsequently a deep dive into the crypto world where you get granular statistics on crypto holdings and their allocation per platform finally the entire tracker is coded to be converted to an overall currency of your choice this certifies precision and consistency when investing in multiple currencies for example if you gained 1 000 in bitcoin and 1 000 pounds in the ftse etf your overall gain is not 2 000 it should be approximately 1 700 pounds or 2 300 whatever you choose from this magical cell this tracker offers significant information about your portfolio while storing every investment in one single place if you would like to skip the tutorial and access this ready-to-use tracker in light dark matrix and cyberpunk theme make sure to visit my patreon which is linked in the description of this video as always these include a mobile tab where you can see the same insights in a smartphone interface before we start with the tutorial i'll quickly show you how to enter transactions with a few examples for those who access this tracker from patreon the first thing you'll see is a protected version you need to click file and then make a copy into your private account now you can edit everything and add your own transactions so the first thing to do is select all these transactions and remove them by pressing the backspace or delete key and now we'll just enter a few transactions as an example so the first thing is to enter cash deposits and let's buy a stock in us dollars so we're gonna buy apple and then let's say we want to buy an etf in another country using this platform we also need to add the cash deposit so we're gonna buy the ftse 100 etf and now let's buy some cryptocurrency using binance so i'm going to buy some ethereum here we enter the asset name that they have in coin market cap and let's buy a few more cryptocurrencies here we're buying tara luna and if you don't know the exact asset name to look for just go to this website called coin market cap and search the coin that you're trying to add to your tracker so here's tara luna and as you can see this is the url for this coin so we need to use this name so here in the asset name we enter it like this doesn't matter if it has capital letters or not so we've added several assets here as you can see if we go to the portfolio tab you can see that they've all been added here automatically you can remove these target weights that we entered previously and just enter the target weight that you want and now if we go to the dashboard you can see that everything's been added here as well automatically and you can see the platform breakdown and the crypto breakdown so now i'll show you how to transfer crypto or stocks from one platform to the other so let's say you want to transfer half of your ethereum to another platform so first thing to do is enter binance as a platform because we're transferring it out of here and for action you're going to select transfer send and in quantity just enter the number of assets that you're sending to another platform so let's say we're sending half of the ethereum that we own and then in the second line we enter block fi and here for action you want to select transfer deposit so if we go back to the dashboard you can see here the in the crypto breakdown per platform the 0.5 ethereum in binance and 0.5 ethereum in blockfire so that's just a quick example of how you enter transactions and you can see how everything is automated as soon as you enter the transactions now let's learn how to build this second generation portfolio tracker from the beginning okay so the first thing you're going to do is open up google sheets and we're going gonna start a new spreadsheet so we're gonna open up four more sheets because this track is going to have five sheets in total and we're going to start with the transactions tab so i'm going to enter all the column headings and what you can do is pause the video and enter them exactly where i entered them so i'm going to merge the title and center it what we're going to do is select row 2 and then we're going to wrap the text and center in the middle this way when we make the column smaller the text wraps and we get a better view okay so before i start entering all the formulas here in these columns i'm gonna first enter some dummy transactions so that when we enter the formulas we can see the results straight away so feel free to enter just a few dummy transactions just to be following the same sequence that i do so one little detail to add in the date column is the calendar so what you're going to do is select from row 3 all the way to the bottom click data data validation and the criteria is date so whenever you're entering new transactions you can just double click and you'll be able to see a calendar and as you can see the format is different to the one we have at the top so we're gonna select everything again and in more formats you just select the date format that you want that's much better okay so we're gonna start with our first formula and this is the total column every single formula i'm going to be using in this tutorial will be available in a formula sheet that you can see in the description of this video so you can easily access it and copy and paste the formulas in the exact same cells where i'm doing it just make sure to enter it exactly where i'm entering it because they are linked to specific cells so you don't want to change the location because if not the formula will be broken so as you can see in this formula i'm using a combination of if statements with iferror depending on the action that we select so if it's sell or cash withdrawal we're going to multiply quantity by price and subtract the fees for all other transactions we're just going to multiply quantity by price and he's going to copy that and drag it down all the way to the bottom as you can see even though you dragged it down to the other cells that are empty it becomes empty and that's because we're using the if is blank statement and that's a way to automate the formula so that you don't have to drag them down in the future just before we continue with the other formulas i'm going to quickly create different lists through data validation in asset class sector and action so that you can see the drop down and select what you require so what you want to do with asset classes select everything and we're going to go to data data validation and select list of items and here you're going to enter the list of asset classes that you want so in this case i'm just going to choose the list that i used in the demo and it usually fits everyone's situation so as you can see now we get this drop down option where you can simply choose whatever asset class you're doing in each transaction and it continues further down so now we're going to do the same with the sector feel free to pause the video and copy all of these or if you require any other sectors feel free to add them and finally we do it for action so here i'm entering all the operations make sure to enter these exactly as i enter them because a lot of the formulas they use text that links to this just like the total formula so we need to make sure that it's spelled correctly okay so now we can move on to the rest of the formulas let's work on the rolling costs columns so these formulas that we're going to enter here there to find out the rolling cost of each asset that you buy and it's really cool because it scans all the transactions and creates that rolling amount that we're going to use later in the portfolio tab so to get the rolling cost we use both total cost and rolling quantity to figure out how that cost has been changing whether you buy or sell or buy more again so it's a very interesting formula that shows us the precise amount that we need for the average purchase price so now in the conversion columns we're going to require that magical cell that i told you about which is the currency that you choose first we're going to link this to the dashboard so we're going to name sheet1 dashboard and we're going to go to column l1 and just enter any currency here i'm just going to enter usd just in the meantime and back in transactions what you want to do in cell q2 is just simply link it to dashboard l1 and you can fix that with f4 and then in cell r2 you want to enter equals in front of it and then quotations and then after in quotations again then the end symbol and you're gonna select q2 so now you get a title with the currency that we have and if you change the currency and dashboard to another one say euros the currency changes here and it changes in the title as well and we're gonna do the same with the total column so quotations for total then a bracket with quotations the end symbol select that currency end symbol again quotations and then close that bracket so this formula analyzes the transactions that we have and the currency that we entered okay so this is the first formula to convert the fees so as you can see we're using google finance and selecting the currency that we have in our transactions with the currency that we have in dashboard so a combination of if error and if statements to figure out what conversion to use in this case because we have us dollars in the dashboard selected it's going to select the fee that it is if it is in us dollars and if not it's going to convert it to whatever we chose and then the total column does the same thing there's one little caveat about currency conversions as you can see if i change the currency to euros you would expect the symbol to change to euros but it stays in dollars the reason why this happens is because we formatted the currency as dollars so if by any chance you prefer to have the symbols in another currency besides dollars just select every row that you want and then change it to that symbol that you want but unfortunately it just doesn't change automatically so you have to just choose the symbol that you want and then just stick to that one however the numbers do change so in terms of the actual figure that you see there it will change so you see this one it's five dollars that we were charged in fees in the us dollars but then that was changed to 4.42 euros and then what we're doing in the date breakdown columns is basically capturing the date that we entered and what we're going to do is just break it down by day month year and then select a column that's all i usually do this in a lot of my trackers just to create drop down cells where you can select a specific month or a specific year or even if you want all of them just to scan through different types of information together with a query function okay and there it is those are all the formulas that we require for the transactions section what i'm going to do now is quickly format this tab so that it looks quite similar to the demo basically i'm just going to add a few borders and colors and some conditional formatting that i'll explain soon okay so as you can see i just added a few colors to the column headers and i'll show you the conditional formatting that i used so as you can see it caches in blue how i did this was going to format conditional formatting and i enter this rule if the text is exactly cache then make it blue and then as you can see every time you enter the transaction the whole row becomes blue i use the custom formula for this rule and it just basically says that if cell a3 or any of the ones below is not empty then format it in blue and you must select the range from a to l because those are the ones that you want to format in blue i did something quite similar for action i entered the different texts that i wanted to be formatted in different colors so transfer send in red transfer deposit in green and so on likewise i did something for face so if the value is greater than zero i want it to be red and i use it in this section as well for the totals column i used this format rule so if the column is not empty then make it gray and bold and for all these others i use this rule that if the cell is not empty then you make it light gray a few of them are empty so after that what i did was basically select all of these and i just made them gray because sometimes when they were empty they would just be white instead of gray so i prefer when everything looks consistent one more thing that i did was remove the grid lines as you can see you don't see the grid lines here anymore and in the transaction section i just added some borders at the bottom of each cell that is dotted so i used this one here and one more thing that i would like to do is actually fix these top rows because i like to always see the headers of the columns when i scroll down so what you're going to do is go to view freeze and up to row 2. so now whenever you scroll you can still see the column headers and that is the transactions tab so what we're going to do now is go to another sheet and start working on the portfolio tab the portfolio tab is a massive tab and we're actually going to work on a lot of formulas here first step here is to just continue adding a lot of rows to the right so you want to insert rows all the way to bc because we're going to be using all these columns if you worked on the first generation portfolio tracker you would have noticed that we had a portfolio tab and the currency converter tab in this case i decided to just combine it all in the portfolio tab so basically on the right side of this sheet i'm going to enter the portfolio with a direct numbers and then on the left side i'm going to apply the currency conversion so whenever you access it you're already going to see all the consistent currencies so what i'm going to do is enter 55 different types of column headers on row 2 and what i recommend is just for you to pause the video and slowly enter one by one and then we'll go column by column entering each of the formulas and i'll explain them so you want to enter all these column headers i'm going to expand them so you can read them well so these go all the way to column a a in column a b you're going to enter currency and then what you want to do is select all the ones that you entered except currency copy them and then paste them again next to currency like i said it's basically a copy but one will have a currency conversion and the other one will be direct the one that's direct will tell us which currency it is so what you want to do similar to what we did in transactions is select row 2 and wrap the text in the middle and center it and you can bold it and i like to have it in size 12. we have this main title here you're going to enter equals then quotations close those quotations the and symbol and we're going to go to dashboard and select that currency in cell l1 and then in cell ab1 not ob1 i'm very keen to see that new tv show so yeah in this cell you're just going to enter this title this section of the portfolio tracker will be technically incorrect if you invest in multiple currencies because if you add up all the values or all the costs you'll get wrong numbers that's why we have the currency conversion here on the left but i think it's still worth it to check it out for those people that want to see what is the original cost or the original value based on the currency that you might have so first we're gonna start with the investment portfolio in the original currencies and just before i enter the two formulas that go here i'm gonna enter the tickers because a lot of the formulas that i'll be entering will come back to column a d just for us to be able to automate the formulas uh that formula that i showed you earlier if is blank so for ticker we're going to enter this very simple formula and it's great it's called unique and we're combining it with filter so we're scanning through our transactions and we're just going to exclude all the cash asset names that we entered so as you can see now you get all the tickers of your transactions i like to have all the numbers below as size 11 so i'm going to select everything and change it to 11 straight away okay so back to column a b we're using this formula to scan through the transactions and figure out what currency the ticker that we entered is using as you can see there's a special one for stablecoin because if you're investing with stablecoins uh the currency is obviously the name of the stable coin so that's where we enter it here in this formula i like to drag down formulas with my keyboard to save some time so if you want to learn how to do that i basically enter the first formula i copy it then i go to the column on the right or the left i go all the way to the bottom go back to the original and then press command shift or control shift and the up arrow takes me all the way to my first formula and then you press command or control enter so it drags down everything with a keyboard so it's a good way to save some time to get the asset name for the cryptos that we entered we just use a simple vlookup function and if it can't find anything it's just blank so there are the asset names that we entered for each of the cryptos this is what's gonna help us scan the current price of the cryptos that are not available in google finance and check out the quantity formula it is extremely long i spent quite a lot of time creating this formula but it works perfectly it's a really good super interesting i basically use sumifs with different conditions we're adding up the quantities and subtracting them and we want to get the current quantity that we have if we buy if we sell if we transfer it etc so as you can see i'm entering different conditions here if it's a buy and if it's a stock add it up and then subtract if it's a sell and a stock and then sell an etf etc and the reason why i had to do it so intricately is because we have stable coins and stable coins just make the whole tracker a bit more complicated because we want to be able to get the correct amount when we use stable coins to buy other cryptocurrency so that's why you have to specify which type of asset class it is if by any chance you have an asset that you bought and then sold you'll get a message that says sold all but then if you put it again then it'll just update to the current quantity that it is so it works perfectly fine and yeah it's a really interesting formula luckily you don't have to enter it word by word because you can just copy and paste it from the formula sheet but yeah it's very interesting formula that gives you the quantity to get the average price we just do a vlookup with the rolling cost that we found in the transactions and this is a really cool formula that finds the current price of the ticket that you entered as you can see we're using firstly google finance the normal one that you use to find the price if that gives you an error then we use the second google finance formula and these are for the cryptos that are available in google finance finally if that doesn't work then we use web scraping formula called the import html to scan the asset name that we have in coin market cap and give us the current price that we have in cell ah 2 we're going to enter just 365 and then we're gonna change the format of this so go to more formats and then custom number format and you're gonna enter this custom number format that we have here and apply it so as you can see even though you just enter the number the column header it says day trend and it's dynamic if you want to see the 90 day trend you just enter the number 90 and the column updates to 90 day trend we're going to use a sparkline function to get a nice little bar chart that shows us the price movement based on the number of days they entered here here we use the change percentage formula to get the daily percentage change of the ticker i really like the daily change formula because we basically get that change and then multiply by the quantity that we have so it gives you a real dollar value of how much your value has increased or decreased within one day getting the cost is very easy you just multiply the quantity by the average purchase price and same with the current value you just multiply quantity but this time by the current price to get the unrealized return on investment we're using this formula using the current value and the cost and i call it unrealized because this formula gives us a current quantity so it would be different if you sold something and that's what we have one that's called realized profit and loss for the realized profit and loss formula we're basically scanning through the transactions and adding up the amount that we sold with the dividends and then subtracting the buy amount and the dividend reinvestment and we add up the stablecoin column in case you bought something with stablecoin so that it doesn't count it as a realized gain and then we add up the cost again just a little caveat the realized profit and loss is not really exactly correct ever because we are using the cost that we have and the cost basically changes depending on the quantity and average price that you had it is not the most exact amount but it is a pretty good guide of how much realized gains or losses you might have so those are the columns that give us a bit of performance in kpis now we're going to start working on the benchmarks as you can see we've got here two columns the day return and then the benchmark what you want to do is select a q and ar in row 3 and you're going to merge it and then you're going to click this paint format symbol and then with your keyboard press command shift or control shift and then the down arrow and it basically merges all the cells all the way to the bottom so now we can enter the formula in this merged cell and basically we're looking at how much the price has changed between now and 365 days ago and yes it's a dynamic cell so you can just change this and you can see how the return has changed so now we're going to do the same for the benchmark and that gives us the benchmark return based on the days that you choose in cell aq2 and now we just calculate the difference to see how our ticker has performed versus the benchmark now for the actual weight we're basically looking at the current value of each specific ticker and then dividing it by the sum of all the other values that gives us the weight and for target weight in this case we're going to link it to the target weight that we're going to enter later in column u because as you might remember in the demo we enter the target that we might have manually but we want to see it in the main converted currency portfolio not in the direct one to get the asset class we're just using a vlookup function and then by using the asset class identifier we're adding up all the actual weights per asset class so we can get an asset class actual weight for the asset class target weight basically what we're doing is adding up the target weight that we initially entered manually per the individual asset everything is going to be 0 at the moment because we haven't entered in column u and finally the sector it's again a simple vlookup function with transactions so we can get the sector for each of the assets so that's the direct portfolio with original currencies so this section is going to be basically a copy of the right section but we're going to apply a series of formulas that's going to convert the currencies of each of the columns that currency change is going to be based on the currency of the actual asset that we bought it with and also the currency that we choose here in the dashboard so for asset name we're going to first just link it to the right side of the table so just to asset name in column ac we're gonna do the same for ticker and quantity so these three numbers are gonna be the exact same as the investment portfolio in original currencies okay so for average purchase price we're using this formula here so basically we're scanning first the currency that is being used on the right side of the table so as you can see this one is us dollars if the currency of our asset is the same as the one we have in the dashboard we're just going to leave the average purchase price that we have and if not we then just make a currency conversion between the currency that we have here and the one in dashboard so now we do the same with current price one thing to note for the current price is that when it's a stable coin it won't convert it so for example if i change the dashboard to euros you would expect tether to be 75 cents or whatever the euro is unfortunately it doesn't change because the currency pair that it's using is usdt so the formula is not really capturing us dollars yeah it's just one of the limitations that this tracker has which is that it can't convert stable coins generally you just see them in the price that you bought it for so it's okay for the meantime maybe in the future i'll figure out how to change it to the currency that you choose but for that we need the currency conversion between a stable coin and a legacy currency for the sparkling we're using the same formula but we're just changing the location of the cell so now this one's linked to f2 because here's where we enter the number of days for the day trend we enter the formula but it's currently blank because if you go to cell f2 you can see that it's actually a text so what you want to do is just change it to 365 and then we're going to do the same as what we did in this column here we're going to change the format so that it says day trend after the number that you enter and it should be here in the recently added formats but if not you can just do it from the custom number format and apply the same that we applied previously so there it is and it's fully dynamic if we change it the trend changes for the daily change we're also linking it to the one that we found previously and we're applying the same currency conversion formula in the daily change to calculate the cost we're just multiplying the quantity by the new average purchase price that we found that it's converted to that currency and same with the current value all these formulas are exactly the same as the ones on the right but we're just changing the location of each of the cells and one more currency conversion for the realized profit and loss now here we are in the target weight column what i'm going to do first is actually change this title i'm going to change it to this formula so i'm using quotations to enter the title and then a formula in between to capture what percentage we actually have entered in the target weight so as you can see it's currently zero and if i start adding any percentage the title adds it up so you can see what's your total target weight so feel free to enter the target weight though you already have for your transactions or if you have dummy transactions just enter a few and then later when you add your own transactions just enter the target weight that you want for each of the assets at the moment i'm just going to enter the ones that i entered in the demo for the asset class we're going to link it to the asset class that we found on the right side and we use this formula to find the asset class actual weight and finally the sector we link it again to the sector on the right side and there it is that's the portfolio tab and as you can see we had some blank spaces when we worked in the original currencies section and now because we have this one linked to the target weight where we enter it manually all the other actual weight and target weights filled up so there it is that is the portfolio tab completed i will now format this sheet with borders and different colors and conditional formatting and when i'm back i'm going to quickly show you how i did the conditional formatting okay so i'm back and this is the formatted version of the portfolio sheet as you can see i just added some colors and borders to the column headers and you can see there's some conditional formatting in each of the columns this one's very simple so if you select the columns that you want to be formatted and then you go to format conditional formatting you can see that we just add a simple format rule which is if the value is greater than zero you make it green and if the value is less than zero you make it red so i just uh selected several columns to apply this to so the daily change the realized and unrealized gains the profit and loss sections the return on investment the differences and for this one here the target weight i just applied this rule so if the cell is not empty make it blue that way when you enter anything here it just turns blue so that you can see first of all that it's a manual entry column and also you can just guide yourself where you're missing a number one more thing that i did was group the columns here so this one is the original currencies table right and we don't really need it that much because this is the main one where you can see everything converted to that single currency i just leave this one here because first of all it is needed for the left side table but also yeah maybe you want to expand it sometimes and see what was the actual average purchase price in the original currency what you have to do is just simply select the columns that you want to group you right click and here you select group columns or ungroup them and then what you're going to get is this little symbol here where you can just click the plus or minus sign to group them or ungroup them it's very handy to open up new sections that you have hidden i'm gonna freeze the first two rows so that way when we scroll down we always see the column headers and that is pretty much it for the portfolio tab so now we can move on and work on the dashboard but before i do that i'm going to create a filtered portfolio tab this tab is just going to be an exact copy of the portfolio tab but we're going to add a filter across the whole table and that way you can play with the filters and sort them the problem is that we can't do it in the original portfolio tab because it will work when you first do it but if you ever add new transactions it just messes up with all the formulas and everything just gets very messy so it is best to just make a copy of it into the filtered portfolio and here you can then play around with the filters so it's very simple you just link it to the first cell so this one here on the left a1 we have up until aa i don't really want to include the other one i just want this main one so what you're going to do is drag this all the way to aa we need to add a few more there until aa and now that you have dragged it all the way there you can just drag it down up to say row 150 so now we get a copy of everything that we have in the portfolio to format it real quick you just select everything here click the paint format symbol go to filtered portfolio and click this cell here between a and row one and everything just formats to the way you had it in portfolio it added the group of columns but we don't really have anything here so what you can do is just select all these and then just delete them and now we just have the main portfolio it is an exact same copy so if i go to portfolio and i make a change here you will be able to see in the filtered portfolio there now all you have to do is select the whole table from row 2 all the way to the bottom and click the filter symbol we're going to create a filter then you can size the columns again and now that we have resized the columns you can see that the filter is applied and now you can play around with the filters so usually i like to see the current value from highest to lowest so you just click this little symbol here so you want to first deselect the blank spaces click ok and then sort z to a now we got the assets with the highest current value in the top and it goes to the lowest one one thing to note is that if you add more assets in the future as you add more they won't appear here straight away what you need to do is just select this little symbol and then click ok and you'll see how the asset that you just added will add up to the filtered portfolio it's just the way to refresh the filtered portfolio tab this is the way to do it so yeah it's a very useful tab here just to copy the portfolio and you can play around with the filters to look at the view that you want now we're going to continue with the dashboard so there are quite a lot of tables in the dashboard and several charts and graphs and screeners so we're just going to do it in step by step one by one i'll explain each one of them and will be done very soon so i usually leave the column a as a space and i start working in column b so the first thing that we're going to do is enter a title in cell k1 you're just going to enter currency and we want to see a drop down of all the currencies that we're going to use so to do this you want to go to data data validation and list here the currencies that you want to see in the drop down so i'm just going to enter a few of the main ones and as you can see now there's a drop down button where you can choose any currency and you can easily change it to what you want so if i choose australian dollars and we go to portfolio you can see that now the investment portfolio is in australian dollars and we can see all the numbers converted to australian dollars okay so let's start on the table that shows us the overall portfolio snapshot so first you want to select cells b3 and c3 and merge them and we're gonna format this all the way to row six and here's we're going to enter the titles of our table and we have to center them and looks like we're gonna have to center everything so what you wanna do is select everything and just center everything in the middle so again we're gonna merge d and e in row three and row four these four here we're gonna leave them individually because we're gonna have different values here to get the portfolio market value we're going to enter this formula so we're going to add column j and portfolio tab so if we go to column j you can see that this one's the current value so it's just adding up all the numbers to show us what's the current size of our portfolio based on the market value of today for the total portfolio return we're going to use the same formula but now we're going to select column n which is the total profit or loss to look at the current return that we have we're going to add up column l in the portfolio tab so this one is the unrealized profit and loss i call it the current one because this column only shows for the assets that are active so if you sold a few assets before this one will just be empty so remember this is just for the current one and the total portfolio return is different so you can see the difference here we use then this formula to get the return so it's just the normal return on investment formula but we just do it with the columns in the portfolio and to get the daily portfolio change we're adding up column h in the portfolio so the one that gives us the change and to get the percentage change we just divide the daily portfolio change by current portfolio change okay so now we're gonna merge cell b8 and c8 as well as d8 and e8 and then we can just copy them and paste them in line 10. and in cell b9 we're going to use a combination of offset match and max functions to get the top performer of today so as you can see this is looking at the tickers in portfolio column g so the one with the largest daily change will be the top performer and then we just use the max formula to get the percentage to get the top performer overall we're using the same formula that we used in line nine but in this case we're using column k which is the unrealized return on investment okay so in row 13 we're going to work on the account balances table so we're gonna merge b13 and c13 then paint format and drag it all the way to row 19. so the account value is basically the positions plus the cache so currently it's going to be zero but now we're going to fill up these two first we're adding all the positions with a market value and then we use a combination of sumif formulas to calculate the cash so we're adding up the cash deposits plus the sales plus the dividends minus by minus cash withdrawals and then we add up stable coins that i buy and finally the cagr or compounded annual growth rate so we're basically using the cagr formula which is the market value divided by the cost and then to the power of one divided by the number of years that you've been trading since the first transaction you could use a whole number for the years but i like to use the year frac function this one basically creates a year into a decimal so it's a bit more precise and even though it's not annual it works really well for when you've been trading for maybe just a couple years or one and a half year so feel free to change this formula to a whole year if you've been trading for say four to five years and you would like to see that compounded annual growth rate for those whole years and now we're going to work on the statistics table so again we merge b and c paint format and drag it down to row 35 okay so this whole table is quite dynamic and it changes based on the dates that we choose so first we're going to select d22 and d23 go to data data validation and select date so we're just going to choose the start date and for the end date i'm just going to write down a formula today so that we get the latest date but if you want to choose the end date manually you can just delete that formula and then just choose it from the calendar to whatever you want so basically all the formulas that we're going to be using on this table uses sumifs well most of them use sumifs but something very important is that we're going to capture the dates from the transactions in column c because that's where we enter the dates and we want them to be within the dates that we select so in these two cases we don't use sumifs but we just use countifs because we want to see the number of transactions instead of a monetary amount and this one here i call it the approximate dividend yield because it's not really exact as you can see we're dividing the total dividend income by the portfolio market value and technically it's not exactly precise because this is the total market value of our portfolio but not all our stocks provide dividends so that's why i call it approximate dividend yield so if you really want to see the exact dividend yield of your portfolio make sure to check out the dividend portfolio tracker okay so now we're going to move to the right hand side and work on the screener so you want to merge cell n2 and n3 and then we're going to use the paint format and drag it all the way to column t and actually we're merging n and o and then you're going to select n4 and o4 and we're going to merge those two as well paint format and drag them down to row 17. so in cell p3 or p2 and 3 you want to go to data data validation and then we're going to select the range so what you want to do is go to your portfolio and select the ticker column remove the row so that it selects everything click ok and now we basically get a drop down with all our investments so it's a cool screen of what you currently own in the other four rows i'm not adding any data validation i'm just going to leave them open so that you can enter whichever ticker you want that you might not own in your portfolio and you can screen them very easily and now we're going to be entering several formulas that use google finance and the specific attributes so in this case it's name just for each of these rows so that we can get the information for the signal we're using a formula that basically scans through the 52-week high and lows of the specific ticker and also it looks at the current price and basically what the formula is saying is that we wanted to signal a buy when the difference between the 52-week high and low is 50 or less than the 52-week low and then the other if statement is that we want it to be a cell when the difference between the 52-week high and low is 85 or higher than the 52-week high and then if neither of these conditions match then it's a hold so now what you can do is select all these formulas that we just entered and then just drag them across to column t and then to finish off this screener we're going to enter a sparkline function so this is a really cool function that gives us you know the classic trendy chart but the interesting thing about this is that i included if statements so if the price has gone down after 365 days it'll be red and if the price is higher than 365 days before then it'll be green and this trendy chart is actually dynamic so if i change this to 10 days we can see how the chart now is red because the price of today is lower than the price 10 days ago so now it's red and actually it says 365 we're gonna change the format of this to this one here and now you can just drag these across to column t one more little caveat about the formula for the signal feel free to change the sell and the buy factors if your investment strategy is different you might have more risk or lower risk so you can play around with these percentages with 50 and 85 percent to see what best fits your strategy but i just use these numbers as a neutral strategy okay now we're going to work on the individual performance screener so we want to get a drop down here as well but what we're going to do is just copy this cell p2 and 3 and then paste it here and we're going to unmerge it and you can just remove this one so now we still get the same data validation but then in a different cell and in this table we're going to use the query function to look up the value that we want based on the ticker that we chose it's scanning column a and then we're selecting column i where column b contains this sticker and that gives us a cost now we do the same for the rest of the values but we're just selecting different columns so this is a nice performance screener to have in the dashboard because it saves us time from going to the portfolio tab and looking up the ticker that we want you can just select the ticker that you want here and you'll be able to see your current performance now we're going to move across to cell w20 and what we're going to do here is get the dates that we have bought this asset then we're going to get the buy price that we got it for and then we're going to use the google finance function to get the close price based on that date where we bought it and then we're going to use this information to create the graph later so we're using this really cool formula called filter and basically we're scanning for the transaction dates that we currently have in column c and then we want this filter to choose the dates that have the ticker based on the ticker that we've entered in column d as well as the transactions that have been in buy only because we want to get the dates when we actually bought the specific asset we then use the same formula but now we select column j which is the price column and this will give us the buy price and finally we use this really cool formula we're using query combined with google finance and as you know google finance can give us a list of all the historical prices and it can be the current price or the highest or the lowest even the volume in this case we're focusing on the close price and then we're just selecting one single price based on the date that we choose here so now we're just going to drag it down and we'll probably drag it down until cell 100 so what you can do select all the rows after that and then just delete them and then you can drag down this formula we're going to work on all the charts once we finish all the tables so we want to do is scroll down to cell n34 and we're going to work on the income breakdown tables okay so here we're going to create a year screener so in cell o36 we want to go to data data validation and then select a range so we're going to go to transactions and you want to select column v from v3 all the way to the bottom so that gives us the years that we've entered transactions you might get an invalid error message to fix this you just need to change it to plain text so to get the list of months based on the year that we choose we're going to use a combination of unique and query functions and we're basically selecting column u which is the months where column v contains the year so in transactions we enter the breakdown of the month and the year and that's what that formula is doing it's selecting the months based on the year that you choose so if you choose 2020 it will show you these months august september october and december so then we use two sumifs functions to get the cash dividends and then the dividend reinvestment and then you can just drag this formula down to cell 48 okay so that's the dividend income breakdown now we're gonna add a small table which will show us the crypto interest that we've earned so again we use the combination of unique and filter and we're gonna use several filter conditions so we want to get the crypto and stable coins for those that have received crypto interest and then to get the actual quantity of interest received we just use the sumifs formula that scans through the transactions based on them being crypto interest in 2021 and the asset so we can drag this formula down all the way to row 49. so as you can see if i change this to the year 2020 we didn't earn any interest in 2020 but it is still showing the total crypto interest that we received since we started entering transactions so it's pretty useful if you want to see those total numbers and that is the income breakdown now we're going to move again to the left side and we're going to scroll down to row 48 where we're going to enter the platform breakdown so you want to enter the following headers but we're actually going to select from net deposit all the way to total dividend income and we're going to move it one cell to the right because we're going to merge cash withdrawals so we're going to use the sort and unique functions to get all the platforms that we entered in transactions column a and then for each of these headers we're going to use different formulas to scan through the transactions of the portfolio tab so that we can get all the statistics so you want to drag down all the formulas to row 62. [Music] okay and there it is as you can see we used again the sum is formulas you've seen it before we're just scanning through the transactions and we use specific conditions so in this case we're selecting the platform as a condition just to get the information that we want and then we just add up all the totals okay now we're going to scroll down and work on the portfolio per cryptocurrency so you want to go to cell b64 and enter the portfolio per cryptocurrency so in this table we're gonna get a list of all the cryptocurrencies and stable coins that we have and as you can see these are not tickers these are the asset names that we entered and if we go to the portfolio you can see that the asset names are here and in some cases they're repeated so we got bitcoin and us dollars bitcoin and canadian dollars same with cardano we got one in stablecoin and the other one in us dollars the purpose of this table is to add up everything per the cryptocurrency that we have so that we can see the total numbers so it's quite interesting to see how much total bitcoin you might have whether it's being bought in stablecoin or in several different currencies so it's a pretty good table that summarizes everything for that cryptocurrency and there are no more tables underneath so we can just drag down the formulas oh we actually forgot to merge these two and in row 64 we get the totals only the return on investment has a different formula which gets the total current value and the total cost you might be asking yourself why are we getting the performance indicators for cryptocurrencies but not for the platforms so if you remember in the first generation i would get the platforms and then each of the performance indicators so you could see what is the current value of all the assets that you have per platform the reason why i didn't add it in this tracker is because technically how i did it in the first generation tracker wasn't correct because a lot of the times you might buy for example apple using robinhood but then in the future what if you buy it using interactive brokers or whichever other platform for me to get the platform per ticker i was just using a vlookup or index match one of those and unfortunately it doesn't capture both of the platforms that you were using i could capture the latest one where you bought it but for example if you buy apple in two platforms and you keep apple in those two platforms the formulas using index match or vlookup wouldn't be able to show those two platforms so that's why i decided to exclude the performance indicators for the platform breakdown and instead i just used several indicators that we can get from the transactions very easily it is different with the cryptocurrencies because these here they're basically just the asset names that we choose from the cryptos so it doesn't matter if you're transferring one or another or selling there is still an asset name and we can add them up from the portfolio tab because they don't change even if you buy bitcoin us dollars from one platform or another the asset name is still bitcoin that's why we're able to get them here i'm hoping for the third generation tracker i'm gonna be able to provide more performance indicators per platform but i want to make sure it's correct and precise and at the moment i haven't managed to do it but trust me i will get it done in the future finally we're going to work on the last table and this one is the crypto breakdown per platform and this is a really cool table that's fully dynamic you don't need to enter any column or row headers so as you can see we're using the transpose unique and filter combination of functions to get the platforms where we're buying crypto or stable coins so if in the future you buy using another platform it'll show up here automatically and same with the list of cryptocurrencies that you bought so here we're using this very long formula not as long as the other one that i showed in portfolio but it's still quite long again we use a combination of sumifs functions just to add and subtract different values that we get from transactions so that we can get the final quantity that we have in the specific platform so with this one you can just drag it to the right and then all the way down if by any chance in the future you have more platforms feel free to just keep dragging the formula until all your platforms and cryptocurrencies fit wow so that's a lot of information looks kind of messy right now because we haven't formatted so what i'm going to do now is just quickly format everything add some borders and colors and conditional formatting and i'll be back to explain what i did okay so here it is looks way better now i'm just going to delete this sheet we don't need it and i'm also going to fix row one that way you can scroll down and still see the title the currency drop down and the trendy charts because who doesn't want to see trendy charts all the time we are just missing the charts so now we've got the space ready for them and we're going to start with them before i start with them let me just quickly tell you the formatting that i created so besides the borders and the background colors i added some conditional formatting and it's quite similar to the portfolio and transactions one for many of the values if the value is greater than zero make it green if it's less than zero make it red i also created some conditional formatting for the signals row so make it green if it says buy red if it says cell and yellow if it says hold i almost said huddle i added a color scale in the dividend income so the largest values are shown in a dark blue and the lowest values are shown in a light blue one more format rule that i added to these three tables at the bottom is the cell is not empty so i selected these cells and these cells and these cells and these cells and then i just entered this rule if the cell is not empty then make the background blue so that way whenever you add something else here it'll change to blue so it's so quite a dynamic and automatic table okay we're almost done let's start with the first chart so you want to go to insert chart and we want a column chart with a data range from portfolio and let's just select everything all the way to aa remove those numbers so that we select everything and click ok just going to remove all the series use row 2 as headers for the x axis we want the ticker in the series we want to get the unrealized return on investment so now we get a nice column chart that tells us the percentage gain or loss of each of our investments for the second chart we want a combo chart so this one here and we're going to select the same data range that we selected for the first chart in the x-axis we want the ticker in the series we want the current value and the cost for the third chart we're going to select a pie chart and we're going to use the same data range so we want the label to be the ticker and the value to be the current value also known as market value the fourth chart is going to be a pie chart as well with the same data range we're using the same one for all of them it's a very useful table this one so in the label you want to select asset class and in the value the current value looks very messy so you just want to click aggregate so we get the unique values okay another pie chart and we're using a different data range gonna go to the account balances and select the positions and the cache for the label we want b14 and b15 and for the value we want d14 and d15 so this one just shows us a pie chart of the cache versus the positions and one more pie chart that will visualize the sector breakdown so we're selecting the portfolio tab as the data range again and for the label we want the sector and the value we want the current value again we're going to aggregate the sector okay i think that's enough pie charts for now okay so we've got six charts let's work on the seventh chart which will be the individual performance screener we're going to select a line chart and the data range is going to be w to y and then just drag down all the way to the bottom we're going to select the date for the x-axis and then the series are going to be buy price and close price [Music] so this chart will be fully dynamic based on the ticket that we choose and the amount of buys that we've had together with the buy price and the close price okay let's work on the dividend income chart this should be the last one so we're going to select an area chart and for the data range we want to select q and r all the way to row 48 x-axis months series dividend income wow we are done let's just remove the grid lines and you can hide these columns when you hide them you'll get this message make sure to click include data so that this chart still includes the data from those rows even though we hit them and that is it that is the portfolio tracker second generation so i hope you enjoyed this video and find this tracker very helpful for you to track your investments please send me feedback let me know what you think and we'll start preparing the third generation with better improvements looking forward to the next one bye bye this is planet finance thanks for joining me until next time happy learning

Channel: Planet Finance

Views: 67,897

Keywords: portfolio tracker, portfolio, tracker, planet finance, planet, finance, investing, crypto, stocks, ETFs, google sheets, google, sheets, dividends, platform

Id: xIDRIOqAldc

Length: 56min 0sec (3360 seconds)

Published: Sat Feb 26 2022