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