Create a Stock Portfolio Tracker on Excel Using Live Data - YouTube

in this video we'll create this stock portfolio  dashboard where you can track how your investments  

are performing both in terms of totals and  individually comparing them to other stocks  

and you can download the file in  the description so let's get into it  

firstly in the transactions tab over here we've  got all of the different buy and sell orders that  

we've made so far with the different the quantity  the price etc all the relevant information  

and suppose we bought some new shares in apple  for that we would just go ahead and right click  

from there you can go to insert and we want to  insert a row below that say we we bought it on  

the 18th so we'll just go ahead and type that and  then here we just want to type apple press enter  

that's going to auto populate for us and let's say  we bought 10 shares and the price for them so the  

price that we paid at the time was 165.07 that's  gonna auto populate and we're just gonna put usd  

here as well in case you're wondering what these  icons over here mean if you click on them you  

can start to see that the share information  of the particular company starts to pop up  

and it's quite a long description there so if you  want to use that format say for something else  

say i put walmart over here hit enter from there  you have to go to data and then you're just going  

to click on stocks over here it's going to ask you  for a data selector which is basically hey which  

stock market are you referencing in this case  let's go with the new york stock exchange now  

you can see that everything is in stock format  so if we click inside it all of the different  

features of that particular share will pop up now  that we understand the transactions tab let's hop  

onto the dashboard looking at the dashboard  tab over here we've got a table with all the  

financial information that we need for our current  holdings and just below that we're gonna have some  

visuals so firstly looking at the companies if  you go back to the transactions tab over here  

the problem is that some of them are duplicated  as you can see you've got apple more than once  

and so we need to find a formula to try account  for that so we'll use a combination of the  

unique the filter and the sum is function  to filter by unique company names that have  

a share count greater than zero firstly we'll  use a unique function so equals unique press  

the tab key and for the array we're gonna put  the filter in there press the tab key this  

array is gonna be the name of all the different  companies that we've got so all of these here  

as you can see it starts to say table 1 that's  because that's the name of this table over here  

press the comma key and for include we're going to  use the sumifs in here press the tab key and the  

sum range that's going to be all the different  units that we've got that's what we want to sum  

press the comma key the criteria range is going  to be that all of these here so all of the company  

names press the comma key and that first criteria  we're also going to take the companies close those  

brackets and we want those to be greater than zero  close those brackets and then close them again and  

hit enter and now you can see that the formula  is gonna spill down all the way to the bottom  

now we've got all the five different unique ones  then for the ticker you're just gonna go equals  

select the company name then you want to do the  number sign here put a dot and as you can see over  

here you start to get all this list and this is  basically because it's in stock format and you can  

select a bunch of different things in our case we  want the ticker so we're just going to type ticker  

symbol press the tab key and hit enter the reason  i put that number sign in front is so that it  

spills down all across and whenever we add a new  share say that's going to be accounted for as well  

for the industry it's the same idea so we'll  go equals select the company from there we're  

going to press the number sign dot industry press  the top key hit enter and there you go for the  

quantity we're gonna need a sum if of sorts to sum  the units so we'll go equals some ifs press the  

tab key the sum range is gonna be the units like i  mentioned ctrl shift down arrow comma the criteria  

range is going to be all the company names down  there comma that we want them to equal to these  

ones over here close those brackets and hit enter  now that's going to auto populate for us as well  

then for the current price we're just gonna select  the company name and we're gonna press the number  

sign again dot and then price press the top key  hit enter for the market value it's gonna be the  

price times the quantity so we'll go equals select  the current price press the number sign multiply  

by the quantity press the number sign again and  hit enter then for the percentage breakdown this  

is going to be the weight of that particular stock  in relation to the overall portfolio so we'll go  

equals get the market value press the number sign  and then divide that by the sum press the tab key  

of all of these closes brackets and hit  enter and that should auto populate as well  

the dollar day change is going to be the price  of today minus the price of the previous days  

so for this we'll go equals we're gonna select  the current price press the number sign there  

and we're gonna minus that by the we'll select  this one over here and then press the number sign  

dot and we're gonna put the previous price  previous close price there press the top key  

and then we're gonna have to multiply those so put  the multiplication there times the quantity sold  

so this one over here and then i'm just gonna have  to put a bracket over here at the front as well  

and hit enter for the gain or loss which is going  to be unrealized in this case it's the current  

share price or the current market value minus the  market value when you bought it so for this we'll  

use a sumifs as well so we'll go equals firstly  we'll select the market value which is the current  

one over here put the number sign and then we're  going to go minus the sum ifs press the top key  

and the sum range is going to be the transaction  amount which is going to be this range over here  

press the comma key and the criteria range is  going to be that the companies so all of these  

over here press the comma key should equal these  ones over here and then hit close those brackets  

and hit enter lastly we'll add the 52 week high  and low which is gonna be quite straightforward so  

it's just gonna be this one and then number sign  dot the 52 week high press the tab key hit enter  

and same thing over here so we'll select it number  sign dot and then we'll go 52 week low hit enter  

nice that table with all the current holdings  is going to be at the core of this model  

from there let's add a few summaries over  here so things like what our portfolio value  

is i've just gone ahead and added these three  titles over here for all the summary financials  

and for the portfolio value it's just going  to be the sum of all of the different holdings  

so it's going to be all of these over here and hit  enter similarly for the dollar day change that's  

going to be the sum of all of these over here hit  enter and lastly for the gain or loss we can just  

copy this and paste it over here press alt hoi to  auto fit that and now that's looking more like it  

now that we have all the relevant financials let's  work on some visuals for them so firstly for the  

portfolio breakdown let's go ahead and select  the different tickers over here so ctrl shift  

down keep pressing the ctrl key and here we want  to select all the different percentages then we're  

gonna go under insert we can go to recommended  and the first one should be the pie chart  

hit okay from there we'll remove the title and let  me go ahead and fast forward how i reformat this

based on this pie chart over here we can start  to see that amazon is a whole 40 percent of our  

portfolio but this isn't a very diversified  portfolio after all the only asset class  

are stocks so if you're interested in minimizing  risk by diversifying away from just owning stocks  

you can consider allocating a portion of your  portfolio into alternative assets which according  

to this jpmorgan report they're no longer  optional so if you want to gain access to  

alternative asset classes masterworks is the  platform for investing in contemporary art  

and also the sponsor of this video they let you  access exclusive investments from names like  

banksy monet and other iconic artists for just  a fraction of what billionaires pay to purchase  

as for how they perform although past performance  doesn't guarantee future results contemporary art  

prices have outpaced the s p 500 total return  by 164 from 1995 to 2021 and have exhibited the  

lowest correlation to equities from nearly any  major asset class since 2020 masterworks has sold  

three paintings with each returning over 30 net  irr to investors and their new offerings usually  

sell out in hours if you want to get in on it  early all you have to do is go to masterworks  

dot io create an account check out what they have  and invest in their offerings and if you want to  

skip their wait list go to a special link in my  description next to the pie chart over here it  

would be nice to see who the biggest gainers and  losers are for us to try to see if we should sell  

or buy a particular stock more so for this we'll  go ahead and select all the tickers so ctrl shift  

down keep pressing the ctrl key and here we'll  select all of the gains and losses then we'll  

go to insert let's go ahead on the recommended  charts again and let's go for it this one the  

first one seems fine hit okay there and let  me go ahead and fast forward the reformatting

great based on this chart we can really start to  tell that tesla seems to be the best performing  

chair for us while amazon over here is in losses  currently now while we did this chart over here we  

can also go ahead under the gain and loss column  so go ahead and select it ctrl shift down and we  

could do a conditional formatting like a data  bar for instance click on that first one as you  

can see this kind of does a similar thing if you  prefer it one thing that this dashboard currently  

isn't telling us is the historical performance  so is amazon just currently having a bad week  

and that's why it has losses or is it because it's  been consistently a bad investment for us for this  

it'd be nice to have some sort of a trend line  where you can see the historical figures so as  

you can see over here we have a bit of a setup  where over here we're going to create a data  

validation so we'll go under data go under a data  validation here and we're going to create a list  

that list we can source it from over here is going  to be all the different tickers that we've got  

hit okay there you go and now you can see hey  what's going to be the performance of tesla  

and over here we're going to create  some sort of a chart to show that  

same with the time periods let's say we go to  data validation and we're going to create a list  

and here we're going to put them manually  let's say we go from one month to three months  

to six months and lastly 12 months and  hit ok there now we're going to have the  

drop down as well with this formatted we now  have to actually find the historical figures  

so for this let's go to the charting tab so  control page down all the way to the bottom here  

here under the actual part we're firstly going to  link all of them so we'll go equals thicker that's  

gonna be the stock over here hit enter and for the  month we'll go equals and just link it to the time  

period over here and hit enter and for this we're  just gonna create a formula so we'll go equals  

stock history press the tab key and this is going  to give us the history of the particular stock so  

the share price history this case the stock is  going to be tesla press the comma key for the  

start date we're actually going to have to go  back three months that's going to be the start  

date for us based on this figure over here so  we're gonna use the e date function press the  

top key the start date is gonna be today press  the top key there close those brackets comma  

and it's gonna be minus three months so  three months going backwards so we'll put a  

minus sign then we'll put the three month sign  over here close those brackets comma and the  

end date is just gonna be today we'll close the  brackets close the brackets again and hit enter  

as you can see over here everything just auto  populated for us so we've got the close price  

for a total of those three month period with this  information we can go ahead and create a visual  

for it so let's go ahead and select everything  there ctrl shift down and then ctrl shift right  

we're gonna go under insert go on the recommended  charts we're just gonna select the first the  

second linear one actually hit ok there and we're  just going to drag it all the way to the top so  

press the ctrl x go ctrl up arrow all the way to  the top here and let's paste it over here ctrl v  

from there to reformat it firstly we're just going  to copy it and take it on to the dashboard tab  

so right over here and this is where i'm  going to reformat it let me fast forward that

great now that this is reformatted for the  title over here we want to make it dynamic  

such that it changes with the months so we'll go  equals we're going to click on that time period  

then we're going to go to the ampersand quotations  press the space and then we're going to put month  

trailing closing prices close the quotations  and hit enter now it says three months trading  

closing prices if i go ahead and change this say  we change it to six months then the title changes  

and so do the financial figures same thing over  here if i change the stock price say to apple  

all of the historics are gonna change for that  six month period one final feature here would be  

to compare stocks so suppose we were looking  to buy amazon shares and we also considered  

walmart as an investment but we didn't and so  we want to see which actually performed better  

and whether it was the right decision for us for  this we're gonna be using the comparison over  

here that we've got down below so firstly let's go  ahead and put walmart over here press enter as you  

can see this is not in share price format at the  moment so we'll go under data and go under stocks  

hit enter there that's going to do it for us  and over here we said we wanted to compare it to  

amazon then let's go to the charting so control  page control page down all the way to the bottom  

here we're going to link this one so for the  comparison to walmart hit enter and for the months  

we can just link it to this one over here then for  this part over here it's just going to be the same  

so we'll just copy it and paste it over here as  you can see this has linked to the right one now  

for the spacing issue over here just double  click up top that's going to do it for  

us and lastly let's create a visual for this tool  so firstly we'll select just this date over here  

as well as the price keep pressing the ctrl key  and from there we want to select all of this time  

period here so click on this first one and then  ctrl shift down arrow then we're gonna go under  

insert go under line chart and we're gonna do a  2d line chart from there press ctrl x and then  

we'll take it all the way back up here and let's  just paste it down over here now the problem with  

this chart is that it's all on one access and as  you can see the share price of amazon is actually  

a lot higher than the share price of walmart so  it's not a very good comparison instead what we're  

gonna do is we're gonna click on this blue one  right click on it and go to format data series  

and we're gonna put it on a secondary axis close  that and now that's gonna look a lot more like  

it where on the one hand you've got amazon and on  the other hand you've got walmart and you can see  

the different trends here for the series name  let's go ahead and edit that so right click and  

go to select data from there we're gonna edit the  first series one name and we're gonna link it to  

the amazon actual hit okay there and for that  second one go under edit again this time we  

wanna select the walmart over there hit ok and  then hit ok again scroll down to see how that's  

looking like now then ctrl c and we're going to  take that over to the dashboard tab just down  

below over here ctrl v and let me go ahead and  reformat this i'm just going to fast forward it

with this chart formatted one thing to keep in  mind is that amazon shares and walmart shares  

aren't all that directly comparable because they  have a different number of shares outstanding that  

being said you can still compare the overall  trend and it seems like over here walmart has  

been the better performer recently if you ever  want to change the share you want to compare to  

you can just go ahead and click on it and say  we type microsoft hit enter and just go under  

the question mark and it's just going to ask  you to select the right market in this case we  

want the nasdaq that's fine for us and now  you can see the overall trend there as well

that's a stock portfolio tracker if you ever  want to refresh the data you can go under data  

and refresh all that's going  to refresh the share price etc  

for more on excel check out this link over  here to create an interactive dashboard  

or this other link over here to  learn more about finance evaluation  

hit that like hit that subscribe if you  liked it and i'll catch you in the next one