• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Graph from a worksheet array

IronPalm

New Member
Hi,


I have a table of daily stock prices/indicies for which I am trying to create a volatility chart. So, I am calculating the daily differences in prices as Today's price - Yesterday's price. These values I have stored in an array for eg. {=A2:A100 - A1:A99} My question is, can I directly graph this array? I'm trying to avoid entering these value in the sheet to keep the sheet neat enough to read.


Any help would be greatly appreciated.


Thanks,


IronPalm
 
Simple.. make a named range out of your array and then use the named range in chart's input source data. Make sure you write the range as Sheet1!name otherwise it wont be accepted.
 
I'm sorry but I am not clear as to how to do it. Is it like below:


Sub Pass_array()

Dim myarray As Variant

myarray = Range("a1:a10").Value

End Sub


and them manipulate myarray? Would appreciate your

help.
 
IronPalm

I think Chandoo indented you to use a Named Range, not in VBA

Try the following

Select your Data A2:A100 and give it a name of Data

You can do that on the Formula, Named Range Tab, use the Define Name button

You will need to add 2 more named ranges

Lets call the first one Offset_Data and type the foruma =OFFSET(Sheet1!Data,-1,0)

and add a third Named Range valled Volatility and make it =Data-Offset_Data

In your chart you can now chart the Volatitity

Right click on your chart and Select Data, Select the Add Button

Type in a Name for your Series and in the series Box add =Sheet1!Volatility


In Summary Setup 3 Named Ranges

Data =A2:A100

Offset_Data =OFFSET(Sheet1!Data,-1,0)

Volatility =Data-Offset_Data

and Chart the series Sheet1!Volatility


Adjust Ranges and Sheet Names as appropriate
 
Hi Hui,


Thanks for the update. I have entered as follows:


I have defined 1 range as BDI -> =OFFSET(Data!$C$5,1,0,COUNTA(Data!$C$5:$C$1997)-1,1)


and the 2nd range as:


Offset_BDI -> =OFFSET(BDI,-1,0)


and the differences as:


Volatility -> =BDI-Offset_BDI


When I defined the above range, Excel doesn't give an error but when I try to graph by giving the series 'Data!Volatility' it doesn't accept. But I am able to graph both BDI and Offset_BDI without any problem.


Would appreciate any help.


Thanks
 
ok... I dont think you need names for first two series.


instead define volatility as =OFFSET('volatility chart'!$C$5,0,0,COUNTA('volatility chart'!$C$5:$C$1997),1)-OFFSET('volatility chart'!$C$6,0,0,COUNTA('volatility chart'!$C$6:$C$1998),1)


and point 'volatility chart'!volatility as the source for chart.
 
IronPalm

Offset_BDI -> =OFFSET(FileName!BDI,-1,0)

Volatility -> =FileName!BDI-FileName!Offset_BDI


where the Filename is the Workbook name


Same with the Chart Series name

=FileName!Volatility


@Chandoo, I used 3 Named Ranges as it is easier to understand for new users and easier to debug, but you are correct, you can do it in 1 named Range.
 
Did exactly as you said but Excel doesn't accept the named range Data!Volatility. Is there someway I can attach the excel file here?
 
No you need to use a public file sharing site

look at http://chandoo.org/forums/topic/posting-a-sample-workbook


Try the File name not the Sheet name in front of the !
 
Yes, I tried the file name as well but Excel doesnt accept. Please have a look at my sheet. Thanks.


Here is the link:


http://rapidshare.com/files/408391777/Historical_data.xls
 
IronPalm


Named Range

VolBDI: =Historical_data.xls!BDI-Historical_data.xls!Offset_BDI


and the second series on the chart will use

Historical_data.xls!VolBDI


the series equation should then be

=SERIES("Vol",,Historical_data.xls!VolBDI,2)


works for me


If it doesn't what version of Excel are you using?

I have tried in 2003, 2007 & 2010, all OK
 
Hi,


Thanks for the prompt reply. I'm using 2007 but for some reason it is still not accepting the series function you mentioned above. Can you post the completed worksheet, I'll see whether it is working in my computer? That way ill know there is something wrong with the setup.


Thanks
 
IronPalm

I have saved the file as a Macro Enabled 2007 file

It is half the size of the old file (Excel 2003)

The Chart on the Data Page is working


I have also added a little bit on the Names page


It updates both the Chart and Volatility at the same time in the same chart

Have a look at


If this is private data let me know and I will remove my upload file once you download


Have a look at http://rapidshare.com/files/408401429/Historical_data_v2.xlsm.html
 
Hi Hui,


Thanks for the file, it works great. But I have no idea why it didn't before. The data is not private, so delete as per your convenience.


Thanks a lot.
 
Back
Top