How to Generate a Stock Chart with Perl in MS Excel?

morfecourier has asked for the wisdom of the Perl Monks concerning the following question:

I want to use Perl to generate a Stock Chart: Open, High, low, Close in Microsoft Excel with Win32::OLE Module. However, all the other kinds of graphs I can get it out successfully except Stock Chart! Gosh, What Happened? Could someone help me on it? Thanks! These are the codes I used.

$Range = $Sheet->Range('c4:f64');

$Chart = $Excel->Charts->Add;

$Chart->{ChartType}=xlStockOHLC;

$Chart->SetSourceData ({Source => $Range, PlotBy => xlColumns});

$Chart->SeriesCollection(1)->{XValues} = "=Sheet1!R4C2:R64C2";

$Chart->SeriesCollection(1)->{Name} = "open";

$Chart->SeriesCollection(2)->{XValues} = "=Sheet1!R4C2:R64C2";

$Chart->SeriesCollection(2)->{Name} = "high";

$Chart->SeriesCollection(3)->{XValues} = "=Sheet1!R4C2:R64C2";

$Chart->SeriesCollection(3)->{Name} = "low";

$Chart->SeriesCollection(4)->{XValues} = "=Sheet1!R4C2:R64C2";

$Chart->SeriesCollection(4)->{Name} = "close";

$Chart->Location ({Where => xlLocationAsNewSheet});

$Chart->{HasLegend} = True;

$Chart->Legend->{Position} = xlTop;

$Chart->Location ({Where => xlLocationAsObject, Name => "Sheet1"});

Actually, it works well when compiling. But when execution, it bursts out a problem and gives me the following information: Win32::OLE(0.1707) error 0x80020009: "" in propertyput "ChartType" at test2.pl line16. (Here, it is the 3rd row.) When I change the type of "xlStockOHLC" to "xlLine" or "xl3DColumn", it works perfectly.

Comment on How to Generate a Stock Chart with Perl in MS Excel? Select or Download Code

Replies are listed 'Best First'.
Re: How to Generate a Stock Chart with Perl in MS Excel? by Corion (Patriarch) on Jan 24, 2008 at 06:45 UTC
In your code you write `$Chart->{ChartType}=xlStockOHLC;`, but in the error message you write `error 0x80020009: "" in propertyput "ChargeType"` - which one is it - `ChartType` or `ChargeType`? I guess you just miscopied the error message, but it pays to make sure :) My approach to such things is to use the macro recorder and then inspect the VB code for what it does differently from how my Perl code does it.	[reply] [d/l] [select]
Re^2: How to Generate a Stock Chart with Perl in MS Excel? by morfecourier (Initiate) on Jan 25, 2008 at 05:13 UTC
Oh, Corion, very sorry about it. That should be ChartType. I have already update it. Thanks for help!	[reply]
Re: How to Generate a Stock Chart with Perl in MS Excel? by hipowls (Curate) on Jan 23, 2008 at 21:22 UTC
I can't offer any help with your problem (I don't have any expertise) but I'd suggest that you have a look at Writeup Formatting Tips which tells you how to format your questions. The bottom of the preview page has a quick guide to available tags. You will get a better response if they are well presented. At a minimun use <p> </p> pairs for paragraphs and <code> </code> for literal code or data blocks.	[reply]
Re^2: How to Generate a Stock Chart with Perl in MS Excel? by morfecourier (Initiate) on Jan 25, 2008 at 05:27 UTC
Hello, hipowls. Thank you for your suggestion. I think now it looks much better. Thanks for help!	[reply]
Re: How to Generate a Stock Chart with Perl in MS Excel? by traveler (Parson) on Jan 23, 2008 at 23:49 UTC
I would check Super Search. I found lots of help there when I had to manipulate excel ss.	[reply]