Hello this is Mark from Tradinformed.com and welcome to this video on How to
Monitor your Stock Portfolio using Google Sheets in this video I will
demonstrate how we can use the inbuilt functions in Google Sheets to create a
very effective portfolio manager that is highly customizable to your specific
needs. Before I get into the video I would like to remind you that there is
more information in the notes below this video and if you want to see more videos
like this, including a series on using Google sheets for trading, please hit the
Subscribe Button and also use the Comments Box to give me feedback and
tell me what are the videos you would like to see me record. On the screen at
the moment I have a Google Sheets document and I've called it portfolio manager and
I'm gonna show you how we can quite quickly build up a portfolio in this
sheet so I'm going to put in some codes here these are quite standard. I'll start off
with Google of course Apple, JP Morgan, bank of America, Cisco, McDonald's so I've
chosen a few nice large cap stocks I'll put in Microsoft as well so we've got some
large cap US stocks in there now one of the great benefits and features of
Google sheets is firstly it is in The Cloud it is designed as cloud software
it's permanently connected to the Internet so we have that functionality
for a start and secondly because it is also plugged into a lot of the other
Google functions Google services and for stock traders and investors the
most useful one of those is Google Finance so we can put this in as a
spreadsheet function we're just putting Google Finance =googlefinance and
open a bracket and we just click here on this cell pointing to this code I'm
going to put a $ symbol in here to keep the same column close the bracket
and we will have the up-to-date current or most recent price I'm recording this
video before the US market opens so this is actually yesterday's closing price
once the markets are open this price is kept pretty close to the market, not
close enough of course for intraday trading, but it is pretty good for end of
day purposes and for longer-term traders and investors so I'll copy this formula
down and it will be updating the price for all these stocks so I'm going to
call this current price we can also get a lot more other data from Google
Finance and I'm going to demonstrate a few of those things now so we could get
the p/e ratio the earnings per share we could get the market cap we can get the
most recent change as a percentage and we could get the currency that the
shares are denominated in so if we wanted to get this data in what I would do is
copy this formula across here and put a comma in and I would point that to this
cell above it here this time I'm going to use the dollar on the 4 to make sure
we keep the same row and I can copy this information across
like so so for Google we have the price price earnings earnings per share the
market cap the most recent change as a percentage and the currency is US
dollars I'll copy this down and we're getting the information for all of these
markets now it might not seem very useful of course to have seven stocks in
a portfolio but of course there is a very large limits for these spreadsheets
and so you could monitor hundreds if not thousands if you particularly wanted to
now one of the things that I find interesting about being able to
customize your own portfolio manager is you could get different stocks that
don't normally sit together so you might be interested in these large cap US stocks
alongside large cap worldwide stocks so you could
put in Nestle you could put in Royal Dutch Shell you could put in HSBC and if we
add these to our portfolio we can see here that we're updating all the same
information we've got the current price earnings earnings per share market cap
and we've also got some different currencies here so we've got Swiss franc
and British Pound so you can see you could starting to get the advantage of
putting this into a spreadsheet format you could have US shares in one place
but maybe you want to have your banking stocks all in one place no matter where
they're domiciled in the world maybe you want to put your tech stocks in one
place your industrials your oil stocks and this is what we can start to do very
quickly and easily using Google Sheets we might want to use some of the color
functions of Google sheets to do things to make it easier for to see things with
such a small number of stocks we can very easily see but if you've got a
hundred stocks in here or a thousand stocks in here you might well find it
useful to be able to highlight things so if we want to look at earnings per share
as a percentage we're interested in that I wanna stick that as a percentage copy
this down here adjusting for the fact and this is a bit of knowledge that you
would need to have if you're looking at different stocks at different countries
in the world to denominate their stocks sometimes in
dollars or pounds sometimes in pence or cents and now we have earnings
per share as a percentage so we can do other things to make this much more
visible to us we can look at the price earnings ratio and we can compare them
to one another say we could put in a color scale and we could do it so that
the highest values come up as red for example so we could see that Nestle
Google and Microsoft are on the high side in this portfolio and that's JP
Morgan bank of America and Royal Dutch Shell are on the low side and this
would be the advantage as well of building a portfolio different
industries different sectors and seeing how the price earnings compares in the
sector we could do this exactly the same for here
we can add a new rule and color scale again we could do it the other way
around here looking at the highest values so we can see that JP Morgan Bank
of America all that Shell score fairly well so we could have an interesting
look at these higher values we're drawn to these higher values the
green scores and we can see stocks that we might be interested in purchasing or
increasing the weight of in our portfolio now I hope you found this
video useful on using Google Sheets to create your own portfolio manager please
check out the next video in this series which will be about how to set up and
monitor a relative strength trading strategy using Google sheets and for
more information about trading the financial markets please go to
www.tradinformed.com