in this video you learn all you need to know about getting livestock data from the internet
to excel so first we'll look at how to add and analyze the stock data then we'll build a simple
stock portfolio and finally we'll compare our investments visually let's get into it first let's
learn to add and analyze real stock data so over here in this Excel file you can see that we have
the stocks on our watch list meaning those that maybe we're considering buying and next to them
we have the ticker which is basically the trading name for the company alongside some other features
that we would like to have in order to make a better analysis so for these you can actually go
and get them from the internet but that's a bit tedious and Excel does have a built-in solution
for it we're just gonna select all of the tickers so Ctrl shift down here
then we're gonna go towards the data tab and under data types you should be able to find
stocks so just click on that and just like so you can see that we have all of these icons
on the side double click up here so it resizes a bit what we want to do is let's first select
the apple apple one here this icon click on it and you can see that we get a ton
of information ranging from the price to the currency and the description for the company
now in order to fill in all of these lines next to it first we'll select all of them so
Ctrl shift down and you can see this plus sign over here so that's what we want to click on
from here the first thing we want is the industry so we're just gonna click on it and just like so
we're gonna get all that information then we also want the price so we're just gonna go on
that drop down again and try to find the price from here it should wait towards the bottom
like so same thing with the market cap which is basically the number of shares outstanding
times the price click on that and lastly for the p e ratio this is a useful ratio
for people that are typically looking to invest so let's click on that one
and just like so we have all of the information we wanted in real time from the internet now to
analyze this data let's first convert it into a table so we're just gonna control a and hit Ctrl t
hit okay there that's going to convert it to a table for us let's hit not noun for that and you
can actually go ahead and remove all the table style if you want just to hit clear there that's
going to remove it for us so let's suppose that we're interested only in companies that are in
the software space so to filter for that we can just right click and go to filter by selected
cells value that's the one we're looking for so click on that now you can see we only have all the
software companies that maybe we want to invest in similarly just press Ctrl Z to go back there
let's suppose that we only want those that have a p greater than 25. to do so we just need to
go towards this drop down click on it and under number filters let's say we want greater than
25 and just hit OK there just like so we have that analysis done as well awesome now moving
on to step two which is creating a simple stock portfolio so over here you can see that we have
all of the stocks that we currently own so those that we've invested in alongside the price that we
bought them at back in the day and the quantities of the number of shares that we that we bought
now we basically want to know if we're making any money of these so it's the life price greater than
the price we bought it at if so we're probably making money so first let's go ahead and select
all of the stocks so Ctrl shift down then we want to find out the live price which is the current
price so just click on the plus sign again and hit on price and we're gonna find it there and so we
want to calculate all of the portfolio value here so the total cost for us is simply the price we
bought it at so this one multiplied by the number of shares that we bought similarly for total
value that's going to be the live price times the quantity so quantity times the life price
and finally the gain or the loss is simply going to be the total value minus the total cost and
hit enter there now to drag this down just go shift right shift down arrow and hit Ctrl d
great and really you can start to see here that it's primarily been quite bad for us all of these
Investments haven't gone too well other than one not to try to identify that a bit better
and make it stand out we can select all of them and then go to conditional formatting
from here if it's less than zero then we want to highlight it in red as that's obviously not good
so highlight cell rules we're gonna go to lesson
zero we want that to be in in red like so that makes sense to us so hit OK that's
how you would make a simple portfolio where you can see how your Investments have gone
whether you made any money of them and they're gonna stand out especially if they're negative
and thank you to nordpass for sponsoring this video If you ever find yourself stuck at work
waiting for others to give you a password note pass could be the perfect solution for you they
provide a secure way to store passwords and share them seamlessly with your team and through their
autofill feature you won't be wasting any more time typing or looking for passwords
also with their user-friendly interface you can easily add or remove passwords users and more
finally they also offer tools like a custom password generator so you don't waste time and a
password Health Checker to make sure your company is confidential information is safe so if you want
to keep your business efficient and secure check out northpass business with a three month free
trial using Code Kenji explains the link for that will be in the description below again that's a
three month free trial with the code and the link in the description below alright back to the video
lastly what if we want to see the overall trend of a stock maybe we decided to buy Google but
we were split between Google and EXO mobile so we really wanted to find out if Google was actually
the right investment instead of investing in Exxon Mobile now to do so we can use the stock
history formula and over here you can see that we have the bought one so we bought Google exactly
one month ago say and we didn't buy EXO mobile that's their ticker one month ago as well so
with the stock history formula it's gonna allow us to go back in time and see the price trend so
for it we're just gonna go equals stock history hit the top key there and the stock for us that
we're interested in is Google comma the start date is basically how far back do we gonna do
we wanna go in this case it's back one month so to do that we're gonna actually use another formula
which is the e-date formula hit the e-date hit the top key and the start date is actually today
minus one month right so we're gonna go equals today hit the top key if you close those brackets
that's gonna be today's date comma and this is the number of ones so in this case we want to go
backwards so minus one month and we'll select the one from here close those brackets comma key and
the end date is when it finishes so we wanted to finish today as we still hold that stock
see so here today close the brackets and now simply close the brackets again and hit enter
and there you go just like that we have all of the data from a month ago all the way to today
same thing with the exome mobile so we're simply going to copy the whole formula so Ctrl C and
Ctrl V if we look inside of it just to make sure you can see that it's now selecting Exxon Mobile
that being said in table format it's not very easy to actually tell if it's done
well or not so instead we're gonna try this out so let's select all of the data here so Ctrl a
and we're also gonna keep pressing the control key so we can still select other data and we're gonna
select all of the close the close prices over here like so once you have all of this selected
we're gonna go to insert under recommended charts we can find the line chart hit OK
let me move this around a bit but the problem is that we don't actually need such a big range
so let's select it right click and go to format access towards the bottom there
and here for the minimum we really don't need it to be that low let's say we put a minimum of 80.
hit enter and now we can really start to see there's a bit of a trend here but we need to
rename this so we know which one is one so just click on this one right click go to select data
for this first one go on edit and you can see that it's currently selecting all of the values
here so this one must be for Google so for the series name we're simply going to click
on Google there and hit enter hit OK there so we have one that's Google and so the other one has to
be EXO mobile so under close here we'll go to edit and change that one the series name there to XOM
and hit enter hit okay there and now you can see that we have the overall trend and it looks like
EXO mobile was actually a lot better of an investment and we actually missed out on it
and what's really cool here is that the way we set up the formula it's fully Dynamic so if I
go ahead and change this to a two month period that's gonna all change like so similarly if I
go ahead and change this to Apple say as I was a company that I was actually considering you
can see that all of that is gonna change the price changes and so it's all Dynamic there to
build a budget tracker on Excel see this video over here or this link over here if you want to
learn all about Finance evaluation hit the like And subscribe and I'll catch you in the next one