馃攳
Get Latest Stock Data in Excel & Create Your Own Stock Portfolio - YouTube
Channel: Leila Gharani
[0]
If you'd like to use Excel
to get stock information
[3]
or to calculate your portfolio value,
[6]
you can do that directly
from within Excel.
[9]
You don't have to copy and
paste or externally import
[12]
this information.
[14]
If you're an Excel 365 subscriber,
[16]
you already have access to this.
[19]
It's in the data tab in the
group called data types.
[23]
Now they're also referred
to as rich data types.
[26]
Now this can sound a bit strange
[27]
but it's going to make sense
in a second once you see it.
[31]
So I'm going to set up a stock portfolio
[33]
and use the stock data
type to get information
[37]
about my portfolio and see how it's doing.
[40]
Let's get to work.
[41]
(bright music)
[46]
To set up the portfolio,
[47]
I'm going to use data types in Excel.
[50]
You can get there by
going to data, data types.
[53]
And we have the stock data type here.
[56]
In addition, I have
geography and organization.
[59]
Now I'm using the 365 version of Excel.
[63]
Notice when I hover over stocks here,
[65]
it tells me it can convert
cells with company names
[68]
or ticker symbols, like MSFT
to get current stock info
[73]
like price exchange and more.
[76]
So let's give it a try.
[78]
I'll do what it says.
[79]
I'm going to type in MSFT in
there just to see what we get.
[84]
Once you have the company
name or the ticker symbol,
[87]
click on stocks.
[89]
This is going to try to convert
this to a rich data type.
[94]
Now, currently it's not converted
[96]
because it has a question mark.
[97]
It doesn't know which
type of exchange we want.
[100]
Is it the NASDAQ, Santiago,
Wiener Boerse and so on.
[105]
You can select the one that you need.
[107]
So I'll go with the
Microsoft Corporation here
[110]
and I automatically get this
little house or institute icon
[114]
together with the name
and the ticker symbol
[118]
and the exchange.
[119]
Now this here is a rich data type.
[122]
This means that if I
hover over this icon here
[126]
it tells me show card.
[127]
Now, when I click, I
get a lot of information
[131]
about this data, more than
what I see in the cell.
[135]
So here I have the current stock price.
[138]
Last trade time, the
change, changing percentage.
[143]
52 week high, 52 week low.
[145]
Price earnings ratio, and a lot more.
[148]
You can even see the number of employees,
[151]
the headquarters and industry in here.
[154]
Now, what if you wanted to
add any of this information
[157]
to your cells?
[158]
Well, all you have to
do is hover over this
[160]
and you get this plus
icon, extract price to grid
[163]
just click on it and we get the price
[166]
and decide if we want
to add more information.
[169]
So you can go back here
and add something else.
[173]
So let's say this time
I want to get the high.
[177]
So I'm just going to click
on the plus and it adds it
[179]
to the next cell.
[180]
Now notice when you go in that
cell you have a formula here.
[185]
So all of this is dynamic,
it equals B3.High.
[190]
This means that you can
write formulas to extract
[193]
parts of this data as you need.
[196]
So for example, if I go
here and then put in the dot
[200]
I automatically get this dropdown
[203]
and I can make my selection.
[204]
So if I want to see
the number of employees
[207]
I'm going to double click
on employees, press enter
[210]
and I get that dynamically in there.
[213]
If for some reason you
type in something wrong
[215]
or you're referencing a cell,
that's not a rich data type,
[220]
you're going to get a field error.
[221]
So for example if this was referencing B4
[225]
and I press enter you get this.
[226]
Always press Control + Z to go back.
[230]
Now, another way you can
add additional information
[233]
to this side, is by clicking
on this card up here.
[237]
You then get your options
shown in a list type of format.
[241]
So I can add headquarters
and it pops in automatically
[246]
in here.
[247]
Now the good thing about this is that
[249]
they don't have to be all together either.
[251]
So you can have this
sitting somewhere else.
[254]
This can also be on another sheet
[257]
so I can press Control + X here.
[259]
Let's go to this sheet
and press control + V
[263]
and the information comes with.
[267]
Okay.
[267]
So now that we're clear
on the basis of this
[270]
let's quickly set up our stock portfolio.
[273]
Okay, so let's assume I have Microsoft.
[277]
Now this time, I'm just going
to type it in like this.
[279]
Microsoft then Google.
[283]
I know the ticker symbol
should be GOOG, Amazon
[288]
and let's add the Royal Caribbean Cruise.
[294]
Let's say you typed it in like this.
[296]
Now I want these to turn into datatypes.
[299]
I'm going to highlight
this and click on stocks.
[302]
It's going to try to
figure it out for each one.
[305]
It has correctly figured out these.
[308]
For this one, it didn't get it right.
[310]
So here in this side, I can try it again
[314]
and let's see if it finds it like this.
[317]
They give me a selection.
[318]
This is the one I want.
[320]
I'm going to click on select.
[322]
If you want another exchange
[324]
and not for example the
NASDAQ here for Microsoft,
[328]
you can right mouse click
go to data type, change.
[331]
And here you can update your texts.
[336]
You get different exchanges,
and then you can select it
[339]
from here.
[340]
In this case, I'm going
to stick with NASDAQ,
[343]
so let's close that.
[344]
Now I've added my stocks.
[346]
Let's say I want to get more
information about all of this.
[349]
I can select them all,
go to this card here
[352]
and get, for example, the 52 week high.
[356]
And I get that automatically
for all the ones
[359]
that I selected.
[361]
If you want this to be dynamic
[362]
so that you can add more stocks
[364]
and everything updates
automatically, it's best
[367]
that you turn it into
an official Excel table.
[370]
So I'm going to press
Control + Z to go back here
[373]
select my stocks and press control + T
[377]
or go to insert and insert a table.
[381]
This table doesn't currently have headers,
[383]
so I'm going to click on okay.
[385]
And update the header myself.
[388]
Now you can also adjust
the formatting of a table
[391]
so let's go and remove it so
that we can add in our own.
[395]
Go to home, make this bold,
[398]
add a thick bottom border to this.
[402]
Okay.
[402]
So now that this is a table
[404]
what I'm also going to do is
update the name of the table,
[407]
call it T stocks.
[410]
I'm going to add more information to this.
[412]
So click on the plus here and
let's get the ticker symbol.
[416]
So I'm going to scroll
all the way down to T,
[419]
here's the ticker symbol.
[420]
And I get that automatically in there.
[423]
Now I want to add in some inputs as well
[425]
because I want to add
information about the
[429]
number of shares I
bought and the buy price.
[432]
So let's add to those in shares.
[434]
Notice it automatically
is completing my sentence.
[437]
It thinks that I want a
part of this rich data type.
[441]
If I just click on this and
say, yes, that's what I want.
[445]
It's going to retrieve it for me.
[447]
But that's not what I want in this case.
[449]
I want to input manually
here, so I just want shares.
[454]
This is going to be input
so let's highlight this
[456]
and select the light yellow color here.
[460]
Then I want to add the buy price.
[463]
I want this to also be input,
so let's apply the same color.
[468]
Here is going to be my portfolio costs,
[472]
which is going to be this multiplied
with this and press enter.
[477]
And because it's a table it's
using the table header name,
[481]
so it's using structured referencing
[484]
and automatically
copying the formula down.
[486]
I don't have to pull this down.
[489]
This is calculated so I'm
going to update the color
[492]
and make it light green.
[494]
Okay.
[495]
So let me just add in my shares
[497]
and the buy price for each
year to get my portfolio costs.
[503]
Okay, so I've added my information in.
[505]
Now let's also update
the formatting of this.
[510]
What we want to do is get the
current price of the stock.
[514]
It would also be good
to have the 52 week high
[517]
and the 52 week low.
[519]
So let's add that to our table.
[521]
All we have to do is just
go somewhere in this table
[524]
until you get the card
icon showing to the side,
[527]
click on it and add what you need.
[530]
I want the price, I also
want to get the 52 week high
[534]
and low is good information to have.
[537]
So let's scroll up, 52 week high
[540]
and let's add 52 week to this as well.
[545]
So now that we have this information,
[547]
let's calculate our portfolio value.
[550]
I'm just going to make this smaller
[552]
so that we can see it
better on the screen.
[555]
Here it's going to be portfolio value
[558]
which is going to be the current price
[561]
multiplied with the number of shares.
[564]
The moment I press enter,
[565]
everything is copied down automatically.
[569]
Now I want to find out for my position,
[572]
is it up or down?
[574]
I need to compare the
current portfolio value
[578]
with my portfolio costs that
minus this, and then I'll know
[582]
if I'm making money currently
or I'm losing money.
[586]
So for the Royal Caribbean
Cruises, I've lost
[590]
because they had a big
drop in their prices.
[593]
But you can also add
conditional formatting to this.
[597]
So let's just highlight this, go to home,
[599]
conditional formatting and add a new rule.
[603]
I want to format only cells that contain,
[606]
if the cell value is less than zero,
[611]
I want to add a fill color
that's in light orange
[616]
and click on okay and okay.
[618]
In this case, only this
one got highlighted.
[621]
Now let's also organize this a bit better
[623]
so we can read these.
[625]
Select this column under
home go and wrap the text.
[629]
We can also add a total row to this
[631]
and since it's a table,
I can easily do that
[634]
by going to table design and
placing a check Mark here
[637]
for total row.
[640]
That got automatically calculated.
[642]
I'm just going to update the
color to the light green.
[647]
Then I want to have a
total for this one as well.
[651]
So just click on the dropdown
beside it and select SUM.
[656]
Let's make that green as well.
[658]
For portfolio cost, I
also want to add a total.
[662]
So in total, I am positive.
[666]
But what if I want to add
another stock to this?
[669]
Well, because it's a table
it's really easy to do that.
[672]
I'm just going to right mouse click here
[674]
go to insert, table row, below.
[677]
Notice, I automatically get fields here
[680]
because the formulas are referencing this
[682]
and I don't have any stock.
[685]
Now let's say my stock is
the American Airlines Group.
[691]
The moment I press enter,
[692]
it tries to figure it
out and it got it right.
[695]
Everything was automatically added to this
[698]
because it's a table.
[700]
All I have to do is add my shares
[703]
and the price I bought these shares at,
[706]
and I end up with a
current negative balance.
[709]
My conditional formatting was
also automatically applied
[712]
because it's a table.
[714]
Whenever you need to refresh this,
[716]
all you have to do is go somewhere here,
[718]
where you have your rich data types,
[720]
right mouse click and refresh.
[722]
It's going to pull over
the newest information.
[726]
You can also go to data and refresh all
[729]
or refresh from here as well.
[731]
Also, if you ever want to
[733]
copy and paste the data
type somewhere else,
[736]
you can because all the
rich data type information
[738]
comes automatically with.
[740]
If you don't want it as a rich data type,
[743]
and you just want to convert this to text,
[745]
you can right mouse click go
to data type, convert to text.
[750]
Now, one other thing I want
to mention is the language
[754]
of the stocks.
[755]
It's not the same language
as the language option
[758]
that's connected to your ribbon.
[760]
So originally this confused me
[762]
because my ribbon and
language was English,
[764]
but my stock information was in German.
[766]
And that's because it's
coming from somewhere else.
[769]
If I go to file, more
options here under language
[774]
this is the language
that your ribbon looks at
[777]
and this here is the language
that the stock information
[781]
look at.
[782]
So if you wanted it in
a different language,
[785]
even though you have an English ribbon,
[788]
you can select that from here.
[790]
(bright music begins)
[793]
So that's how you can use stock data types
[795]
to get more information about a company.
[798]
Now it's not just related
to stock information
[801]
but also some additional
background information
[803]
about the company.
[804]
So this feature is available
for Microsoft 365 subscribers
[809]
and also for Excel on the web.
[811]
Unfortunately, it's not available
[813]
for older versions of Excel.
[815]
If you liked this video, don't
forget to hit that thumbs up.
[819]
And if you haven't subscribed
to this channel yet,
[822]
consider subscribing.
[824]
Thank you for watching and
I'll see you in the next video.
[827]
(bright music intensifies)
Most Recent Videos:
You can go back to the homepage right here: Homepage