hey guys welcome to another episode on my perspective in this video I'm going
to show you how you can use Excel to build and track your own portfolio of
course a lot of brokerage app on your phone may do a lot of this for you
but not all of it right so this is where Excel can provide that additional value
in my previous video I showed you how you can create a stock watchlist using
Excel and in this video we'll just be building on that concept the link is
posted in the description below if you haven't seen it already again make sure
you have office 365 with the latest version of Excel or you'll need to do
this on Excel online using Excel we're able to perform calculations and
generate a lot of graphs that can really provide insight to your portfolio and
the market in general say for example you want to know the extent of
diversification in your portfolio and using Excel we can show you not only
diversification across stocks but the different levels of composition within
the industries that you're invested in well in all honesty this depends on what
kind of investor you want to be right do you want to be more of a hands-on person
or do you want to just let it do its own thing and I'm sure you guys are familiar
with this with all else being equal the theory is that the more effort you put
in the greater the rewards I mean at the end of the day you want to know where
you're putting your hard-earned money right? well I hope that's a yes for you
so first we'll look at how you can set up your portfolio in Excel and then
we'll see how we can play around with the data to address the issue of
diversification and last but not least performance doesn't really mean anything
unless there's a comparison so I'll show you how you can benchmark your
performance against the general market first off for this portfolio I'll use a
hypothetical one off the top of my head I'll write the first ten stocks I think
of so don't read too much into my choices these are purely hypothetical so
we'll turn these tickers into the stock data type
the readily available attributes I would put are the ticker symbol which
will come in handy later on the current price, industry, market cap, and the p/e
ratio
obviously these attributes are not enough so we'll need to add in our own
attributes such as quantity, market value cost and total gain/losses the
total gains/losses will be the overall percentage gained or lost
relative to your cost in order to produce these columns we'll need to
build a separate sheet called transaction history so the sheet will
have to buys and sells you've made so far and the idea is to feed this
information through so the portfolio table is updated automatically this
transaction history table would include the date, ticker symbol, the action
whether it's buy or sell, quantity, price and the total amount, so again
hypothetical transactions now let's go back to the portfolio table for quantity
we can now feed this from the other sheet we don't really want to manually
add that quantity because that would make the table pointless for this we can use
the SUMIF formula basically this formula means if a certain criteria is
met then we want the sum of it this formula takes three inputs the range is
the cells in the transaction history table that have the ticker symbol as this
is the reference Excel uses to add up the transactions in this case the
criteria we're using is the ticker symbol because we want to add up all of
the quantities of the transactions that relate to that ticker symbol and the
last input is fairly easy to understand the sum_range is the cells and the table
that you wish to add up which is the quantity column this table is really
useful when you make a lot of transactions and you're wanting to
calculate the average cost of your position so using the SUMIF formula again
we can calculate the total cost of your position
then you can simply take the total cost and divide that by the quantity so once
we have those columns market value and total gains/losses are just a matter
of using formulas so market value would equal the current price times the
quantity and the total gains/losses would be dividing the difference between
the market value and a total cost by the total cost
before we can start analyzing diversification we need to add another
column which calculates the percentage of each stock in the portfolio this is
simply the value of the stock divided by the total value of the portfolio okay so
we've got a fair bit of data now let's go ahead and make some charts so we'll
go and select the relevant columns in this case we'll select the column with
the ticker symbols and also the percentage of portfolio column so if you
don't know what type of chart you should use you can head over to the insert tab
in the ribbon and click on recommended charts where Excel will give you some
suggestions based on your data go through the list and see what type of
chart you like the aim is to pick a chart that tells the story right in this
case you can see that the pie chart is probably the best but you never know
some people might like bar charts but personally I'd go for the pie chart the
default style of the chart is a bit hard to read so we'll change the design up a
bit we want each of the sections of the pie to be clearly labeled so we'll go to
add chart element under the chart design tab and click on data labels to select
data call-out now the chart is looking much better if you want to chart by
industry you'll have to create some sort of summary table as you'll realize that
Excel won't group the same categories together if we use the same method as
before the simplest way to create a summary table is to use pivot tables
first we'll select the table and navigate to the insert tab in the ribbon
and click on to the table on the very left so now you can start constructing
the summary table so we're basically recreating the table but with a more
powerful sorting function for example let's grab the industry field and
percentage of portfolio field so let's create this pie chart
you realize that if you switch the industry field with the stock field
Excel will produce the same chart as before
And another function that could be useful to you is adding slicers there are two kinds of slicers a
general one and a timeline one adding slicers give you the flexibility to carve
out the data you want using an industry slicer you're able to delve further into
the specific compositions within each industry but what does this all mean
you may be wondering I'm sure we all know the saying that we shouldn't put
all our eggs in one basket on the face of it, it may seem very straightforward
but it all depends on how you look at it right? what type of basket are you
holding? what is it made of? are there cushions in your basket? I know
all this may sound abstract what I'm really trying to say is how well do you
know your investments? simple questions you can ask yourself do I know what the
company does? how many revenue streams does the
company have? is the company itself diversified enough? now that brings me to
my next point do you want to perform better than the market? I'll show you
a way you can benchmark your performance against the overall market using Excel
so our aim is to compare our performance with the markets' let's just compare our
year-to-date performance with the S&P 500 but first you'll need to export the
year-to-date balances for your portfolio next we'll need to download the
year-to-date data for the S&P 500 and navigate to historical data and make
sure the date is YTD apply the filter and click download
we'll be using the adjusted close price so you can just copy this column
over to the same sheet with your balances since the two sets of data are
on different scales we need to standardize or rescale one set of data
to the other here we make the first cell the same then after that it's just a
simple exercise of multiplying the previous day's balance by the percentage
change
now we can plot them on a line chart to see how they fare with each other bear
in mind that the appreciation and your account balance may include dividend
reinvestments or any deposits, withdrawals that were made during the year if that
were the case then you may need to adjust for those events to get a more
accurate picture of your portfolio's performance in short this is a quite a
good way to see whether you need to make adjustments to your portfolio
composition of course it will depend on how much risk you can take for example
can you tolerate your portfolio to decrease more than the market during bad
times but be compensated for by a larger increase when there's good times ahead?
well that was an intense session on Excel I hope I haven't worked your brain
too hard though I definitely encourage you guys to build something similar as
it can really increase your knowledge and exposure to the equity markets
although it doesn't have the full set of data but it can still do some pretty
simple but powerful analysis again I hope you've enjoyed this video feel free
to post any suggestions or questions that you may have down below and I'll
see you next time in my next video