Help with Scroll and Zoom Bar for Candle Chart

[cds979]

I am currently doing data manipulation for my series of data. I have
Time, Open, High, Low, Close. There are approximately 6000 rows of
data that I need to input into the Candle Chart and to do that I
require a Scroll and Zoom function. I have been using a template for
Scroll/Zoom for a normal basic Line Chart and attempted to reconfigure
for a Candle Chart. I have named the following:

SCROLLVALUE=Sheet1!$S$31
SERIES1==OFFSET(Sheet1!$B$1,SCROLLVALUE,0,ZOOMVALUE,1)
SERIES2==OFFSET(Sheet1!$C$1,SCROLLVALUE,0,ZOOMVALUE,1)
SERIES3==OFFSET(Sheet1!$D$1,SCROLLVALUE,0,ZOOMVALUE,1)
SERIES4==OFFSET(Sheet1!$E$1,SCROLLVALUE,0,ZOOMVALUE,1)
TIME==OFFSET(SCROLL_TESTER.xls!SERIES1,0,-1)
ZOOMVALUE==Sheet2!$S$29

Then I go into 'Select the Data Source' in the actual Candle Chart and
try to enter the following details for the X-axis (time) and Series 1,
Series 2, Series 3, Series 4 (all required to draw a Candle Chart in
Excel):

='SCROLL_TESTER.XLSX'!TIME
='SCROLL_TESTER.XLSX'!SERIES1
='SCROLL_TESTER.XLSX'!SERIES2
='SCROLL_TESTER.XLSX'!SERIES3
='SCROLL_TESTER.XLSX'!SERIES4

Then Excel states an error saying that either the Formula or Worksheet
or Cell Reference is incorrect. I believe the only error is in the
actual naming and defining of the range through the OFFSET function
however when I double check it seems correct to my knowledge (due to
my lack of).

If anyone could provide some help into getting the Scroll/Zoom
function to work properly for the Candle Chart. I have seen it done
before. Or if you know any other method/program that has greater
efficiency then please also suggest. Thanks for your help in advance.

I can also send you the original workbook if it is of any help.

Cheers,

Chris

 

[Jon Peltier]

I can't tell what's wrong. Nominally it looks okay.

To make things easy, I typically only define one name in very much detail,
then base the others on that one.

Time
=OFFSET(Sheet1!$A$1,SCROLLVALUE,0,ZOOMVALUE,1)

Open
=OFFSET(Time,0,1)

High
=OFFSET(Time,0,2)

Low
=OFFSET(Time,0,3)

Close
=OFFSET(Time,0,4)

Test to make sure Time represents an actual range. In the Define Names
dialog, click on the name Time in the list, then click in the refers to
formula, and the range should be highlighted by the marching ants outline.
If it isn't, make sure Zoomvalue is greater than zero.

From here it's a straightforward procedure to assign names to X values and Y
values. It sounds like you've done this before. I've just tested it in 2007,
and it's really just the same as in earlier versions.

- Jon

 

[cshort979]

Hi Jon,

I tried the method as you suggested and it works successfully. Thank
you very much for your help.


Also is it possible to add in a vertical line per every single 'x'
variable candle so that I am able to see each day clearly. For e.g. I
want to have a vertical line down the graph every 12 candles in the
candle chart graph.

I have seen this done in another chart excel file I have however I do
not know how to add this function as the excel file has been locked
and password protected.


Cheers,

Chris

 

[Jon Peltier]

You can use the combination of major gridlines (use light gray instead of
the default black!), and changing the X axis scale so the Y axis does not
cross between categories. Though keeping the Y axis crossing between
categories will put a line between each candlestick, which may also work.

- Jon

 

[cshort979]

Excellent I got it to work. Thanks Jon.

Chris