In this video I will show you how you can get live stock and ETF price data in
your Excel 365 spreadsheet
Excel is probably the most useful tool for investors and for traders and
Microsoft has made it a little bit easier for us to get live price and
other company and etf information in our spreadsheets I'm going to demonstrate
using the spreadsheet on the screen at the moment how we can do that so I'm
going to put a list of some stocks and in my portfolio here I'm going to put
Twitter, Amazon, IBM, Google, Oracle and Ford and
Pfizer the next thing I need to do is to tell the spreadsheet that these are
stock type data so I will highlight them I'm going to go on to the data tab and
click Stocks and we see it's recognised all of them as Stocks I've got a couple that
aren't quite right here I put in Ford here and it has auto-filled that as
Forward Industries I'm just going to change that to F Ford ticker and it gets
the correct stock now if I want to add more to this I can do so I could put in
JP Morgan Bank of America again we need to make sure that it's recognizing
the correct one so there we have a selection of stocks to represent a
portfolio now what price data or what data might we want well I'm gonna say I
open high low close change
volume what about price earnings as well so I can do this by highlighting the stocks
and clicking on here and then just choosing what I want so I want open next
thing I want is the high and I want the low and I want the close which is the
same as saying the price because of course that will change when the markets
are open I want the most recent change I want the volume and finally let's have
a look at price earnings so there we have a table and whenever the markets
are open this information will be updated the information is taken from
Morningstar and it is quite close to the market not close enough to trade on but
it is pretty close and accurate information now what we can do here to
make this slightly more usable is we can convert or we can rather change this
into a table so I'm going to just click on insert table and we've got a table
now before I do anything else on this I'm
gonna show you how we could do exactly the same with ETF's now you may trade
ETFs you may invest in ETFs or you may just be interested because they give you
an idea of a broad swathe of the market I've got a few
ETFs here click on data click on stocks again and it has correctly identified
all these ETFs now all I want to do is take some of this data so for my ETFs
I'm just going to literally copy and paste the tables that I entered above and
because I just copied and pasted those I can do exactly the same for the
actual data itself and highlight this double-click
highlight the whole thing click insert table and there I have
within a matter of minutes I've got two tables one showing my stock portfolio
the other one showing my ETF portfolio now of course because we have this as a
table we can filter it and sort it so we might want to know if we're looking for
value we might want to know which is the lowest of our price earnings you can see
here it is Ford Pfizer and Bank of America and which is the highest not
much surprise there to see that Amazon.com is 92 on price earnings of
almost 92 now for ETFs we can have a look at what is the most recent change
of course the open high low close data we could also have a look at some
different things I can add some new fields in so if I highlight this again
click on my tab here what about the 55 week low and the 55 week high and you
can see now that it is automatically adding this data including the title
into my table so if we have this we can work out a 52-week stochastic which is
a useful way to find out the relative strength of the different
ETFs I can demonstrate that now and this would be current price minus the low put
that all in brackets and divide it by the current high minus the low close the
brackets I'm going to change this to 52 week stochastic
you can see here we now have a measure of relative strength and this is very
useful in case you're looking if you want to invest on strength you want to
invest on weakness you're looking for momentum you are looking for which area
which market is performing better so I can again sort this smallest to largest
we can see that the FTSE developed market is extremely weak at the moment
and the strongest performing is the QQQ's Tech following the Nasdaq-100 what else
can we do now we have this in a table we can go into our table tools you may want
to change the style else we can do we can insert a Slicer here so we could
actually pop this here for this is the one first stocks I have a look at stocks
have I just wanted to have a look at Amazon Alphabet Amazon thought I wanted to have
but in another one here another slicer I go into table tools design insert slicer
and I want to select it by ETFs I've got another one here so if I just want to
have a look at the queues or the S&P and if we had a lot more data this would be
a useful way to drill down into the table and understand what it is telling
us but this makes a great daily dashboard you can see at a glance from
your own desktop in your own format exactly what information what market
information you would like to see if you enjoyed this video and you would like to
learn more about using Excel to analyze your trading and investment strategies
to back test your trading strategies and also to manage your trading strategies
please hit the like button please subscribe to this channel
for more information about trading the financial markets using Excel please go
to www.tradinformed.com