Dynamic Stock Chart

Programming and Web Development Forums - MS EXCEL - Microsoft Office Excel

Skip to content

Dynamic Stock Chart

MS EXCEL
Post a reply

    Next

  • 1. display multiple charts in a workshheet
    If I change the display resoution in Windows (Control Panel/Display/Settings) This appears to affect how my graphs display on the worksheet but only on a sporadic basis. I am a naive user and have lined the graphs up by using macros to record hand adjustments and then inseting then into the VB code for the sheet. My problem is that I can change the screen resolution a number of times and the graphs will line up OK and then I change it again and the graphs do not line up. I have a program that automatically charts data and I would like the output to be consistent across different hardware and dislay settings
  • 2. xy scatter chart-How can you make the axis equal for a 1H:1V p
    I really need it to be exact. I've used the method of stretching the graph, but to get it correct, you do end up printing, measureing, adjusting, etc. "James Silverton" wrote: > mark wrote on Fri, 19 May 2006 12:35:02 -0700: > > m> The xy scatter chart seem to allow you to change the scale > m> on the x and y axis independently and then automatically > m> stretches the axis (x and y) to fit the page. the result is > m> that the graph is not true scale in both directions. > > But you can make the units of x and y the same and make the > chart area square by clicking on it and moving the markers. I > don't know how to do this automatically but I can do it > satisfactorily, IMHO, by eye. I suppose I could hold a ruler to > the monitor to make it more exact. > > James Silverton. > >
  • 3. ChartLabeler Questions
    I have been using the ChartLabeler from appspro.com that by the way it is fantastic!!!!!!. in all my charts to add difference kind of labels However, I have noticed that every time I have new data to load in my chart the chartlabeler does not update automatically the new points loaded in the graph. I have more than 30 graphs in this file and I have to add the label again to each graph to get the new point with the label. Do you know if there is any way to automate this chartlabeler? I will appreciate you helping with this matter. Maperalia

Dynamic Stock Chart

Postby TmljayBLcmlsbA » Mon, 02 Feb 2009 03:18:01 GMT 

How can I create "Name" that will enable a dynamic stock chart in Excel 2003. 
I have been able to create single series dynamic charts using "Name", but no 
luck in creating a dynamic stock chart.
Top

Re: Dynamic Stock Chart

Postby Jon Peltier » Mon, 02 Feb 2009 05:24:45 GMT 

1. The hard way: Define a name for dates and one name for each series in the 
chart (Open, High, Low, Close). Create the chart using static data, then 
replace the static addresses with the names, either in the series formulas 
or in the Source Data > Series dialog.

2. The easy way: Put Date, Open, High, Low, Close into a table. Go to Data 
menu > List > Create List. Make your chart based on this list. As the list 
expands, any formulas (including chart series formulas) update to include 
all rows in the list.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
 http://www.**--****.com/ 
_______
Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Mon, 02 Feb 2009 09:39:01 GMT 

My database of stock prices contracts as well as expands. I haven't been able 
to get either method to adjust it's range to fit the number of data rows. Any 
ideas?
Top

Re: Dynamic Stock Chart

Postby Sm9obiBNYW5zZmllbGQ » Mon, 02 Feb 2009 22:07:01 GMT 

As Jon noted you can use range names to automatically expand or contract your 
data set.  The article below explain how:

 http://www.**--****.com/ 

From the article, the series formulas to expand or contract your data set 
look like this:

Date =OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1) 

Price =OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)-1) 

John Walkenbach also walks through the steps:

 http://www.**--****.com/ 

These articles might help too:

 http://www.**--****.com/ 

 http://www.**--****.com/ 

-- 
John Mansfield
 http://www.**--****.com/
Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Tue, 03 Feb 2009 01:34:03 GMT 

Those methods work well with single line charts - I've been using them for 
some time now, but I haven't been able to get them to work with a stock chart?
Top

Re: Dynamic Stock Chart

Postby Jon Peltier » Tue, 03 Feb 2009 09:23:52 GMT

hese methods do work for stock charts and for any other charts with
multiple series. Is it a stock chart with HLC or OHLC? Put the data into a
List and plot using the list. Your descriptions don't explain what isn't
working, or how you set up the list or names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Nick Krill" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Tue, 03 Feb 2009 23:55:03 GMT

hanks - I'm using OHLC stock charts. Excel will only accept the range name
for the first series: Open price. When I attempt to enter the range name for
the second series (High) it says that there is an error in my formula, which
isn't true: I test that formula by using it in a single line chart and it
works just fine. Weird huh?As a workaround I created a macro that edits the
data range:

ActiveChart.SetSourceData Source:=Sheets("Data").Range("j10:M" &
DataCount), PlotBy:= _
xlColumns

Where DataCount is found as:
DataCount = DataSheet.Cells(Rows.Count, 3).End(xlUp).Row

The incoming data is variable in number of rows. I wansn't able to create a
list that contract as well as expand, so I need to get series names or data
range working.


"Jon Peltier" wrote:

Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Tue, 03 Feb 2009 23:56:05 GMT


"Jon Peltier" wrote:

Top

Re: Dynamic Stock Chart

Postby Jon Peltier » Wed, 04 Feb 2009 06:57:00 GMT

his isn't how you define names. You don't need to use any VBA at all. Try
it this way.

Say your data starts in A1, where cell A1 itself is blank, but you have
dates from A2 downward. O, H, L, and C are in columns B through E. (If your
data is elsewhere, adjust the steps below.)

Use the Insert menu > Names > Define dialog to create the following names:

Name: TheDates
Refers To: =OFFSET(A1,1,0,COUNT(A:A),1)

Name: TheOpen
Refers To: =OFFSET(TheDates,0,1)

Name: TheHigh
Refers To: =OFFSET(TheDates,0,2)

Name: TheLow
Refers To: =OFFSET(TheDates,0,3)

Name: TheClose
Refers To: =OFFSET(TheDates,0,4)

Select the data range you currently have and make your chart. The series
formula for the Open series looks like:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$20,1)

Edit this so it reads:

=SERIES(Sheet1!$B$1,Sheet1!TheDates,Sheet1!TheOpen,1)

Similarly:

=SERIES(Sheet1!$B$1,Sheet1!TheDates,Sheet1!TheHigh,2)
=SERIES(Sheet1!$B$1,Sheet1!TheDates,Sheet1!TheLow,3)
=SERIES(Sheet1!$B$1,Sheet1!TheDates,Sheet1!TheClose,4)

Now if you have done this correctly, it will update whether data is added or
removed.

Actually, as I stated, creating a List is even easier, but you didn't seem
to want to try that.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Nick Krill" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Wed, 04 Feb 2009 07:14:02 GMT

hanks Jon - Actually, way back on 1/31/09 I replied that I had tried both
methods. Method #1, the list method worked beautifully as long as the data
kept expanding, but charting would not contract to fit a smaller database
once it had been enlarged.

"Nick Krill" wrote:

Top

Re: Dynamic Stock Chart

Postby Jon Peltier » Wed, 04 Feb 2009 09:00:25 GMT

f the list itself contracts, which has to happen by deleting rows of the
list, not just emptying cells, the chart would contract.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Nick Krill" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Top

Re: Dynamic Stock Chart

Postby TmljayBLcmlsbA » Wed, 04 Feb 2009 09:10:04 GMT

hanks ever so much for the information. I really appreciate your input.
Nick

"Jon Peltier" wrote:

Top

Similar Threads:

1.Horizontally Banded Chart Background. - on Stock charts 

I have 3 series of data, Hi/Lo/Avg that I am plotting using a Stock
type chart with 3 data points.

I want to use a Horizontally Banded Background to indicate the desired
range.

I have read and tested Jon P's excellent article on Horizontailly
Banded Chart Background, and tested it successfull on a line chart.

However when I test it on the 3 var Stock chart it does not work.

Can anyone give me a suggestion for a way to do what I want with
either my stock type of chart or an alternative way to show what I
want to indicate.

Thanks from American Samoa

JDB

2.Cannot combine stock charts with other chart types in Excel 2007

3.Combination of stock chart and line chart 

Dose anyone know how to plot a stock chart and a line chart together in 
combination ?  I tried but can't seem to do it, is it possible in Excel ?
One way is stock chart with open, high, low and close plus volume but I need 
to plot 2 other lines.
Thanks,
Hiromichi

4.High/low stock chart with line chart 

any help appreciated...is it possible to plot a 
high/low/close stock chart with an additional series on a 
second axis?
for example

stock high/low/close price -  y axis 1
p/e ratio - y axis 2

5.Provide a "high/low" chart (stock chart) in horizontal mode

6. Stock chart with indicator on same chart

7. How to automatically stock Dynamic data?

8. [VBA] Dynamic target stock

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 23 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group