馃攳
How to build your stock portfolio with Excel | Investing 2020 - YouTube
Channel: Investing with Perspective
[0]
hey guys welcome to another episode on
my perspective in this video I'm going
[4]
to show you how you can use Excel to
build and track your own portfolio of
[10]
course a lot of brokerage app on your
phone may do a lot of this for you
[14]
but not all of it right so this is where
Excel can provide that additional value
[20]
in my previous video I showed you how
you can create a stock watchlist using
[26]
Excel and in this video we'll just be
building on that concept the link is
[30]
posted in the description below if you
haven't seen it already again make sure
[34]
you have office 365 with the latest
version of Excel or you'll need to do
[40]
this on Excel online using Excel we're
able to perform calculations and
[45]
generate a lot of graphs that can really
provide insight to your portfolio and
[50]
the market in general say for example
you want to know the extent of
[55]
diversification in your portfolio and
using Excel we can show you not only
[62]
diversification across stocks but the
different levels of composition within
[67]
the industries that you're invested in
well in all honesty this depends on what
[71]
kind of investor you want to be right do
you want to be more of a hands-on person
[76]
or do you want to just let it do its own
thing and I'm sure you guys are familiar
[81]
with this with all else being equal the
theory is that the more effort you put
[86]
in the greater the rewards I mean at the
end of the day you want to know where
[92]
you're putting your hard-earned money
right? well I hope that's a yes for you
[96]
so first we'll look at how you can set
up your portfolio in Excel and then
[102]
we'll see how we can play around with
the data to address the issue of
[106]
diversification and last but not least
performance doesn't really mean anything
[111]
unless there's a comparison so I'll show
you how you can benchmark your
[116]
performance against the general market
first off for this portfolio I'll use a
[122]
hypothetical one off the top of my head
I'll write the first ten stocks I think
[128]
of so don't read too much into my
choices these are purely hypothetical so
[133]
we'll turn these tickers into the stock
data type
[141]
the readily available attributes I
would put are the ticker symbol which
[146]
will come in handy later on the current
price, industry, market cap, and the p/e
[152]
ratio
[155]
obviously these attributes are not
enough so we'll need to add in our own
[160]
attributes such as quantity, market value
cost and total gain/losses the
[168]
total gains/losses will be the
overall percentage gained or lost
[172]
relative to your cost in order to
produce these columns we'll need to
[177]
build a separate sheet called
transaction history so the sheet will
[181]
have to buys and sells you've made so
far and the idea is to feed this
[186]
information through so the portfolio
table is updated automatically this
[191]
transaction history table would include
the date, ticker symbol, the action
[196]
whether it's buy or sell, quantity, price
and the total amount, so again
[202]
hypothetical transactions now let's go
back to the portfolio table for quantity
[208]
we can now feed this from the other
sheet we don't really want to manually
[213]
add that quantity because that would make
the table pointless for this we can use
[218]
the SUMIF formula basically this
formula means if a certain criteria is
[223]
met then we want the sum of it this
formula takes three inputs the range is
[229]
the cells in the transaction history
table that have the ticker symbol as this
[234]
is the reference Excel uses to add up
the transactions in this case the
[239]
criteria we're using is the ticker
symbol because we want to add up all of
[243]
the quantities of the transactions that
relate to that ticker symbol and the
[248]
last input is fairly easy to understand
the sum_range is the cells and the table
[254]
that you wish to add up which is the
quantity column this table is really
[259]
useful when you make a lot of
transactions and you're wanting to
[262]
calculate the average cost of your
position so using the SUMIF formula again
[267]
we can calculate the total cost of your
position
[273]
then you can simply take the total cost
and divide that by the quantity so once
[280]
we have those columns market value and
total gains/losses are just a matter
[285]
of using formulas so market value would
equal the current price times the
[291]
quantity and the total gains/losses
would be dividing the difference between
[296]
the market value and a total cost by the
total cost
[304]
before we can start analyzing
diversification we need to add another
[308]
column which calculates the percentage
of each stock in the portfolio this is
[313]
simply the value of the stock divided by
the total value of the portfolio okay so
[319]
we've got a fair bit of data now let's
go ahead and make some charts so we'll
[324]
go and select the relevant columns in
this case we'll select the column with
[328]
the ticker symbols and also the
percentage of portfolio column so if you
[333]
don't know what type of chart you should
use you can head over to the insert tab
[337]
in the ribbon and click on recommended
charts where Excel will give you some
[341]
suggestions based on your data go
through the list and see what type of
[347]
chart you like the aim is to pick a
chart that tells the story right in this
[352]
case you can see that the pie chart is
probably the best but you never know
[356]
some people might like bar charts but
personally I'd go for the pie chart the
[361]
default style of the chart is a bit hard
to read so we'll change the design up a
[365]
bit we want each of the sections of the
pie to be clearly labeled so we'll go to
[371]
add chart element under the chart design
tab and click on data labels to select
[377]
data call-out now the chart is looking
much better if you want to chart by
[383]
industry you'll have to create some sort
of summary table as you'll realize that
[388]
Excel won't group the same categories
together if we use the same method as
[393]
before the simplest way to create a
summary table is to use pivot tables
[398]
first we'll select the table and
navigate to the insert tab in the ribbon
[402]
and click on to the table on the very
left so now you can start constructing
[408]
the summary table so we're basically
recreating the table but with a more
[413]
powerful sorting function for example
let's grab the industry field and
[417]
percentage of portfolio field
so let's create this pie chart
[425]
you realize that if you switch the
industry field with the stock field
[430]
Excel will produce the same chart as
before
[436]
And another function that could be useful to you is adding
slicers there are two kinds of slicers a
[442]
general one and a timeline one adding
slicers give you the flexibility to carve
[447]
out the data you want using an industry
slicer you're able to delve further into
[453]
the specific compositions within each
industry but what does this all mean
[458]
you may be wondering I'm sure we all
know the saying that we shouldn't put
[463]
all our eggs in one basket on the face
of it, it may seem very straightforward
[468]
but it all depends on how you look at it
right? what type of basket are you
[473]
holding? what is it made of?
are there cushions in your basket? I know
[478]
all this may sound abstract what I'm
really trying to say is how well do you
[483]
know your investments? simple questions
you can ask yourself do I know what the
[489]
company does?
how many revenue streams does the
[493]
company have? is the company itself
diversified enough? now that brings me to
[498]
my next point do you want to perform
better than the market? I'll show you
[503]
a way you can benchmark your performance
against the overall market using Excel
[509]
so our aim is to compare our performance
with the markets' let's just compare our
[515]
year-to-date performance with the S&P
500 but first you'll need to export the
[520]
year-to-date balances for your portfolio
next we'll need to download the
[525]
year-to-date data for the S&P 500 and
navigate to historical data and make
[532]
sure the date is YTD apply the filter
and click download
[539]
we'll be using the adjusted close
price so you can just copy this column
[544]
over to the same sheet with your
balances since the two sets of data are
[549]
on different scales we need to
standardize or rescale one set of data
[554]
to the other here we make the first cell
the same then after that it's just a
[560]
simple exercise of multiplying the
previous day's balance by the percentage
[565]
change
[569]
now we can plot them on a line chart to
see how they fare with each other bear
[575]
in mind that the appreciation and your
account balance may include dividend
[579]
reinvestments or any deposits, withdrawals
that were made during the year if that
[585]
were the case then you may need to
adjust for those events to get a more
[589]
accurate picture of your portfolio's
performance in short this is a quite a
[594]
good way to see whether you need to make
adjustments to your portfolio
[598]
composition of course it will depend on
how much risk you can take for example
[603]
can you tolerate your portfolio to
decrease more than the market during bad
[608]
times but be compensated for by a larger
increase when there's good times ahead?
[614]
well that was an intense session on
Excel I hope I haven't worked your brain
[619]
too hard though I definitely encourage
you guys to build something similar as
[623]
it can really increase your knowledge
and exposure to the equity markets
[627]
although it doesn't have the full set of
data but it can still do some pretty
[632]
simple but powerful analysis again I
hope you've enjoyed this video feel free
[638]
to post any suggestions or questions
that you may have down below and I'll
[642]
see you next time in my next video
Most Recent Videos:
You can go back to the homepage right here: Homepage