[Music] hey guys it's Dan here welcome to my
channel in this video I want to show you how you can create a stock watchlist
using Excel I wanted to do this video because I know many of you are following
the market very closely and I think this is a great tool that can help put things
into perspective there's no doubt that the market is reacting to events much
quicker than before so without keeping a close tab during times like these you
may find yourself missing out on some great deals so first of all for desktop
applications make sure you have office 365 and the latest version of Microsoft Excel
which is 2019 as it won't work otherwise if you don't have office 365 then you'll
have to use office online okay so let's get into it
so on a blank sheet write down the tickers of the stock you wish to follow
say Apple Microsoft Visa Lululemon just to name a few next convert the
tickers into the stock data type this can be done by navigating to the Data
tab and the ribbon and clicking on the Stock data type while you have the active
cell on the tickers sometimes you have to tell Excel which Stock Exchange to
pull the data from for example when you type in Apple it'll explicitly ask
you to choose the exchange you want click on one of the options on the right
and you'll be able to see which stock exchange relates to
now once you've done all that format the information into Excel's table style by
navigating back to Home in the ribbon and clicking on Format as Table the good
thing about having it in Excel's table form is at the table content of
subsequent column entries can be auto populated so for a customized stock
watchlist you might want to display data such as price, industry, the p/e ratio
market capitalization, the 52-week high and low, average volume and maybe even
the daily percentage change now one way to add these attributes is to go to the
top right corner of the table while having the active cell in the table
another way is to click on the grid temple icon where a card will pop up and
you'll find an icon to the right of every attribute when you hover your
cursor over them once you have the columns you want you can start
performing some filtering, sorting and additional calculations the watchlist
will ultimately end up being quite a useful screener for example you may want
to sort the list by p/e ratio and as a rough guide you can also
calculate how much the stocks have fallen from their 52-week high to
potentially search for undervalued stocks
using a simple formula we can obtain the percentage change of the drop that is
we'll divide the difference between the 52-week high and the current price by
the 52-week high price another way is to switch the denominator around which
would give you the potential gains for the stock if you were to invest in it
now so instead we'll divide the difference by the current price
once we've done that we can use the formatting rules to sieve out stocks
with at least 20% upside and with this set up in Excel's table style, information
will automatically be generated when a stock is added to the watchlist let's
add Google and Exxon Mobil for example and you'll see that for Exxon Mobil
it has been highlighted as there's over 90% upside as of the
time this video was made and whenever the data is refreshed which can be done
under the data tab in the ribbon the watchlist or screener
will automatically reflect the most recent prices and information so
depending on the type of information you want the data can be manipulated
strategically to give you informative data but one obvious limitation to this
tool is that it doesn't show dividend information so for dividend investors
out there you'll have to import them manually but all in all this is still a
relatively easy-to-use tool to help you perform high-level analyses of
stocks so there you go you can now follow these steps and create your own
stock watchlist but on top of this you should obviously do some research on
the stocks that you understood so I hope this video was helpful if you want
to see more related videos please give a thumbs up or click subscribe below
thanks for watching I'll see you next time