Hi, Jason Morrell here.
If you want to show the price of a stock over a period of time, whether that's weekly or
monthly, yearly, or multiple years, then the Stock Chart in Excel is the tool to use.
It lets you not only plot the closing price of the stock, you can also show the high and
low points of that stock using what's called candle sticks.
Here's what it's all about.
So I've got a made up stock here for ABCXYZ company.
And depending on what you want to show, I've got two different sets of data here.
One is showing the OPEN price, the HIGH and the LOW price throughout the day, and the CLOSING price.
The second set of data that just shows the HIGHEST and the LOWEST prices and the CLOSING price.
So depending on how much data you have or what you want to show on your chart, you can
choose one or the other.
So let's say I start with the simplest one here.
So this is formatted table, which means I can select any cell in this table, go to the
INSERT tab, WATERFALL or STOCK chart.
And then STOCK chart.
There's quite a few options here.
You'll notice that the first one says High-Low-Close and that's what we're going to use.
The next one as a comparison is the Open-High-Low-Close, so that's where we need to use the other set
of data for that to represent that information.
You can also plot volume prices if you have that information available as well.
And these last two options here contain chart types if that's what you need.
So for this one, let's just use the first type there.
And because I've got a year's worth of data, you can see that it's really cluttered here.
So the first thing I'm going to do is just choose Select Data at the top, reduce the
amount of data down to just one week to start off with, just so we can get the chart the
way you want it.
And after that's done we can then expand the range of data, if we need to.
So click OK there, and this is your initial chart.
(I'm just going to put it on one screen so that the chart is next to the data.)
Now these things here are called candle sticks.
So it shows you the range of data, the price range, that the stock has been at during the day.
At the bottom of the stick is the lowest price it reached.
The top of the stick is the highest price it reached.
And halfway along, which we can't see at the moment is the closing stock price.
So if I right-click on any one of these candlesticks and choose Format the High-Low lines, this
will display a panel on the right hand side and it's taken me straight to the bit I need,
which is how I want to format these candlestick lines.
At the moment it just says Automatic, Automatic generally means black.
The color we can see is down here.
If you want to do a slightly different effect, you could choose a gradient line and that's
where it fades from dark to light, and if you look closely, you can actually see the
closing price now as a marker, tiny little marker, on each of those candlesticks.
You can also choose a solid line and choose any color you like for your candlestick.
If we change the High-Low options here and change it to say Series Close, you have the
options here to change either the line or the marker characteristics.
I'm going to focus on the marker and if I just, if I start off with the marker options
and I'm going to say Built In, you can change the type from a little horizontal dash to
a square or a diamond or triangle or any of these symbols you see down here.
Let's maybe choose a square and let's maybe supersize it a little bit so we can see it
clearly on the candlestick.
Just while we're here, I'm also going to set the fill color.
So Automatic again is just the default color.
We can change that to anything we like and let's turn the border off ... and that's how
our candlesticks are now looking.
So the block is your closing price for the stock and obviously the candlestick, the line
behind each one, shows you the range that the price was throughout the day.
If you want to show a trend line for your closing price over five days, or if you want
to, your highest or lowest prices, you can add a line to connect all the dots.
So just take any one of these closing price markers, double-click it.
It takes me into the sidebar here.
It starts off on the Series Options, but if you come across here and go to the Fill & Line,
under the Line Settings it's currently said No Line, but you can choose a solid line or
a gradient line or an automatic line and choose your color and all your other characteristics down here.
So you might decide, for example, to have a dotted line or a dashed line or a solid
line or thick line and you can change all those things down here.
Let's switch across to the other set of data.
So here we have an extra column of data.
We've got the opening stock price, the highest and lowest that it was throughout the day,
and the closing stock price.
So again, I'm going to select my starting range here, just a week's worth of data to
start with.
Go to the Insert tab, go to the Stock Price chart and choose the second option.
Now because the data is set up differently, it gives us a bit more information on the
chart itself.
(Let me just move this and just resize it so we can see it.)
So what you have is a similar system with the candlesticks showing the price range throughout
the day, but the block in the middle rather than just showing a square or diamond as in
a closing price, it shows you the opening and closing price.
Now where it's got a white marker here, that means the price rose throughout the day, so
it closed higher than it opened.
The black markers indicate that the price lowered throughout the day, so it opened higher
than it closed.
To change any of these options, you can either right-click and choose Format on the context menu.
Or you can double click here and it opens up the appropriate options in the panel if
you've got it open, or you can switch to these options anywhere in the panel.
You just do that by clicking this option here and choose the elements that you want to work with.
So here in the dropdown list we have Series Open, Series High, Series Low, Series Close,
and each of those can be set.
In the list here we have a couple of options called Down Bars One and Up Bars One.
So you can actually change the white and the black settings here to any color you like.
If it ended lower, you might want to mark it red to indicate that it went down.
If it ended higher, you might want to mark it blue to indicate that it went up.
And to do that you just go to one of these, it brings up the options for that element
you just selected.
And let's say we just change the Fill color and because we're changing the Up Bar options,
let me change that to blue.
If we change this option here to the Down Bar options and maybe I'll just set that field
color to a red.
So now on here you can see those changes being made.
If you can't be bothered with all this stuff doing things manually, you can always use
the preset options up in your Chart Styles section and this gives you various looks and
various designs.
Some of these are pretty good and even if there's not a perfect one, you may find there's
one that's pretty close to what you want.
Once you set up everything the way you need it, if you want to expand the current data
range, you can.
So all you've got to do at any point, is to go to the Chart Tools Design tab, make sure
you choose Select Data.
It brings up the current data range that's being used.
And the simplest way, rather than changing all these things individually, is just to
reselect and include the new data range.
So let's say we get two weeks worth of data and click OK.
You can now see the candlesticks for ten days rather than five.
Obviously the more data you select, let's say we choose a month's worth of data here,
the more data choose, the more crammed it's going to be.
That's just the nature of things, but don't forget you can always move the chart to its
own sheet and that way it's full size or maximum size and you do that by going to the Move
Chart option over here.
Make sure you choose New Sheet and give it a name.
You then get the full screen to play with.
So if you follow stocks and shares, maybe download some data or input some data and
just have a play around in Excel and see what you can't when there's lots of options available
for stock charts..
For more infomation >> Good Work(s) "Empower Me Every Day" Inspiration Leather ... - Duration: 6:26. 
For more infomation >> 배우 송하늘 조민기 성추행 폭로한 이유! - Duration: 3:29. 
For more infomation >> Bure : les tiraillements de Nicolas Hulot - DN - Duration: 6:32. 
For more infomation >> 大S瘦身十公斤效果显著，42岁的孩子妈似少女，跟汪小菲超恩爱 - Duration: 2:33. 
For more infomation >> 《歌手》2018KZ谭定安挑战格莱美金曲 一把吉他走心演绎 - Duration: 3:17. 
For more infomation >> Сырный суп с шампиньонами и мясом, суп с шампиньонами и сыром плавленым - Duration: 2:06. 
For more infomation >> A St-Barth, Johnny Hallyday n'avait pas fini de payer les travaux de la villa Jade - Duration: 2:48. 
For more infomation >> LEGO® Marvel Black Panther...
For more infomation >> Voici la lettre très ferme que Laeticia Hallyday vient d'envoyer à Laura et David - Duration: 2:03. 
For more infomation >> Les signes d'alerte du diabète que la plupart des médecins ne connaissent pas - Duration: 6:48. 
For more infomation >> Nathalie Baye prend la défense de Johnny : « Il est intelligent et il a un énorme talent » - Duration: 3:15. 
For more infomation >> Dany Brillant révèle l'âge tardif de sa première relation - Duration: 1:17. 
For more infomation >> Supporting Trump, Human Mannequin Reactions, Oregon | VLOG - Duration: 14:43. 
For more infomation >> Triệu Hồi JEFF The Killer Nghi Thức Liên Lạc Nỗi Sợ Hãi | 360hot REN Ghost - Duration: 11:08. 
For more infomation >> Affaire Johnny Hallyday : Le gros coup de gueule d'Henry-Jean Servat - Duration: 2:26. 
For more infomation >> Walter E. Williams - Liberdade De Expressão ( Legendado) - Duration: 1:54. 
For more infomation >> Lucu Apa Bodoh Try Not To Laugh P6 - Duration: 10:23. 
No comments:
Post a Comment