doesn't that just look beautiful what's
up guys my name is Michael and today
we're going to be doing a tutorial on
how to build your own stock tracking
spreadsheet personally I really enjoy
using these spreadsheets because I find
it helpful for me to keep track of all
my investments in a single place and it
provides some useful tools and analytics
that can help me in my decision making
processes today we're gonna start with a
relatively simple spreadsheet and in the
future we can build in more features to
that by the end of this video you should
be able to recreate a very similar
spreadsheet for yourself and be able to
use that for your stock investing I'm
going to go at a slower pace so everyone
can follow along and I'll also have
timestamps in the description of the
video linking to which portion of the
project we're working on at a certain
time alright so without further ado
let's jump into the computer for this
spreadsheet I'm gonna be doing it all in
Google sheets which is Google's
equivalent of Microsoft Excel so I'm
using Google sheets because it's free
with a Google account and it links up
very nicely to Google Finance so in
building a spreadsheet usually just
start one piece at a time
add the pieces that you know you're
going to have so in this case let's
start out with our stock tickers so
these will be the actual tickers that
are traded on the stock exchange and
we'll use this information to link up to
Google Finance so for instance I'm just
going to input a few stock tickers here
so we've got Microsoft Facebook Vanguard
index fund Vanguard bond index fund and
then Apple over here so relatively
straightforward five stocks right now
but in this next column we'll put our
shares the number of shares we've have
in each stock and the reason I'm putting
these on the left hand side and we'll be
adding more columns over here on the
right hand side is these will be the two
two of the main things that we'll be
inputting into the spreadsheet the rest
will be formulas that will actually
calculate based on the inputs so I'm
just going to make up some numbers here
and one other thing that I'll add is
I'll actually add a a cash position here
you don't have to do this but
I will for my sake if your I'll have a
Robin Hood account some of your account
will likely be in cash if it's not
invested so you can keep track of that
there okay so this is our start and it's
not going to look pretty at first but
that's okay we're going to worry about
at the end usually when you're building
things out it can take some time to get
it really where you want and then once
it's everything's all in the position
that it should be for the spreadsheet at
that point it's a lot easier to do the
formatting and things like that
rather than have to redo that as you're
building the spreadsheet so next I'm
going to add a third input which is
average average cost so this is your
basically your unit cost basis so your
cost basis would be like the total
amount that you put in for your number
of shares and the unit cost basis or
average cost would be over all the
shares you've purchased all over that on
the security what's the average cost you
paid so once again I'm just gonna make
up some numbers here okay so basically
these three things right here are your
inputs so depending on what your
portfolio is you'll have different
stocks you'll have different amounts of
each stock and you'll have different
prices paid for those stocks those are
the main inputs at this point and from
here we can begin building out the rest
of the spreadsheet and so yeah there's
no formulas in here you just input that
actual value but from now on we're going
to start going into some actual inputs
so the first of which will be the price
and this will be the live price of each
of these stocks so the way that we're
actually going to get this is we're
going to create a formula that'll go to
Google Finance and look up the stock
price of each of these stocks so in
excel or in Google sheets what you'll do
to do a formula is you'll do equals and
this indicates that the program that
it's actually a formula and there's
various tools and formulas you can use
but won't really be using is Google
Finance it'll create a drop-down list of
formulas that will match what you're
typing
so in this case we're gonna do Google
Finance when you click on that it
provides some helpful tools into what
are the inputs for this function so for
Google Finance we have ticker and that's
gonna be the main one we're gonna add so
instead of you know you might be tempted
to type Microsoft here in this case it
need quotes quotes indicates it's a it's
a text so you could do that and it gets
you the stock price live which is fine
but to make the spreadsheet actually
dynamic and so you don't have to
manually update this what we want to do
is have this formula instead of typing
in Microsoft we want it to reference
this text here in this cell so to do
that we're going to actually get rid of
this and we're going to have it
reference c7 so you can either type in
c7 here or you can just click once the
drop down goes away you can just click
on the cell you want to reference so hit
enter after that and then this gives you
the live price so to get this formula to
the other cells you could you know
retype Google Finance and just do it all
over again
but that's rather inefficient and very
tedious so there's a couple things you
can do one is if you're have a formula
that you're dragging down through to
fill up a column there's a little button
here in the corner you can just click
that and drag it down and it will drop
down the formula through all of those
and so this one isn't actually this last
cell here f12 isn't really accurate
because because it's it's looking up a
symbol CAS H which is just as some
security I don't know which one but it's
not exactly what we're looking for that
was kind of small so we'll blow it up so
it's a little bigger for you and there's
another way you can actually do that
which is instead of using this little
dot and dragging it down what you can do
is highlight the column that you want to
fill and then you'll hit control D or I
think on Mac it's like command D and it
will auto
go ahead and fill the rest of the
formulas down for you so if you look
over here it's got Google Finance C 11
which is what we want so now in this
column we can do a calculation of our
gain and loss so for this time actually
going to do total gain and loss so of
all the Microsoft shares how much are we
up on all of them so to do this we'll
just do another formula so it's going to
be equals so give the gain or a loss we
want to take our price and then subtract
what we actually paid so if the price is
over what we paid we've got a gain and
if not we've got a loss so that'll be
the total gain or loss per each share
since the prices per share and the
average cost is per share so want to
multiply that by the number of shares
that we own it's in this case $40 total
gain for that so on another tip for
highlighting is that if you hold shift
and then use the arrow keys you can
create a box that select cells just by
moving the arrow keys and holding shift
so by holding shift and having g7
selectively them and hitting down four
times I can then create that column and
then hit control D and it fills the rest
of the column for me and then also
double clicking these adjusts the column
length so you can do that we'll do that
at the end to make it look nice but for
now we'll just focus on the actual
insides so next we can do percent gain
so this will express the gain as a
percentage so we'll do this by equals
our price divided by our average cost
minus one and then we'll bring that down
so this formula here is just the price
you've used to calculate it percent and
change I know it said lightly formula
stuff for the end but I'm gonna change
this to percent just so it's easy to see
easier to read alright next we can add
total equity so this is how much your
current value is in each position the
total equity you have in the stock so
it's just going to be the number of
shares you have
so equals number of shares for each
position times the current market price
you've got that for Microsoft and then
fill the rest down so I'm gonna actually
just hard code one is the price for each
each dollar search dollar is a $1 sounds
redundant but that's just what it is for
this and we'll fill that all in there
Oh average cost should be average cost
should be one okay now if we drag these
down yeah that'll that'll look about
right all right so we're game the
spreadsheet coming together it doesn't
have any visual tools yet but we'll be
able to use this information going
forward to create some of those graphics
but there's a few more things like to
add first so one thing that can be
helpful is in the context of your entire
portfolio it's nice to see just offhand
what percent of your portfolio is in
each position to give an idea of how
diversified you are and how concentrated
each of your positions actually is so
for this will do a portfolio percent so
for this we want to use total equity so
for that that is how much your position
is in that stock and we want to compare
that to the total positions you have in
all of your stocks so we want this as a
proportion so it's going to be I seven
our total equity and Microsoft divided
by and now we want the one a total for
everything so there's a couple ways we
could do this we could actually want to
delete that you create a total down here
and the way we can do that is we do
equals and then do s um for some and
then parenthesis and then we'll just
select a range so in this case we're
just selecting this whole range and the
parentheses hit enter and this is the
total equity for our entire portfolio
now so now if we want to do a percentage
we can just do that I seven divided by
our total portfolio value so in that
case we get 16% so not too bad now if
you drag this down and fill it out
you'll get some errors and that's okay
but there's a lesson here to be learned
which is that when you drag it down
these references are dragged down
exactly the same as their relative
position so when you drag it down this
cell now references this one which is
correct which is what we want but it's
no longer referencing the total
portfolio value so we want to lock that
in so it doesn't move and the way you
can actually do that is you can change
what these are called relative
references that are relative because
their relative to the the cell that
you're writing the formula in so if you
drag this down they'll move but you can
also create absolute references which
are references to a specific cell that
won't change and you can do that by
adding a dollar sign directly in front
of both the letter and the number of the
cell so in this case we want to always
reference that specific cell the total
portfolio value and we don't want that
to move but we do want the individual
stock equity to move down so we do that
we now have our percentages and you can
just quickly check this by highlighting
everything and then in the bottom corner
where my face is covering down there you
can see it's at a hundred percent so so
that's a good check there so we've got
four fully percent one other thing we
can add is a total cost so for total
costs that'll just be your unit cost
times the number of units and then bring
that down so for this total we can
actually bring this across and just like
when you dragged it down you can also
drag formulas right and so these this
now gives us the totals for each of
these columns so there's a hundred
percent there and then this is the total
amount that you've actually paid into
the portfolio all right so this is
actually not too bad so far I'm going to
clean it up a little bit I know why you
usually say leave this till the end but
this let us give us a little more space
to work with
in terms of where we're going to put our
actual visuals and graphs so basically
what I'm doing here is I'm just double
clicking this little column and it'll
auto adjust the size to fit the largest
text that's in that column so this just
keeps things a little more neat and tidy
and reduces the whitespace within your
spreadsheet and actually I'll take that
back since we're almost done with what
we're going to be doing for this video
we can work on a little bit of the
formatting to make it look a little
nicer so in this case I like having the
numbers formatted as numbers that's fine
but for any dollar amount value I
actually like using an accounting format
so you can highlight those and then go
to more formats and they've got a
currency but they've also got an
accounting and I like like the
accounting format because it has
negatives in parentheses rather than
just having like a negative sign in
front I think that's a little lease for
me it's a little easier to see for these
you won't really have negative prices so
you don't need to worry about that but
for the gains and the total loss you
could so looking ahead we've got these
are would all be formatted the same way
so to save time you can select
everything you want if you make a one
selection here you can hold ctrl or the
equivalent key and Mac and select other
sections of the spreadsheet that you
want formatted the same so that way I've
got all three blocks selected I only
have to format them once instead of
doing it three times so for this we'll
go two formats and then we'll do
accounting and I actually want the
negatives to appear red so what we're
going to do is we're going to edit that
format we're going to go to formats more
formats custom number format and up here
this gives you an example of what this
coded format will look like and it's a
little complicated here honestly more
complicated than I initially expected
but I think what we want is for
I'm tryna this if it's red there we go
basically putting the red in this
section here says hey if it's a negative
number apply all these play all these uh
nevermind by all these formats it's
really annoying by all this format and
then having the red in front of me and
so I'll color it red as well so we'll
apply that now they're finally red that
took way too long to do but we've got
that sat for me I like adding a few
little borders as well you can go up to
here borders and have a border and then
maybe a total outside border and then
one here for the totals at the bottom so
just do a bottom border here alright so
that's starting to come together a
little bit zoom out just a tad okay so
this is looking not too bad
so next up if this is going to be a
spreadsheet that you're looking at for
total sort of overview of your portfolio
and you have like all your positions in
here then this is a good place to kind
of have a dashboard of what your current
like portfolios looking like each day so
for that I sometimes like having four
big numbers up top that have sort of key
things that I'm looking for in my
portfolio one of those would be like the
total value of my portfolio so what
we'll do here is we'll just grab this
total value right there and have that
nice and big at the top since I know
what that is I'm actually going to
format it as just a number remove the
decimal and then I'm just gonna make it
nice and big that's a little too big for
me but there we go
so that's total portfolio value right
now so total portfolio value
so when you open your spreadsheet you'll
know exactly what your portfolio is at
that moment in time another thing I like
to add is my percent so this would be
what percent I'm up or down on the
portfolio which would just be your total
equity divided by your total cost and
then again - one format that is a
percent
I like having only one decimal place and
then what we'll do is we'll make that a
little bigger as well that'll be our
portfolio gain or loss and so so this
will give you a good overview of your
portfolio if you want to add more
positions what you can do is basically
click on a row and then right click and
then insert a row above and you'll do
that and you can add now you have room
to add more positions in here so trying
to think let's do Pepsi let's say you
just bought H here's a Pepsi and current
price is 116 so let's say you bought it
for a hundred bucks so you got it on a
good deal from there all this the rest
once you've inputted these three things
all the rest is formulas so basically
just highlight this entire row and move
it down one and then you can do ctrl D
alternatively you can just hide this
entire row and then click this little
dot again pull it down it will
auto-populate fill the rest notice also
your portfolio total values updated
portfolio total gain percents updated so
the benefit about doing a spreadsheet
like this is you know you only have to
input three things for your portfolio if
you add a new position or change the
position and the entire thing updates
and you know life is easy life is good
okay now we're going to zoom out a
little bit because what I want is for
some of this white space here to be
filled up with some nice or
visualization tools and graphs that can
help give me a quick snapshot visually
at least some
relatively visual learner and person so
I like having those kinds of tools just
easily see what's going on so what we'll
do a couple of the graphs that I like to
do is have just a pie chart of what my
portfolio is in each percentage so we'll
start with that one
you can go to insert and then go to
chart so right now it's got no data but
we can place the chart where we want it
to be in our spreadsheet so for this
chart we want to do a pie chart and then
we can select our data range so the
first date range you can select by
clicking this little grid select data
range then I'll ask what data and you
can just drag in this case this whole
column here and then click OK and
that'll be the label so that'll be the
actual tickers and actually we're going
to redo that we're going to add we're
going to include c5 which is the it goes
from ticker all the way down so that'll
include this in there and that'll be
actually the title for the title for
that data so for the values we want the
total equity so we're going to select
all of that okay and poof we've got our
graph right here so you want to use row
5 as headers notice that the label is
updated to ticker and the values the
total equity values so from this now
it's a lot easier to see although you
can see it in the portfolio percent here
that you know looks like about quarter
put portfolio is that a total stock
market index almost quarter is Pepsi but
it's a lot easier to see visually okay
you know I've got this much in vti
damage and Pepsi Wow my Apple position
is much smaller than the others is that
something that you intended if not now
you can visually see that if so
yeah you've checked that with the visual
graph so this is one thing I really like
at a glance plus also gives you a quick
view you know Microsoft Facebook Apple
you know if you add up all those
percentages that's a fair amount within
just like the tech space maybe that's
what you want and that's you know your
portfolio is heavily weighted towards
tech but it could also give you sort of
a heads up flag hey my portfolio is
weighted a pretty significant amount
towards tech know is that what you
really want or is that a risk that your
being now susceptible to being sort of
over concentrated in a certain sector
it's up for you to decide but you should
at least be aware of it and this tool is
a great way to sort of see that from a
quick glance another thing I like is
having my gains and losses in sort of a
bar chart all in all together in one
chart so for that will do another chart
here so I've got stacked column chart
here we'll want to grab some data so
it's asking what data and to make this
simple I'm just going to grab all this
data here and we're just going to take
out what we don't want or don't need so
the x-axis should be the ticker so it'll
be the ticker symbols so the x axis will
be the ticker symbols and actually want
to grab the cash so I'm going to redo
the data range we're gonna grab
everything in here okay so now I've got
the cash so the x-axis we want to be the
ticker that's fine what we really want
is to have our total cost and then our
gain and loss to be showing because
that'll give us an idea of how much we
actually put in and then how much of
each position is a gain and how much of
each is a loss so going back to the
graph we want to get rid of that
averaging that cost get rid of that
price give of that we want total gain
and total loss we don't want total
equity doing too
percent okay so now we've got total
costs and total gain or loss I actually
don't want the order to be that order so
we're going to reorder it so we're going
to take that we're going to copy that
actually going to cut that and we're
going to put it over here so that out
we'll swap the range hit enter there we
go
so now I've got the total cost in blue
and the total gain or loss in red so I
like this graph as well because similar
to the pie chart where it gives you an
idea of how much is in each position
here you've got an idea of not only how
much is in each position but how much
you put into each position and then how
much of the gains are from each position
so if you do this on your own portfolio
you'll likely see that you know some of
your positions might have a significant
portion of the gains for your portfolio
that's okay and some might have more of
the losses from your portfolio or some
might have losses spread out but this
gives you a visual reference to see you
know how each position is doing relative
to the others
one last thing I'll add up here is I'm
just gonna go and do a function and I'm
going to do equals today open
parentheses close parentheses all right
so now we've got quick dashboard to our
portfolio how we're doing and a few
visual tools at the bottom so hopefully
this will give you guys a good start
into how to build your portfolio so this
is with Google sheets I definitely would
encourage you to try this out if you
haven't already I personally really
enjoy using this and it's automatically
updates anytime so I check and some even
on my email every day it's a handy tool
to just take a peek at every once in a
while in the future if you guys like
this kind of content I can do another
tutorial building this spreadsheet out
further and adding a few more additional
features so just let me know in the
comments if you're interested in that
and if you've got any questions or
technical difficulties if you're
building this spreadsheet just let me
know and I can help you out in the
comments there in the future I plan to
make a free spreadsheet course for you
guys which won't
clewd downloadable versions of all these
spreadsheets so if that's something
you're interested in you can sign up for
my private email list and you'll be the
first to know once that's available and
you'll have the link for that when I get
that done I hope you guys found that
useful thanks for watching and I will
see you in the next video
[Music]