Google Sheets Master Portfolio Tracker | Dark Theme

what is going on guys we are back at it with another master portfolio tracker i i honestly thought my last one was the best that it could get but that one this blows it out of the water compared to my old one this is the dark theme portfolio tracker everyone turns on dark theme for all of their brokerage accounts so i figured why not make a portfolio tracker in google sheets that is also dark themed now there are a lot of updates in this portfolio tracker you could easily see just from the dashboard not only do we have all the major indexes we have the volatility index we have their lines their uh line charts we have this wasn't the easiest thing to figure out but we have my biggest gainer and biggest loser for the day you can see that oh look there's ethereum we got ethereum and bitcoin in this we have right here market closed when the market is open this will say market open and guys i think the coolest part of this all because i have a lot of international traders and you guys all rock we now have a conversion chart so we could convert all of our currency which will change all the currency on the dashboard everything's dynamic here so if you are in europe and you're buying some u.s stocks you need it convert it over to your currency so then you can see exactly what it's worth in your money so you don't have to try to figure out what it is manually again everything is automatic everything updates i'm going to walk you through step by step exactly how to make this down to a t it is going to be a long video it is going to be very involved we have some code that we need to make in the script editor oh yeah guys and another thing this counts this also counts splits let's see i owned apple where is it look at this split when apple had its four to one split i've i've owned apple since 2017 all the way back up here this says 2019 but that's because i transferred between brokerage accounts so i actually bought this back in 2017 for a dollar course average of 159 but it had a four to one split so instead of going back and having to change all of my entries the code will actually update that as well so you can see that the split is in there and you see right here now my purchase price is down to 91 the price is 134 everything is accounted for guys now before we dive into this video because it is going to be very long if you don't have the time to build this out you just rather have it right away it is in my patreon you guys can check out my patreon every single portfolio tracker i make is uploaded my patreon so you guys can download it for yourself and guys before we jump onto this video please smash that like button and subscribe if you haven't already and drop those comments if you have any questions or if you have any ideas that you could that we could add to this throw that in the comments so the youtube algo could boost this so everyone can see this because i don't want this information being hidden beneath a bunch of other portfolio tracker videos because there's some awesome stuff in here guys all right guys just to show you how this works so let's add a date let's say i bought some more apple okay so i'm gonna say apple put a price in of well quantity we'll say i bought let's let's make it big we'll say i bought a hundred shares worth of apple at two hundred dollars so that's gonna update it's gonna be twenty thousand dollars this is just so you see how everything's dynamic if we go to positions you can see here's apple right here oh well i guess 200 would be too much because it's current price is 134 so instead of uh 200 it will say 100. so do that 100 so there we go you can see right here this updates now my purchase is 97 i have 135 shares now instead of only 35 and if we go to the dashboard you can see apple is now really high you can see that apple is the biggest position that i have you could also see that this updated look at this my cash value is down to almost nothing so you see right there my cash value is only five hundred dollars left over so everything is completely dynamic here guys and just to show you how the conversion works let's throw in a foreign stock here so i'm going to delete all of this and we're going to put in how about fra vow that's volkswagen we'll say buy we'll say about 10 shares for 250 bucks so if we do that now i bought it for 250 we could see right here euros okay so even though i bought it for a total of 2500 right here converts it to usds so this would be 2 500 euros i'm buying but it's showing me how much i bought in dollars which would be 2987 because the euro is a little bit more than a dollar if we go over to conversion now and you can see all of this is usd except right here here's our euro we go up here we could change it to euro so now it's going to change everything over to euros you can even see up here look purchase in euros purchase in uh price and euros change in euros go back to history it would change this so if we're in euros and i only put 2 500 in euros now you can see this is different and then all of the stuff in the u.s dollar changed so it flip-flopped and then if we go to the dashboard all of this is also in euros now as well so this would be a great way for you to track all of your stocks especially if you have uh international stocks this is a great way to track them in your own currency so we'll go back and change this to usd and then everything will update and whatever whatever you want to do whatever type of currency you're using this will work for it alright guys so the very first thing we need to do here is write out the code because the code is lengthy and what code are we writing out well it is where it transfers all of the data in history it'll spit it out into the positions tab right here this is a custom function the my positions function we have to create this function it will then spit out all the stocks we have with their current share price and their current purchase price it'll account for shares that you have sold and then re-bought all of it will be dynamic all of it will update that is the first thing we need to do is open up google sheets brand new google sheets which is not excel so many people have google sheets and excel confused sheets is something on google uh you go to the little three dots the google apps and you look for sheets click on that and then you click new luckily this is free it's not like you have to pay like excel this is all completely free to use which is great so this is what we're going to want to do we're going to want to create two tabs the first one is going to be history and then the second one is going to be positions we're going to have more tabs like the dashboard and the currency conversion later but let's jump over to history and this is like my old portfolio tracker for those of you who have watched that one before we're going to do transactions we're going to do date security action quantity and price so far everything is looking very similar to the old tracker but this uh we're about to do the code the code is not nothing like the old tracker it's a lot more advanced there's a lot more moving parts to it just gotta follow along the video and try the best that you possibly can with getting everything right no syntax errors or any of that but trust me guys you could do it it is possible don't get discouraged with the length all right you just got to follow it nice and easily what i'm actually doing here which i want you guys to do is we want to make sure we have everything right with the code and how we can do that well we have to debug the code or at least once the code is up and running we want to make sure everything is working how it's supposed to so what i'm doing here is just kind of having some uh fake trades so before you enter your entire trade history into this i'd recommend you do something like this if you want to copy exactly what i'm doing this is just we're going to be testing the code with this and then after we know that the code works we then put in our actual stuff drip and we got buy dividend and by jump over to quantity we got we're going to say one ten five two here's for the split two colon one so we're pretending it's a two-for-one split let me just fill out the rest of this and when the code is done i'll kind of explain exactly why we're using this this is just kind of testing everything that we have in the code which would be [Music] not only putting deposit of money in buying seeing how the split works seeing how selling works then rebuying that cell and making sure that both the drip and the dividend works as well all right so if you guys want to pause the video and just kind of copy what i have right here so then you could test your code once we have it complete do that now but we're going to jump over the code because this is going to be a decent chunk of the video but i'm going to try to make it as quickly as possible so you're going to go to tools and you want to go to script editor right here just going to click on that it's going to open up something that looks like this so you could name this whatever you want so you go up here we can name this i'm going to name it think stocks function or i guess it would be more of a code because they're going to be multiple functions so think stocks code so we'll rename that you can even go as far as renaming this if you want so if you click on this you can rename code.gs to whatever else we're just going to keep it as code.gs i'm actually going to delete all of that and we're going to start with function make sure you have functions spelt right so function generate trade open parentheses stock quant comma stock price comma action go on the outside of those parentheses hit the squiggly bracket hit enter do return i'm going to do space a second squiggly bracket hit enter we're going to have shares colon stock quant comma price colon stock price comma action colon action and then we want to drop down one and hit the semicolon and that is our first function pretty easy only seven lines uh if you want to pause the video make sure everything is spelt right here and by everything i mean all the syntax needs to be exactly the same uh function should be blue you don't want to have a capital f it's case sensitive so see how mine's lower lowercase return is also lowercase but it's also blue and there's no squiggly lines underneath if you have something spelt wrong like say function right if i'm missing the c you see how that has the red squiggly line that means i have something wrong so the code will try to help out with what's wrong but it's about to get a little bit more complex and the code may not catch what you're doing wrong but this is the first function we are done with it nice and easy the next function is the monster i broke it down into eight separate parts to make it easier to follow uh so we're just gonna take it one section at a time but here we go i'm gonna go down to line 10. well you know what we'll go down even further we'll go down to line 20 just to really like separate everything and we're going to do three lines the first part is going to be three lines so it's going to be function because we're creating a new function so we've got function generate fifo so we want to make sure the f is capitalized and on top of that this is going to be the fifo function so if you guys watched my other video my other master portfolio tracker that was an hour and six minutes long some of you have realized there was a problem when it came to selling a stock then buying that same stock later on it averaged the price from that previous buy that you've already sold and that's wrong you don't want that that's because you want something that is first in first out so if you were to sell a stock say you have a stock for let's say and don't don't write this this is just me showing you the example you have a stock you buy apple right one share of apple for a hundred dollars then you sell one share of apples for say 150 okay so this is a buy and then this is a cell in case for you non-coders are wondering what these slashes are these slashes means that i don't want this to be a code i want to write something so i can keep these slashes in here and the code won't read this so fun little tip but we buy one stock then well we buy one apple we sell one apple and then let's say a couple months later we want to buy apple again so we buy one share of apple again this time it's for 200. so in our portfolio it should say we own one share of apple with our dollar cost average of 200 bucks the problem with the old code is it would account for this old apple up here so instead our dollar cost average would be 150 because 200 and 100 you get an average of 150 so that's wrong so what generate fifo does this fifo code that we're making it will do first in first out so it will know okay if we sold apple then we just want to forget these entries we want to delete them and then it'll actually have the right one so that's what this function is doing so here we go we got function generate fifo security action quantity price squiggly bracket hit enter do let portfolio equal new maps we want to create a map open parentheses close parentheses hit the semicolon hit enter then we want 4 var i not 1 i equals 0 semicolon space i again less than security make sure security spell right dot length make sure length is spelled right semicolon i plus plus again for you non-coders out there is a very very popular function that a lot of coders will use unless we want to open another squiggly bracket and hit enter so this is our first section is done out of this long function this generate fifo is going to be eight parts this is part one done if you wanna pause the video make sure everything is right do that now okay i'm gonna move on to the second part of this function but one thing that i want you guys to see is this squiggly line is a little too indented you could delete them and then make one there hit enter and then make another one just so it's that's what you want you see how this one is in line with this and this one's indented that's kind of what we want here well that's exactly what we want uh what i had would kind of mess us up down the road so now that we have that we're going to move on to the next portion we're going to hit enter a couple of times and it's going to be 5 let's so these are going to be let formulas so we got let ticker equal security i dot to string open and close parentheses semicolon hit enter then we got our next let action equal actions i dot to string again and guys when this pops up right here if you just hit enter it will autofill it for you it's also a good way to prevent spelling errors down the line now we're moving on to our third let stock quant equals number quantity square bracket i another semicolon hit enter our fourth let stock price equals number open parentheses price open square bracket i and then we want to be on the outside of our parentheses semicolon hit enter our last let let trade equal generate trade this is how we're bringing our first function into our second function so open parenthesis stock quant stock price action and semicolon then we can hit enter again and we'll hit it a couple of times actually we'll hit it a lot of times so we can move everything down so this is the next part pause the video make sure that this is completely right you don't have any kind of spelling errors or syntax errors make sure all the let is two lines over you see how we have this line and then we have this line so the let is all right here which is what we want and once that is good now what you want to do is go back up to the squiggly line and you're going to hit enter from there if we were down here by 47 or if we just click randomly you see how now we're like here and if we start typing this it breaks that line we don't want that that could mess us up so go to the squiggly line then hit enter not squiggly the uh semicolon you guys know what i meant hit enter a couple of times so now we're properly indented and now we are moving on to part three of the fifo function so we want if action equals equals by make sure your bi your b is capitalized on the outside of that and hit these two lines where do you find these on the keyboard it's to the right of your squiggly bracket and uh square bracket if you have a similar keyboard to mine then we want action dot 2 upper case open and close parenthesis equals equals then we want drip open squiggly bracket hit enter let active trades make sure everything spelt right equal portfolio dot get ticker semicolon hit enter now we're doing if so if active trades equals equals null go on the outside of our parentheses do another squiggly bracket hit enter portfolio dot set open parentheses ticker comma square bracket in the square bracket we're going to write trade on the outside do another squiggly and not squiggly a semicolon we're going to hit the down arrow then i'm going to hit space do else hit space then open the squiggly bracket then hit enter and then the last one for this part is going to be active trades dot push open parentheses trade go on the outside and hit that semicolon and guys that is part three of the fifo function we have five more parts see it's not that bad this is pretty easy again pause make sure everything is spelt right make sure everything looks the same as mine so we're not going to run into any problems when we are going to execute the function all right so now that we know all that is done we're going to hit enter i'm going to actually hit it a couple more times because this is going to be our split function so what do we have up here well if you notice we're tying in the generate trade function so this function is tied into this now we're kind of defining everything here and how we want our function to work like you can notice security actions quantity and price is what we see on the spreadsheet security action quantity price and we're saying hey if the action is buy or drip this is what we want it to do so now i'm going to say hey if the action is split we're going to want to account for that so this is what we want if open parentheses action dot 2 upper case open and close parentheses equal equal split and notice how split is all capitalized that's what you want go on the outside of that parenthesis we want another squiggly bracket hit enter let active trades equal portfolio dot get open parentheses ticker go on the outside and then we want to hit our semicolon hit enter let ratio equal quantity square bracket i on the outside dot 2 string open and close parenthesis dot split open parentheses we want double quotation colon go on the outside semicolon hit enter now this is line four we want active trades equals active trades dot map now this is going to be a little confusing so make sure you follow along we're going to do two parentheses that we're going to open we're going to write trade then we're going to go on the outside of one parentheses but not both hit space then we're going to do equals greater than sign then we're going to hit the open squiggly bracket hit enter and now we're going to want to do four trades we're going to have trade.share trade.price trade.share and trade.price and it's going to be multiplying or dividing depending on our ratio you'll see what i mean in a second so we want trade dot shares equals trade dot shares multiplied by ratio square bracket zero going the outside of the square bracket we're going to hit semicolon hit enter then we want trade dot price equals trade dot price then we want to divide that by ratio square bracket zero on the outside and then we want semicolon so that's two of the four now we want trade dot shares again equals trade dot shares multiply that by ratio square bracket one go on the outside and hit our semicolon and then the last trade dot price equals trade dot price divided by ratio square bracket one go on the outside semicolon don't hit enter instead hit the down arrow so now we're on this one right after the parentheses and then we want semicolon so you hit that then you can go down and then you can hit enter a couple of times so now time to pause again make sure this is completely right and that is look at that one two three four we are halfway done with this hard function which is turning out to really not be that hard when we break it up like this all right so now the next part i think this is the hardest part and this is the sell action so what do we have right now we have our buy and we have drip which is pretty easy and straightforward we have our split which is a little complicated here but now our last action is going to be cell and the cell has a lot of parts and we're actually going to break it down into three separate parts but this first part is the hardest so make sure to follow along and have everything right i'm on line 54 right after my squiggly bracket then i'm going to hit enter a couple of times and now we're going to do if action equals equals cell then you want to be on the outside of that parenthesis hit squiggly bracket hit enter let space active trades equal portfolio dot get going the outside we want that semicolon hit enter let precision make sure precision is spelt right equal 5 semicolon hit enter if open parenthesis active trades exclamation point equals that stands for if it doesn't equal null go on the outside hit our squiggly brackets hit enter then we want let so it's saying if our trades don't equal null if our active trades don't equal null let shares to sell equal number open parentheses number open parentheses trade dot shares go on the outside of the first parentheses but not the second dot 2 fixed open parenthesis again precision go on the outside of our parentheses hit our semicolon hit enter while space shares to sell is greater than zero then we want another squiggly bracket hit enter shares to sell equals number number again and we want shares to sell dot 2 fixed open parentheses oh you know what i just noticed we didn't go on the outside of that parentheses so when we have shares to sell go on the outside of that parentheses then we want the two fixed then we want to open parentheses and do precision make sure it's spelt right then we go on outside of both those parentheses hit that comma not comma hit that uh semicolon hit enter now we want if active trades dot length is greater than zero go on the outside do another squiggly bracket we're gonna hit enter let item to sell equal active trades square bracket zero not o not nine there we go zero semicolon enter item to sell dot shares equals number then number again and open our parentheses we want item to sell dot shares going outside of one parentheses dot two fixed open our parentheses precision do our semicolon hit enter if item to sell i swear guys we're almost done with this one shares equals equals not plus plus equals equals shares to sell squiggly bracket hit enter shares to cell equals zero semicolon hit enter active trades dot splice one comma not period comma zero one comma zero semicolon thank god that is over that is now what part five one two three four five guys i think we're more than halfway done here this was the hardest one if you stuck through the video watching that then the rush should be a cakewalk honestly this is the hardest one right here just cause there's a lot of repetition you might miss something so definitely pause the video definitely check and definitely smash that like button and subscribe if you haven't already guys all right guys now it's time for the easy stuff um make sure you're on the outside of this squiggly bracket and once we're done with the code we don't have to go back to this ever again it's one and done then we can move on to the fun stuff and making our portfolio tracker look nice hit enter we'll do it a couple of times just to space out kind of get rid of that uh that code up there then what we want to do is else if item to sell dot shares is less than shares to sell guys if you haven't noticed by now i mean it's kind of like straightforward if you look at if you read this and try to read it like in english what we're actually saying here is hey if our active trade is null and this is what we want and it's kind of going like the order of operation of hey do we have shares is it greater than zero if it's not then what do we want to do this precision is do we have a fractional share and now it's saying hey if none of these criteria are met then we'll drop down to this part it's i have another video that explains this code besides the split because i didn't have the split in that code but it explains everything else you guys could check that out if you want to learn more about what's going on here but this video is going to be long enough so that's why i really didn't have time to just kind of explain everything so we want shares to sell minus equals so minus equals item to sell dot shares semicolon hit enter active trades dot splice 0 comma 1 semicolon that part is done so part 6 is complete guys we have two more parts of this long function so now that we are on the outside of this squiggly bracket we're going to hit enter a couple times i'm going to do else semicolon not semicolon squiggly bracket hit enter item to cell dot shares minus equals shares to sell semicolon hit enter shares to sell equals zero semicolon then we want to go one that parts done see that's getting easier and easier one two three hit enter a couple of times now the last part of this function is going to be if active trades dot length oh make sure length isn't capitalized length equals equals zero go on the outside hit the squiggly bracket hit enter portfolio dot delete open parentheses ticker on the outside hit our semicolon and then we want to be 1 two three and actually four so we could delete some of these lines here so we could bring this all the way back up and there we go and i'm just going to go like this to move everything down okay great so look at this we got one two three four five going all uh descending down so what we want to be is one two three four hit enter return portfolio semicolon boom that is the second function done thank god again guys check i guess these last uh three parts because i kind of blew through them quickly make sure everything is right here you have no syntax or no spelling errors whatsoever and guys i actually had an error right here i didn't mean to have a forward slash i meant to have a period um there's no there's no guarantee that i have everything right here too trying the best is possible or not have any kind of error but we won't know until we actually try the function and make sure that everything actually works but let's go on to part three the third function it's nowhere near as hard as the second one a little bit more difficult than the first one uh it is only three parts so it's not too bad we'll hit enter a lot just to kind of get rid of that here we go all right so what we want to do is we're creating another function so function my positions open parentheses security actions quantity and price on the outside do our squiggly bracket hit enter let portfolio equal generate trade i lied we want generate fifo open parentheses security action actions so plural quantity and price on the outside we want to hit our semicolon we're going to hit enter a couple of times now we want let return array equal square bracket open and close semicolon hit enter portfolio dot 4 each open two parentheses value comma key go on the outside of one parentheses but not both equals greater than i'm gonna hit a space do a squiggly bracket hit enter and we want three let's so we got let shares equals zero semicolon let total cost equal zero semicolon and then let average or avg price equal zero semicolon hit enter then we want map not map want value dot map open parenthesis trade space equals greater than another squiggly bracket hit enter then we want shares plus equals trade dot shares semicolon space total cost plus equals trade dot shares times get rid of that b whoops times trade dot price semicolon you want to go on the outside of this one hit semicolon i'm going to hit enter a couple of times pause make sure that this is right and guys i do have one spelling error right here well not spelling but syntax cost well the c should be capitalized so after you check everything there we're going to be right on the outside of these semicolons we're going to hit enter and now we are going to write this is the next part is going to be average price so avg price equals total cost divided by shares semicolon hit enter return array dot push open parenthesis open square bracket key comma shares comma average price only outside of that we are going to hit our semicolon and then we can actually bring this up like that right here we could also hit semicolon and then in between the two so in between this one and that one we're going to do return space return array semicolon and guys that is it check to make sure that last part is right with no spelling hours whatsoever now this is the nerve wracking part after we have all this you can hit save and now if you want you can hit run and this is what you want to see execution started execution completed but we're not out of the woods yet there still could be an error and the only way to see if we have it right is we got to put in our function so you could at this point if you have it saved we could x out of this okay go back to our spreadsheet we can name this i'm gonna name it dark theme master portfolio tracker all right after we name it one thing i did realize so right here split has no price so you get rid of that you do a dash if you want and on top of that right here for drip um i don't know where i got 5.5 i guess i was typing in fast we'll say that the price was 55 is what i meant to say uh and the reason why i'm saying 55 is if we're saying apple was a hundred dollars here and then it's split for two for one that would cut the price in half it would double our shares but cut the price in half to 50 and then i'm just pretending that you know the stock went up 10 so that's where i got that 55 from and on top of that nvidia i'm going to want that to be not 55 but 550 so that's what we sold the two shares for but again we'll explain all of this in a second let's go to positions let's actually try to make sure everything works up here we can type portfolio positions hit enter we want tickers shares and purchase because these are what we're going to be getting these three things is what the function should well the code should get us so i'm going to hit equals my positions open parentheses we're going to go to history we're going to highlight all of this hit comma highlight action hit comma highlight quantity hit comma and highlight price and then we're going to close the parentheses so you can see right here i have my positions history exclamation point b3 to b12 history exclamation point c3 to c12 history exclamation point d3 to d12 history exclamation point e3 to e12 we're pulling security action quantity and price that's what we just did that entire code for hit enter it's loading boom there we go everything is right this is what you should have if you don't something could be wrong with not only your uh code but maybe you typed in the wrong function it has to be equals my position the p has to be capitalized if the code doesn't have this spelt right maybe you misspelt positions in the code so you just have to go and change that on top of that you got to make sure that there's nothing in here that would throw it off like if we had i think a letter may throw it off so if we had a letter you see that that's not going to work we get rid of the letter then it does work okay on top of that if you over sell so watch if we have nvidia or we'll say target instead of dividend we'll well no we'll change in video to sell and we'll say 10. so we sold 10 nvidia but we don't have 10 nvidia to sell nvidia is gone sometimes it will break the code that way so you got to be careful with that but if everything's working and this is what you have here's how we can check to make sure everything is proper so we deposited ten thousand dollars worth of cash that's not going to affect our positions really that's going to be for the dashboard but apple we bought 10 shares then we had a two for one split so now we should have 20 shares at the price if not 100 but the price of 50. and then you can see we had a drip so we had something reinvest back into apple 0.1 so this is after our split so technically we should have 20.1 shares and the price was 55. so the price was 55 and our original apple price was 50 right because this is after the split so it should be 50 and some change and that's what it is it's 50 and some change that's accounting for the dividend you can see that it actually split down to 50. if we get rid of this split right here watch this let's delete that row look at that now the price the purchase price is up to 99.5 because it's saying that we got our dividend for 55 and our shares is only 10.1 let's hit control z bring the split back 20 shares now and the price is half so we know that works great now what about paypal paypal was just a buy that i threw in there but nvidia we had a buy we bought two nvidia for 500 then we sold both nvidia for 5.55 and then we bought nvidia again for 600. go to positions look at that nvidia only one share for 600. so we know that also works and then lastly there's target got a dividend so we have target dividend only one so if we jump over to target we can see that that dividend for shares it's not included because it's not a drip this shouldn't be 20 this should really be say 201. all right so now that we know this all works that our function works we can start putting in our actual data so this was just data to test i'm going to delete this i'm actually going to jump over here and just copy and paste what i actually have because these are all of the positions that i have obviously besides volkswagen i just that did that for example but we'll throw it in as well i'm gonna hit ctrl shift v so it puts everything in without a format now guys one thing that you need to understand is it needs to be exactly like this if you just download a csv or cvf whatever it's called from your brokerage account with all of your positions if it's not in this format it's not going to work so you're going to have to make it into this format it might be a little tedious but once it's done then you're good and then you just need to slowly add as you buy more all right so the first thing i'm going to do i'm actually going to click here i'm going to click on c right click i'm going to go to data validation and what we want to do is we want to enter all of the different drop downs so we're going to go to list of items and we'll type in deposit we'll type in sell we have interest we have adjustment we have split dividend drip what else we got split dividend drip withdrawal i think that's everything so i'm gonna hit save here and now it gives us a drop down so we could change this to whatever we want you can see actions also has the drop down which i don't want so we're actually gonna click on action now right click we're going to go to data validation again and then remove it and then we're good so now this is all set up for me you guys put in all of your entries it has to be in date order now the code doesn't account for the date on the left here but it has to be in a descending order where your earliest entry is at the top and then as you go down later entries happen because that's how the code will read it it'll go down the list so if i bought apple or even better if i bought cvs down here oh well up here if i scroll all the way down if i have that entry down here it's going to mess everything up because i then sold cvs somewhere over here i don't know where but i sold cvs somewhere um it won't account for that instead it would read saying that i sold first then i bought later and that's not what i did so keep that in mind i'm going to go up here and drop this down like that so now what we want to do is make this the dark theme color i'm going to save these two rows right here f and g if we go to h and go all the way over we could delete all of these we're not going to need them so i'm going to go to delete h through z i'm saving these two because this is where our conversions are going to be so up here i'm going to merge this and i'll name this conversion we'll keep it like that for now we will have to do some more stuff up here when we have our conversion tab so we're going to hold off on that so this will be something we'll do later but let's make this into the color that we want so if we click up here you could make it you could go to this and just kind of make it all black but that's going to be too harsh on the eyes i don't know if you guys have noticed but any time see the look this is way too harsh any time you turn on dark theme for something it's never truly black it's like in dark gray like very dark gray maybe something like that that might be a little too light but just kind of mess around for what you like here let's see maybe a little bit darker like that that looks good i like how that looks we'll click up here instead of 10 we'll make this bigger let's try how about 18. that looks better also make this a little bit bigger make this maybe 12. yeah 12 looks good and then the last thing that i have before we move on if we go over here and then if you click on borders go to white we want it kind of thick like that throw border in there just to kind of split the two we could even have the border up here as well there we go and then for conversion we can center this so we'll do that all right now the last thing we're going to do here is actually i'm going to click on this i'm going to make sure everything's centered all right good we're going to go over here to actions i'm going to right click and we're going to do data validation not data validation we already did that we're going to do conditional formatting and now with this we could change we're going to keep the color the same so our nice dark theme color but we're going to change the text depending on what it says so we're going to go to if text is exactly deposit then we're going to change it and i'm going to change it to a nice bright blue and we'll make it bold as well add another rule and then we will do buy so obviously buy is going to be green that green if if you think that green's a little too harsh again you can go to custom and you can mess around with uh the right kind of green that you want maybe something like that then i'm going to hit add we'll do sell obviously cell is going to be red add we'll do interest interest i'll make maybe like that pinkish add rule we'll do drip i don't know what to make drip i think i had it like an orange here we go we'll do that orange add rule i had dividend we'll make dividend i don't even know how about like a blue like that you know you can just mess around with all the colors i think that's good for now that's pretty much most the stuff i'm missing maybe adjustment and split so there's adjustments or white and the split is white but you can make those colors whatever you want i think it's kind of easy to uh see exactly what's going on here so i can see exactly where my deposits are uh if i want to see where drip is oh that's easy one two three four five six seven eight it's just easier on the eyes to look at everything see exactly where my cells are because all one color is just way too difficult but we're pretty much done with this tab for now actually the very last thing we're going to do which is very cool is we're going to go and click on a and what we want to do is up here where it says a a we're going to name this we're going to name it history date i'm going to go up here name this history security hit enter you know what we didn't hit enter here so that's what you have to do if you do history see look now history security's there history date hit enter and then it logs it now we got history security history look at that there's date and security this is going to be history action and this is going to help us when we're making our dashboard and also when we're making our uh our positions tab as well this is going to be history quantity hit enter then we'll go over here this would be history price hit enter and actually well i just remembered we want to then hit one to the right and we're going to want to move the border which is kind of annoying so we're going to turn the border off there jump over here throw the border right over there because we're missing one thing oops and we want to get rid of this we're missing total so we want total and what is total going to be well total is going to be equals if error open parenthesis open parenthesis if open parenthesis is blank open parenthesis we're going to click on right over there a3 actually you know what we'll click on b3 sometimes you guys are lazy and you don't put in the date at least that's what i do sometimes so we're going to do comma quotation quotation comma then we're going to want abs so absolute open parenthesis we're going to want e so right here multiplied by d close close close comma quotation dash quotation close and then hit enter and then yes we will autofill now some of you guys and actually we'll probably see this the code's not down here so if you just want to drag this all the way down so then the code is all the way down to the very last cell and you guys may be asking why do we have absolute and why are we saying if is blank when there's shortcuts we could do shortcuts uh this is just uh to make sure that no errors happen whatsoever and then the if error is actually for the split so if we go down to split this is just going to show up as an error because you're trying to take this which is not a number and this and you get an error message so instead it just gives a nice little dash but now we are done with history we'll come back when we do the conversion let's jump over to positions so now right away you may be thinking oh hold on this doesn't look right because i have more than six stocks and on top of that if you guys listen to me in the beginning i said i don't have cvs anymore so what's going on here well if you look up here we have history b3 for b12 so it's just taking all of these cells right here it's not taking any of this in so we need to update this we just did this to test everything so we want to go within our positions or within the parentheses hit back and here's the cool part guys we're going to type in history and look at this here's everything we want history security see that isn't that cool everything's here so history security comma history action is next comma history quantity comma history price and that will put in everything we need which you can see here is security action quantity we hit enter look at that now everything updates this is exactly what i have i'm going to click here and center everything i'm going to go up to where i have this go all the way over to j hit merge we can make this bigger if we want so maybe 14 how about 18 18 looks a little too big 14 looks a little too small we'll call it 16. that looks much better click over here we'll bold it we'll make sure this is bolded as well and we'll make this one bigger we'll make it to 11. all right so now that we have our tickers shares and purchase the next couple things are pretty easy so we need price change percent change dollar or if you're in europe and you want change euro just make sure your conversion is different whatever your currency format is we want cost we want value gain percent and then gain dollar it's not limited to this you could add more stuff you want to see i don't know like the beta of it or whatever you can add whatever you want but this is all i want to see we're going to get all of this i'm going to right click and delete everything and then we're going to start with price so it's going to be equals if is blank then we want a3 close that comma quotation quotation oops we want double quotations comma google finance i'm going to click on that and we want to do a3 again we're going to close that off hit enter it's loading there is apple's price and i'm going to drag this all the way down i'm gonna drag it down to that and what i'm actually gonna do i'm gonna go down to a thousand i will not own a thousand stocks ever i would just invest in etf at that point i'll go down to 101 hit shift hit that right click and then delete all of that so there we go i really only need that much i wouldn't i wouldn't own 100 individual stocks either i'd probably cap out at maybe i don't know with cryptocurrencies included maybe i'll cop out at like 22. 20 stocks couple of cryptos that's it but again i'm not a financial advisor and my strategies are no better than yours so you do what you want so now the next is going to be change and change is not going to be as easy as price price was the easy one change is going to be a little bit longer but nothing is as bad as that dreaded code that we had to make in the very beginning so this is what we're going to want if error open parentheses if open parenthesis is blank open parentheses again a4 well a3 close parentheses comma quotation quotation comma google finance a4 we want a 4 not a4 a3 i don't know why i keep saying a4 comma change pct close our quotations close our parentheses divide that by 100 and we're not done here i want to close that we now want to hit comma we're going to do google finance a4 again a3 again close our parentheses hit minus open parenthesis index and i'll explain why we're making this a lot more complicated than what it has to be it's actually because of uh cryptocurrency so if we want to add cryptocurrency this is why we need the next couple of parts so we got index google finance open quotation currency colon open quotation hit the ampersand or the end sign which should be shift or command 7 a3 hit the end sign again hit quotations twice hit a comma hit quotation again throw price in there close that quotation what are we already on the yeah we're on the second line now i could drag this down so we're at price now let's see after that we're going to want to do comma today open and close parentheses so now we're pulling today's price from the cryptocurrencies we want to then subtract that by one so now we're pulling yesterday's price close that hit comma hit two hit comma hit two again because well i'll explain later close one two three divide that by index so divide by index google finance currency again colon quotation quota uh do we want two quotations no we just want one quotation hit end a3 and now we want two quotations comma quotation price quotation comma today minus one whoops today open close minus one comma two comma two close close close hit enter there was a problem we are missing something oh i have too many hit enter it's loading oh we got an n a let's see what we are missing all right it looks like right here i don't have an open parenthesis so we want to open that close that then hit enter there we go let's change this to a percent and then we can drag this all the way down and it's loading and it looks like everything's working but our kryptos so i must have screwed up somewhere in here let's drop that down we need a parenthesis right there at the index and then we also need one right after the negative one then if we hit enter look at that now we got six point two eight percent drag that down and we got seven point seven two percent both bitcoin and ethereum had a crazy day today but that is how you get your change percent for bitcoin and ethereum and google finance i mean we could pull google finance by doing btc usd or if you're in euros btc eur it does suck that we're pretty limited with what cryptos we can do in google finance but uh it is what it is let's move on to change oh and what we want to do is kind of move this all the way up so we're just taking this function the proper one and we're changing everything because if we drop down to here you can see this uh if we don't do that like this is still missing the parentheses so we want to take the proper one that's working in case if we pick up other cryptos or if the cryptos move and speaking of moving one thing that you guys asked how do we put this in alphabetical order that's a great question if we click up here we go up to here and we do sort for sort and then put this in parentheses hit enter it loads now look at that everything is in alphabetical order all right so let's move on to change dollar this code is this function is a lot easier than this one so i'm going to go over here and we'll go up here hit equals if error open parentheses if open is blank open not open we already have an open a3 close comma quotation quotation comma google finance for you guys thinking that i'm going way too fast i'm sorry i just know this video is going to be very long so i'm trying to make it as time efficient as possible you guys have the luxury of pausing whenever you want to make sure all of your functions are proper and running change close multiply b4 not b4 b3 close comma google finance again so on google finance a3 close minus index google finance quotation currency colon quotation and a3 and quotation quotation comma quotation price close quotation comma today open and close minus one close quotation comma two comma two and then one two three four and let's see if i have an error or not it's loading boom looks like we have it and if we drop this down does everything work it does look at that we even got for let's go to bitcoin as you can see bitcoin was 58 000 and it had a change of 3758 in one day so yeah six percent sounds about right you guys could check the math on that but guys the hard part is done for the portfolio positions these next four are going to be very short and easy functions so let me just go back to bitcoin uh for the change percentage and explain what's going on here so what's happening with the function because you guys like to know kind of how these functions work so it's saying hey if a5 is blank leave it blank but it's not so then it's saying okay let's throw in this google finance a5 change percent divided by 100 well that's not going to work for bitcoin because this only works for tickers and stocks so it's going to spit out an error message but it's saying hey if we have an error message which we do the comma means move on to this next one and what's this next one it's take google finance take the price of bitcoin currently subtract it from yesterday's price so that's why we do today minus one then divide it by yesterday's price that's how you get change percent all change percent is today's price minus yesterday's price and then divide that by yesterday's price so that's what's kind of happening there that's how it works let's move on to cost course it's very easy is going to be if is blank so if is blank a3 comma quotation quotation comma then what we want is c3 times b3 that's really all it is we can close that hit enter hit yes all cost is is your purchase multiplied by your shares which i think is pretty straightforward drop that down to 100 let's go to value that was going to be very similar so i'm actually going to copy this and then paste it here and instead we want d and then instead of c we want b hit enter hit ok and then we can drag this down and for value again pretty straightforward d and b b is shares d is current price that's our current value so you can see apple i it cost me three thousand two hundred and three dollars and now i have four thousand seven hundred and twenty four dollars in apple gain this one is gain is going to be very similar to change in the sense of how i just explained it it's going to be equals if is blank a3 close comma quotation quotation comma close then what we want is d3 minus c3 close divided by c3 close hit enter hit that we'll click on i will make it a percent there we go there's all of my percentages we'll drag this down all right and now the last one which is probably one of the easiest ones we're gonna do equals if is blank and yes there is a shortcut we don't have to do if is blank we can do if a3 equals blank then make it blank that is a shortcut but we're just going to keep everything if is blank is blank because it's easier to read it as a human that way a3 comma quotation quotation comma then we want h3 minus g3 close that hit enter hit the check that's gain that's our dollar gain how do you figure that out you take what our value is and you subtract it from our cost and this is our profit or loss drag that down and now you may be wondering why do we have so many decimal places well that's because of the code remember in the code we wrote precision five so precision five is taking it five decimal places back now if you're someone that's really into cryptocurrencies and you have a lot of maybe bitcoin and ethereum you may have to go back even further than five decimal places so if that's the case go back to your script editor i know i said we wouldn't have to go back to this but it's just for your crypto guys and if you want to clean this up you can you don't need these massive spaces in between we just did that so it was easier to write the code because then we'd get overwhelmed if everything was on top of each other but you could clean it all up like this if you ever have to go back to it who knows maybe in a year i'll come out with an even better video and we'll have to make uh some updates to the code but there we go now everything looks a little bit cleaner everything's closer together but right here let precision equal five if you're doing cryptos maybe you're more than five percent uh five decimals back maybe you're six decimals back maybe you're eight whatever it might be uh we'll we'll do we'll say eight okay and then if we hit save it's not going to mess with anything all that does is take the math and move it back eight decimal places so if we go let's hit on everything so you see that we just went what are we one two three four five we keep going decimal places back um and it looks like i mean bitcoin and ethereum i'm pretty leveled out but you could see well that's price you can see right here some of the shares that i have like into it i have 323.60492 one big problem that people will have is when they're trying to sell a stock they may come up with an error message or it may still be on here even though i'll say there's zero shares it may not actually be zero it may be you have 0.00001 of a share and that's going to pop up so everything needs to be super precise when it comes to decimals so keep that in mind i'm going to shrink the decimals down to just two just so it looks clean right now but guys this is the portfolio section uh or the positions tab we're now done with this so we can add in all of the nice colors so to start we will click on everything right click i'm going to go to conditional formatting and then if it's not empty we'll use our lovely black or dark gray hit okay get x out of that we'll change all of our text to white or even if you want an off-white off-white also looks pretty nice up here i'm going to add a nice thick border like we had want to change the color to that hit that boom there we go so now what we're going to do is whether our text whether our change is positive or negative i want to change the color it's going to conditional formatting hit add rule what we want to do is that green that i have not for our background we want the background to stay black we want green and then if it is greater than or equal to zero hit done move this above whoops it's going to hit on we want to move this above that look at that now all that's green so that's for everything that was in the green today uh let's go back into it we'll bold it okay add another rule and now we want less than so if it is less than zero we then want it red what do i do we don't want that red we want that black as well i don't know why i keep hitting that there we go we'll bold it hit done bring that above and there we go now that's red so now we can go here and we want to do that also to the change dollar and then the gain and the gain dollar we click here add another range how easy is that let's just hit f click ok and it adds it add another range we'll go right there hit okay add one more range we'll do j hit okay hit done then we want to jump back over to e where we have the less than zero open this now we're going to do the same thing we're going to add these to all of these so whoops go back here add another range f add another range i add another range j hit ok now all that gets added we can hit done we get x out of that the very last thing that we want to do highlight all of this and make the text is it that gray now that's too dark let's try again that one and then go over to where is it insert not insert view grid lines get rid of the grid lines that's still a little too dark because it's one more it's probably this there we go now it looks like that's completely gone i just like how that looks so this is all i want to focus on you can zoom in if you want to as well you know so it could fit your screen whatever it might be but now our positions tab is done so now we get to jump over hit that little plus button we're going to go to rename and this is gonna be named conversion if i have any spelling mistakes guys forgive me for that awful speller good investor all right investor anyway uh let's go right over here so a to e we are going to merge that and we're going to name this currency conversion and then we're going to go from f to i merge that and we're going to leave that blank for now but we are going to make sure that that is to the left and that this is all the way to the right all right now right here we're going to have currency tickers shares purchase price change cost i know this is looking very similar to what we had in the positions tab value and then gain although now we're going to start to change some stuff up so first we're going to start with currency this is going to need a certain function that function is going to be equals if error open parentheses is blank uh if so open parentheses if then another open parenthesis is blank open parenthesis and we can jump over to positions and we're just going to click positions a3 so it's positions exclamation point a3 guys if it is not a different color than black so if it's not like orange like mine or you know another color as long as it's not black then you're doing it right if it's black it means that you aren't actually grabbing onto the positions tab maybe you're forgetting the exclamation point uh we're gonna do comma quotation quotation comma a uh if google finance so google finance then we want positions a3 again so i'm going to go over there positions a3 comma we jump back to conversion what we're going to want is currency so currency close quotations close parentheses equals now here's where it gets interesting so a lot of stocks are going to be in sterling pounds and for all of my british followers sorry if i messed this up but uh with the terminology but i think all of your stocks are in british pounds or at least google finance will show it in the sterling pounds but it's not going to show in your british pound and a lot of you guys are keeping your currency in british pounds and not uh british pennies or sterling pennies so here's what we have to do we actually have to do some conversion ourselves it's going to do gbx because that's what all the stocks are going to show up as comma gbp because that's the currency we actually want to use comma google finance and it's weird for some reason google finance doesn't register gbx it only registers gbp uh for the currency but then the stocks come up as gbx so we have to work around that positions and for most you guys positions exclamation point a3 or we could have just clicked on it for all you guys who aren't in uh who aren't british or aren't trading with british pounds it really is not going to retain to you so you're not going to know what i'm saying but for everyone who is uh this is the solution essentially so we got a3 comma currency close close close comma then what we want is money sign f money sign one so you see it's pulling from here but we don't have anything in f1 yet we got to put that in so we're going to hit enter it's going to be there you go we got usd and we could drag this down if we drag it down you can start to see stuff will slowly start to fill drag it all the way down again we could same thing as a positions tab we don't need a thousand cells so we could delete most of these i'm gonna go to 100 right click go to delete good now these are blank because they are kryptos so you got to keep that in mind but that's going to be fixed in a second so what we're going to fill in now is going to be right up here so up here we're going to do data validation so i'm going to click on this and we're going to go list of range and then where do we want it to pull from we're going to drag this all the way down to 100 and hit ok hit save this is where we're going to pull it from so you can see there it is right and we could click usd or euro where are we getting that well because we have currencies that are all in either usd or the euro so we're gonna pick usd and it fills in our bitcoin you see that so we got usd for bitcoin if we go to euro it'll switch our bitcoin and ethereum or at least our cryptos to euros so that solves that problem you know what we're going to unmerge we're going to delete all of these because we don't need that to have data validation so i'm going to go to data validation remove just so this drop down is close here and this is now pretty centered so we could go to one we could make this bigger maybe 14 maybe even bigger than 14. uh yeah we'll make it 18 or maybe i like 16. 16. good we are on a good start this is going to be the easiest one all we're going to do is hit equals a well jump over to positions hit a3 hit enter that's it probably the easiest function of the night or morning depending on when you're watching this we're going to scroll down boom we're done shares going to be just as easy we're going to hit equals jump over to positions what we want is right there b3 hit enter drag this down now that one's filled and now we get into the fun stuff uh for this one we're essentially going to write out a really long function in purchase and then we could kind of copy and paste it into the rest that we need it for so here we go we're going to do if open parentheses a3 equals g bp close quotation comma if open parenthesis b3 equals quotation quotation comma quotation quotation comma if again so now we have three if statements open parentheses a3 equals we want to do dollar sign f dollar sign one so that locks that in comma we're going to jump over to positions now and we want to pull right here our purchase price which is c3 so c3 comma c3 multiplied by google finance so i'm going to get google finance quotation currency colon quotation and a3 and quotation quotation and dollar sign f dollar sign 1 and again quotation quotation close close close divided by 100 comma if uh open parenthesis b3 equals quotation quotation comma quotation quotation comma if open parenthesis a3 equals dollar sign f dollar sign one comma positions comma positions multiplied by google finance again quotation currency colon quotation and a3 and quotation quotation and dollar sign f dollar sign one end quotation quotation close close close close hit enter now you're probably thinking what did we just write well this is going to be converting everything so we could drag this down and i'll explain in a second if we go back up let's look at volkswagen it says it's 395 cents and if we jump over to positions volkswagen says it's 290 dollars so it's not dollars this is currently in euros that's at least how google finance is polling so it's polling euros which is 290 euros and that converts to 300 so gbp this is up here what i was explaining before when we are over here if we pull gbx and gbp well then this is the second part of the equation that we need for it to actually work so that's why this is so ridiculously long a decent part of it has to be because of gbp uh but now this is it so whatever stocks you trade doesn't matter what currency it is in it should work and the beauty of it all is as you add more currencies so let's say uh i don't know irn or inr is what it is that's uh the indian currency we jump down here look at this now it's there so now this will all convert to the indian currency so that's how that works the reason why bitcoin and ethereum didn't change is because i still have it under btc usd if uh i was buying it under inr that would have changed under my history i wouldn't be buying it in usd instead here's bitcoin i'd be buying it in btc inr and that's where it actually update uh but let's get rid of that and we'll change this back to usd let me drop that down now price is going to be easier i promise all we have to do is go to purchase highlight all of this hit control c go over to purchase hit uh price hit control v and most of this stays the same all we have to do is change position c3 to d3 so d3 d3 then over here d3 and d3 the rest can stay the same we hit enter and there we go so i told you guys would be a lot easier we drop it down boom price is done change we're gonna be doing the same thing so we could control v again and instead of c we want f so f f f over here and f over here essentially all this code is doing or all this function is doing if you guys haven't noticed yet is we are just taking the positions over here and then with this nice little function we wrote it's converting it into whatever currency you want it then we have cost cost is going to be pretty simple because now we're just going to be pulling from this this is all the data we needed just going to hit equals if open parenthesis is blank b3 close comma quotation quotation comma d three multiplied by c three close that off hit enter add one too many quotations on our quotations parentheses let's get rid of that hit enter boom there's the right answer let's then drag this all the way down go back up i'm gonna go over to value gonna be another easy one we're actually now just gonna copy and paste this so i'm gonna copy this i'm gonna go into value hit control v we're changing this d to an e everything else can stay the same value is now done we could drop this down that is good last one is gain we are going to hit control v again except now well control the yeah ctrl v to paste now i'm going to delete all that we're going to do h3 minus g3 hit enter and boom that's all it is so those last three codes were very simple i mean all of them were simple once you got purchased so once you fit that in then the rest of this is just easy stuff we're going to go up here and we are going to center it because i like everything centered we're going to go to text well not text we're going to go to default so everything changes and you know what yeah we're going to go over here we're going to hit 10 we just got to change everything so 10 we could change this back to 16 then we can drop down here change this to 11 and then if you want to make everything look all nice and neat uh we could reduce the decimals so it just shows two there we go guys if you are not in the united states and you don't know how to change your currency um if you let's see where is it if you go down here look at this here's the euro here's different like dollars if you go to more formats more currencies it's right here so let's say you were using i don't know um chinese yan or yen however you say that hit apply look there it is so now it's there so that's how you do all of that i'm just gonna go here hit that uh we don't want any dollar sign for shares but now we could uh put in our currency so that one is a currency want that to be currency change obviously cost value and dollar gain there we go so now i have my proper currency you guys put your proper currency in just know when i change it to euro it's not going to change the dollar to the euro but this is all in euro just has the wrong uh symbol in front the wrong currency symbol but this is actually all in euros now we're going to change it back to usd this is now done we could make this look pretty now um here i'm gonna bring that to the right bring this to the left just so they're like touching we could do the exact same thing where we highlight all of this and we could go to some conditional formatting if it's not blank we add in our nice dark gray hit ok we can x out of that we could also change these three to that dark gray as well uh let's go back to some conditional formatting we could reopen this and we could change our text to that off white hit okay now we could change we could add in our colors so let's go to change right click go to conditional formatting add a rule and we want this to be black and then this text to be green if it's greater than or equal to zero hit done just bring this above that boom we have that and we could add another rule if it's less than zero then we want it to be our red we can make that the gray hit done bring that up we could click on this could click right here we could add another range we will add our gain to it hit okay oh we can bold it we could go let's see we hit done go back over to f1 click on this hit this add another range go to gain hit okay make that bold hit done x out of that the very last thing for this that i actually forgot which i think is pretty cool when we go up here where it says purchase what we want to actually do we're going to make this a function we go to equals and then we want to put purchase in quotations so equals purchase and if we hit enter that's it it'll just stay as purchase right which makes logical sense but what we want to do is go in between the e and the quotation hit space hit open parenthesis then hit quotation and f1 end close quotation well go on the outside of the quotation close the parentheses close quotation hit enter now let's make that a little bit bigger anytime we change this up here it will update it here so we can see okay this is in usd it's great well let's go to euro look at that it changes that to euro so i think that's a pretty cool function to have we can make all of them like this so equals so again it's equals quotation space close quotation and f1 and open quotation close parentheses close quotation hit enter there we go i'm going to fast forward so you guys don't want need to watch me do the same for all of these but that's exactly what it is for every single one so now the very last thing that we want to do with this i'm going to bold this i'm going to go from a to i we're going to add a border so we'll make sure we have white selected and we want it really thick do that there we go and i'm also going to add a border here i'm just going to click on that we're going to go to our borders again i'm going to go down and actually instead of white we want that off-white and you'll see why in a second so make sure that's selected and then we can do that the same here with our border make sure it's the off-white not the white and the reason why is because i want to click up here now and i want to change this alt to that off-white obviously change these back to degree and we could delete all this which is unnecessary unless you want to add more uh columns for here with more information and lastly we will go to view turn off gridlines we are done with that we are about to move on to the dashboard which is the coolest part in my opinion but before that we do have to go back to history and fill out our two tabs for conversion all right so the very first thing we're gonna do on uh the conversion section of the history tab is hit equals jump over to conversion we want to click on our lovely f1 hit enter and that one is done we then want to go over to total there's gonna be another easy one it's gonna be the equals quotation total space open parenthesis quotation and g2 and quotation close parenthesis quotation hit enter those two are done now i will warn you right now these two are going to be a little lengthy but i think we're used to that now this is what's going to be equals if error open parentheses if open parenthesis c3 equals crypto and crypto is case sensitive so we have a capital c essentially it wants to match our uh action so when we buy crypto our action is case sensitive with capital c in action so it has to match here uh then we want to do comma then we want to do dollar sign g dollar sign two then we want to do comma open parenthesis if open parenthesis is blank open parenthesis b 3 close comma quotation quotation comma if open parentheses b3 equals then another one that's case sensitive so cash close parentheses comma dollar sign g dollar sign to comma if google finance b3 comma open uh quotation currency currency quotation close parentheses equals quotation g b x close quotation oops gbx close our quotation comma another quotation gbp comma google finance again oh we want to close our quotation comma google finance parentheses b3 comma currency again close then we want to do one two three four five six dot not dot comma conversion exclamation point dollar f dollar one probably have conversion spell wrong let's just jump over to conversion this way hit that so i have no spelling hours close that and before we hit enter we just want to go before the f do dollar sign and then before the one dollar sign then we get to enter there we go we got usd we could drag this all the way down and there we go everything should be in yep look at that everything's in you can see that is in euro so it's right there there's going to be the last one here all right this one's going to be another long one but i know we could get it done so it's going to be equals if g3 equals quotation g bp close quotation comma if open quotation uh open parenthesis b3 equals quotation quotation comma quotation quotation comma if parentheses g3 equals dollar sign g dollar sign 2 comma f 3 comma f again multiplied by google finance quotation currency colon quotation and g3 and quotation quotation and dollar sign g dollar sign two and quotation quotation close close close divided by 100 comma if open parenthesis a3 equals quotation quotation comma quotation quotation comma if open parenthesis g 3 equals dollar sign g dollar sign 2 comma f3 comma f3 multiplied by google finance parentheses uh quotation currency colon parentheses not parentheses quotation and g3 and quotation quotation and dollar sign g dollar sign 2 and quotation quotation close close close close hit enter we have that let's drag this down i'm gonna stop right at our uh euro just to make sure it's right yep we're looking good we can keep on going all the way down to 1000 all right now we can drag it all the way back up and we could throw on our lovely dollar sign and there we go now that is all in the hour price uh we go to conversion we change this over to euros then we go to history look now euro and this is all in euros so that is done guys we could finally move on to the dashboard hit that new sheet bring the sheet to i don't know guess the first one if you want right click rename dash board all right before we even touch this we're going to do what we did to history where we go up on top of the cells and we're just going to name all these so this is going to be uh positions tickers so positions tickers hit enter we go over to b name this one positions shares hit enter then this one positions purchase hit enter and guys just do it with every single one so this should be positions price positions change percent positions change dollar positions cost value dollar gain so i'm just gonna fast forward through that uh so then it's all done you don't have to watch me retype them all so once you have all of the positions in so you can see up here i have every single cell or every single column or row has all of their positions in right so we got position price position game percent gain dollar have all of that we could jump back to the dashboard and we can start creating this so for the first let's see these first two cells right here well first four cells we could merge we're going to type in welcome back and then the next four cells we're going to merge as well you write whatever you want you write your name i'm going to write think stocks with an exclamation point so that's good and you know what i actually want to merge down to the third so i'm going to merge all three for all of these then i want the next two right here we're going to merge this one and then we're going to merge this right over here and this is going to be easy it's going to be equals now open and close parentheses and it gives you today's date and time uh it'll refresh every five minutes or so uh and now this one guys this is going to be a pretty obnoxious code this is actually to get market um this is where we're going to determine if the market is open or closed so this one's pretty long i think this is going to be the last oh no there's going to be the second to last really long code so just bear with me guys it's going to be if open parentheses or open parentheses week day open parentheses a 4 close close equals 1 comma open parenthesis week day again open a 4 close and i just realized i have a couple zeros here and it means zeros i mean a uh closed parenthesis so we got close oh i wanted to close here so guys i messed up there i hit zeros instead of parentheses i meant to do parentheses so close close this is gonna be the same deal close close equals seven close comma in quotations we want market space closed close that and then right before the or we also want a parenthesis i forgot to add that sorry about that guys so now we're back to market closed equals now we're back to market closed we want comma then we want if open parentheses time open parentheses hour open parentheses now open close close comma minute uh open parentheses now open close close comma second open now open one two three for close is greater than time open 16-0-0 close and now you may not be doing 16 0-0 this is when the market closes for me 1 600 hours okay so whenever the market closes for you that's when you're going to be doing this i did make another video that shows depending on where you are in the world which uh open and closes you're going to want but this is technically going to be for market hours for the new york stock exchange and the nasdaq market closed comma then we want if open open time open hour open now open close close comma minute open now open close close comma second open now open one two three for close is greater than time now we're putting in when the market opens for at least the new york stock exchange and the nasdaq which is nine comma thirty 30 0 close close comma then we want in quotations market space open comma close quotation comma open quotation market space close close close close close hit enter there we go the market is closed for me right now very obnoxious formula but there it is guys pause the video make sure it's right again for 16 this is when your market is closed this is when your market is open so if you're not in the united states if you're not if your market doesn't close at 4 pm eastern standard time and 9 30 pm uh eastern standard time for open then you want to change that to whatever it is for you but that's good now we can move on to the next couple of cells which is going to be six and seven we're gonna merge those i'm gonna do day return and then we're going to merge all of these and this is going to be easy one we're going to do equals sum open then we want to jump over to our conversion go to conversion change and we just want f so conversion change exclamation point uh well conversion explanation point f uh colon f hit enter there is my day change i lost a nice two thousand eight hundred ninety nine dollars but that's okay it'll bounce back it always does then what we want here for eight is i'm gonna merge all of these i'm gonna merge oops i don't want nine as well i only want row eight i'm going to merge that i'm going to name this portfolio balances all right and now we're gonna take the first two we're gonna merge that we're actually gonna merge five down so nine we're gonna merge 10 11 12 13. all right so we've got one two three four five this is going to be a count value this one is going to be positions this one will be cash value this one will be unrealized gains this won't be realized gains all right and then we're going to jump over to here and again we're going to format all this to make it look nice and pretty i just want to get the data in first so account values is going to be pretty easy it's going to be equals positions which is c10 for me plus our cash value which is going to be c11 we hit enter it's zero because we have nothing in yet but now for positions again a very easy one we're gonna do equals sum open parentheses we're gonna go back to conversion and it is going to be our value right here so h close hit enter so there we go we got 87 000 and then we're going to go to cash value this one's going to be a little annoying but this is why we put everything in for our history with all of uh these things up here we want this as well so we're gonna name this one history history total convert hit enter now that we have that we could go back to our cash value we're going to do equals sum if we want history action comma deposit comma history total convert close that hit plus then we want sumif again now it's going to be history action again comma in quotations cell comma then we want history total convert then we want to do plus sum if history action again comma dividend comma history total convert see now that we named it it just makes it so much easier we kind of understand what's going on here so it's not just a bunch of random numbers uh so let's see history total convert now we're going to want plus another sum if history action comma interest comma history total convert close plus sum if history action comma adjustment at least in td ameritrade sometimes there's many adjustments so that's why i have that in there or maybe you may notice hey for whatever reason your brokerage account is a little bit off from what you have on the excel sheet so that's why we have that adjustment one history total convert close that then we do subtract now what do we want to subtract it out we want to subtract out well sum if history action comma by we want to subtract our buys this is going to be our history total convert close that minus sum if history action withdrawal if we withdraw any money comma history total convert and that's it so what we can see here is we're taking our deposit plus our cells plus our dividends plus our interest plus our adjustment and we're subtracting our buys and withdrawals we hit enter and i have one too many uh parentheses we're gonna get rid of that hit enter and i notice one other uh error i had an equals not a minus again sorry for that guys trying to go as quickly as possible uh but then there's the cash value seven thousand four hundred and ninety one dollars double check to make sure that those numbers add up for you but this is what you want right here so again pause the video make sure that this is all correct and we can move on to the next step so let's add a dollar sign to that and luckily unrealized gains is going to be much easier it's going to be equals sum go to conversions and we want conversion gain so you're going to click on all of that close that hit enter boom there's my gains nice thirty thousand dollar gain in the market so far all right guys now we're going to move on to realize gains this code is a little long but we've had much worse so far this is going to be equals sum if and we want to do history action comma cell comma history total convert and then we want to do plus we're going to do sumif again not some ifs some if we don't want that in parentheses either so we got sum if history action comma dividend comma history total convert then we want to subtract sum if history action comma by comma history total convert then we also want to subtract our drip so minus sum if history action comma drip comma history total convert close that off then we want to add back once we do all this we want to add back our actual uh positions so we want to do sum and then we could go to our conversion we want to add back all of our cost so what we actually spent for what we paid for so we could close that off we could hit enter and there we go there's my realized gains negative 1 392 and now we're going to move on to one of the fun ones and that is actually going to be our biggest gainer and biggest loser for the day so this is a pretty cool function that i was able to figure out and we're actually going to pull data to show hey out of all of our positions which one and you guys may realize that this looks different than maybe a couple of seconds ago because it is a new day that i'm recording that's why also now it says the day return is 380. so it took me a couple of days to record this but you could see here let's see what is our biggest gainer and what's our biggest loser and it's going to pull that depending on our change now i did catch one error that i have with our uh kryptos i did forget one thing all the way over here we actually forgot to multiply it by the amount of shares that we have and that's why it's saying i'm up 958 dollars in bitcoin today i'm not bitcoin alone is up 958 dollars but i do not have a full bitcoin i only have 0.05 so we got to factor that in so right here right before the last parentheses you want to do multiply by click on b5 if you're on the b5 1 hit enter and there we go i'm actually up only forty seven dollars so you wanna actually change that for everything so you wanna drag this drag the code so you can see look i updated it it's right here you can see this says b3 so we want to multiply it by that so b3 b4 b5 b6 b7 b8 b9 b10 11 12. you can see it's not here so we just got to drag that new code down and that is purely for our kryptos so now everything's up to date but we could jump back to our biggest gainer and biggest loser how are we going to find out which one is our biggest gainer which it looks like is ethereum and our biggest loser looks like is it paypal i think it's paypal yeah so here's how we're gonna do it we're gonna go to this cell so i'm in b14 we're gonna do equals off set and then what we're going to want to do is positions exclamation point a 2 so we want to offset positions a2 comma match parentheses max parentheses and then we could go into our positions and we're going to want positions percent chance so we could choose if we want to do percentage or if we want to do dollar so i'm actually going to do percentage i like seeing what has the biggest percentage drop because with dollar change it's a little skewed if i have so much money and say paypal that's why this number is bigger but it may not be my biggest mover for the day uh so that's why i'm going to do percent change instead of dollar but if you want a new dollar go for that so i want that which is going to be e we're going to close that off we're going to hit comma then we want to do we want to click right there so we have positions e3 colon e the reason why we do that is because we don't want to factor in this or this that will break the code so we want to start with e3 but this right here it's okay if we have the full uh range in so once we do that we want to do comma zero close that parenthesis comma zero comma one do that hit enter and look at that now we have e t h usd so essentially what's happening here is it's saying hey out of this which one has the biggest loser up it looks like it is i mean the biggest winner looks like it's eth usd and then it's jumping over here and pulling that that's how offset works so now that we have that we're going to go down to the biggest loser and it's very simple all we have to do is copy what we have here so you hit control c go down to biggest loser control v switch max to you guessed it min hit enter and etsy is my biggest loser for the day now this code is not hard at all we're just going to do equals max and then we want positions percent change so we're going to go back to positions do positions percent change right there hit enter and we'll make that a percentage and then what we could do is highlight this drop it here change our max to min hit enter and there you go it's that easy to show which one is the biggest loser and the biggest winner uh for this row right here and i don't know why i don't have a percentage on but there we go so now that has percentage on do i have percentage on over here no i have no percents in there so that's it that's how you grab the biggest loser and biggest winner for each day this one's easy because it's just pulling the actual min and max out of this row uh the tricky one was getting which one it was and that's how we had to use that offset so we're essentially grabbing the same thing here but then we need to offset it a couple so just to review it's saying hey here's our biggest winner right here now let's offset it into our a column oh okay we want to pull this cell so that's how we're getting that but now that we have that this is pretty much all the hard information here there's just a couple of things i want up here that i had so we'll put that in snp 500 i want all the indexes we have the dow jones we have the nasdaq russell and then our volatility index which i think is also very important to look at and then right here we need their tickers which is dot inx dot dji dot i x i c this one is just r u t no dot and then this one is v i x and then all we have to do is equals google finance click up here hit enter loading boom there's the s p 500 whoops we don't want to bring that down what we want to do is now bring this over so we can pull all of that all right and then after that what we're going to want to do is google finance again and we're going to take e2 or e3 not e2 no i'm sorry we're going to want to take e2 do a comma do change so we can get the change percentage out of it hit enter it's loading boom there's our change we'll drag that over all that's loading and then the very last thing here well not the very last the second or last we're going to want to see our actual uh change in a percentage form so we're just going to do e4 divided by e3 make that a percentage so now we can grab all of that drag it over that now loads and then the very last thing that i have in here i want to see the sparkline of everything and to do that i'm actually going to merge these two cells so i'm going to merge the two cells here just so we could have a better uh sparkline going merge those two those two and these two and now this is going to be equals spark line google finance e2 comma price comma today open and closed minus 364. we want to see the last 364 days comma up to today and we want three closed parenthesis hit enter it's loading boom there's our beautiful sparkline we'll bring that over to grab the other sparklines now we're getting somewhere so at this point all we're missing is the color and our charts but first i'm going to delete rows 25 all the way down to row 1000 so i'm going to hit delete and i want to delete j all the way to z i don't want to see any of that we'll delete that and now we're going to start to color everything then we'll throw in our charts and then we'll be good to go that'll be it guys so for starters i'm gonna hit up here we're gonna change the text to that off-white that we like we're gonna change it to our nice dark theme and right here we're going to want our welcome back doesn't have to say welcome back you can make it anything uh we're going to make it about 28. so there we go i'm going to bold this as well and then for think stocks i'm going to do the same thing i'm going to make it 28 but instead i'm going to give it that nice orange that i have bold that and i'm going to highlight both of these i'm going to go over the vertical line and i want it right in the center like that and now we could kind of to bring this out a bit so i'm going to bring the a and b column out and then i'll bring the c and d column out a little bit as well just so we could see all of that okay next we're going to go down to these two columns we'll make it 18. we'll bold that as well we'll do the same thing where our vertical align is going to be in the middle and now for market closed what i'm going to want to do is go to conditional formatting and we're going to make the background our dark gray i'm going to make this red when the text is exactly market closed we'll hit done we're going to add a rule make that our dark gray we're going to make this green i'm going to change this to when text is exactly mark it open hit done so there we go when it says market close it's going to be it's going to be red when it says mark it open it's going to turn green so now we're going to jump down to our day return and we're going to do the same deal we're going to highlight both of these we're going to make it font size 18 we're going to bold that we are going to to not bring in the center i'm going to want to bring this one all the way to the right this one all the way to the left so it looks the same as these and i'm going to do the same deal here with some conditional formatting i'm going to change this if it's greater than zero i want to be dark gray and i want it to be green hit okay we're gonna add another rule if it's not greater than if it's less than zero i'm gonna make that our gray i'm gonna make this red so there you go we're going to hit done all right now we're going to move on to portfolio balances and one thing i noticed here is i actually don't want it going all the way to d so i'm going to unmerge it and then i'm gonna re-merge just to c and then we will bold it we will make it how about 14 and we'll put it right in the center so that looks good and now for everything here i'm going to want all of this i'm going to highlight all of this i'm going to want everything centered and i like bolding everything i think bolding everything makes it look nicer i'm actually going to make it a little bit bigger i'm going to make it size 11. yeah i like how that looks and size 11 kind of it just fills everything out so now what i'm going to want to do is go down to my unrealized gains well actually i guess all of these right here i'm going to want to do some conditional formatting with these rows and do the same thing with the greater than or less than so we got greater than or equal to zero i'm going to want it green hit done add a rule if it is less than zero we are going to want that gray and we're going to want this red hit done you know what i'm going to take these four rows right here and i'm not going to have it centered i lied i'm actually going to have it to the right just so it kind of separates these two so you can see okay these go in line with that and then because this has this own thing right here it just kind of makes it easier to see guys we are almost done here thank god what we have to do now is we we'll we'll put in the charts and then we're going to add some borders so the first chart that i'm going to want to do is going to be the balance breakdown and this is a pretty easy chart it's from my old tracker i really like to see my cash value and my position value i'm going to highlight these four cells right here positions cash value my positions dollar amount my cash dollar amount we're going to go to insert chart it's going to create a chart we're going to want to change that to a pie chart and from here i'm gonna want to customize it we'll start with legend no not legend we'll go to right here title text so i'm gonna want to name it balance breakdown so now it's up there but i don't like the positioning of it so i'm going to bold it i'm going to change it to wide and i am going to center that and i want the text to be our off-white because now we're going to change our background so here's our background color i'm going to change it to our lovely gray that we have border i'm going to hit none or you know what no with border i'll do white because that's what we're going to kind of make all of our borders to make everything look good so once we have that what else do i need to change i need to change the cache value we really can't see that so if you double click on that it'll just kind of open to where it is so we could make that our off-white i'm also going to make that wide because that's easier to see and then you know what i'm going to want to go back to balance breakdown and make this a little bit bigger so let's see what 20 looks like it's already at 20. 24. maybe it could be a little bit bigger how about 30 30 might actually be too big so we'll jump to 24. i'm going to want to shrink this down move this in a little oh but i want to see cash value so maybe right about there oh we could definitely make this probably smaller this way this is where i want to fit this right in here so from 16 down to 24 bring that out a little that looks pretty good to me could mess around with the finer details later but that looks good now what i want to do is create the portfolio performance and that is going to be we're going to want to go to the positions tab all right we're going to want to go to the conversion tab and we're going to go to insert chart we're going to have a chart here obviously this is a bunch of information that we don't want so right here we want tickers as our x-axis we want cost and then we're going to want value so we could delete purchase so remove delete price so we want cost and gain so we're going to delete value we're going to add a series it's not here so click on this and here's our gain we'll just kind of click right there let's click ok boom there is my gain this is what i want to see we want to change the title i'm going to change the title to portfolio performance that is spelt very wrong there we go portfolio performance we're going to do that right in the middle i'll change it to wide we're going to want to center that and same deal we're going to want to make the text our off-white and then find our background we're going to make it that dark gray look at that that's looking good we're going to make that our border white we're going to want to change this color to the off-white and again i'm going to use wide right here same deal off-white wide we're gonna have to change all of this just everything to off-white just so we could actually see what's going on here now we can change the color of all of these so i'm gonna want this to be that orange that i keep using and that's looking pretty good to me i think that's everything that i want i'm going to x out of that what we're going to do is we're going to click on this hit control x and then we're going to go to dashboard and hit control v and boom we paste it right in there we could delete it from here now because now we have everything right here want to delete our tickers there we go we could x out of that now we could fit that right into here widen that a little there we go that fits perfectly into place starting to look good now the very last thing that i have is actually i'm going to widen the a a little bit so then this could be wide end so the very last thing before we add the borders is right here all of these i'm going to want to right-click do some conditional formatting and what i'm going to want is i'm actually going to do the background i want the background to be green if the text is greater than or equal to zero hit done add another rule and then we're gonna want it our red if it is less than zero hit done we could x out of that we will bold it and we will center it all right now guys up one last thing right over here i want that red i'm gonna change it to orange as well just so it matches with the other chart all right the very last thing we have now is just to add our borders so i'm going to highlight all of this we're going to go to border i want to make sure the border is nice and thick like we've been using before we're going to want that off-white hit that boom there's that border portfolio balances going to get its very own border all right and then we want to section this off right here so this is gonna get a border this is gonna get a border as well we will add a border over here okay and now we can kind of move this up we're going to want this to have a border all right i'm actually going to move this right now just so i could add a border where this is going to go all right now let's move this back into place resize it you can move this back into place bring it up a little bring that over and boom guys we are done how long was that i'm probably gonna guess this video ended up being two to two and a half hours long but guys we are done we have everything that we possibly need now what we have is the dashboard history positions and conversion just to show you that conversion works with everything let's go to eur that all changes our dashboard changes as well you can see my account value is now only 90 000. now watch if we change it to usd 94 000. so that seems about right because the euro is a little bit stronger than the dollar right now price wise so that's all updated the only thing that you have to change if you are not in america and you are dealing with euros or whatever currency you got to change all the currency but besides that we are getting the actual conversion rate again if we jump over to history and if i go and buy a stock that is in a different currency like for example let's say i go and buy into l-o-n-n-g we'll hit buy we'll say i don't know i buy a hundred shares of it at i don't know what the current price is i think it's around maybe we'll say 900 this is 900 gbp well actually it's going to be nine so you see right here it says 9000. uh it's in gbx and now this shows that it converts over to gbp because for some reason we can't look at anything in gbx we can only look at anything in gbp for google finance let this all load there you go there's everything in gbp we'll change it back to usd so there's everything in usd here's our lon ng you can see the price updates for usd and if we go over to dashboard now it's in there see that and everything is dynamic everything that we possibly want to see is in here guys again thank you for sticking to the end we now have an updated master portfolio tracker with everything that we need to possibly see in it for conversion with different currencies we got bitcoin we got ethereum we got this sick new dashboard that could see the biggest loser and biggest gainer got your day return right up here all the major indexes up here guys if you like this video please smash that like button let's bring this algo to the moon bring this video to the moon so everyone can see it create an awesome portfolio tracker like this if you haven't subscribed already please do so oh look at this i want this centered i want it all bold there we go that looks better guys seriously thank you for watching all this you guys all rock i hope you like this video and as always guys i will see you in the next one

Channel: Think Stocks

Views: 48,512

Keywords: google sheets portfolio tracker, how to track my stocks in google sheets, portfolio tracker in google sheets, google sheets stock tracker, google finance portfolio, google finance function in google sheets, excel portfolio tracker, tracking stocks in execl, how to track stocks in excel, portfolio dashboard in google sheets, track international stocks in google sheets, track crypto in google sheets, stock tracker in google sheets, how to make a portfolio tracker in google sheets

Id: T47pgIS67_M

Length: 150min 45sec (9045 seconds)

Published: Fri Apr 30 2021