Tag Archives: stock

Issue while creating a stock like excel chart using PowerShell

Posted on by

Although this post is not in continuation of my previous posts on Excel charts and PowerShell but discusses a specific issue while creating Stock like chart. As topics are same I will advice you to go through the previous posts as I will be referring few things from that. The links of my previous posts are below

So to create a basic stock like chart we need to provide the data is a specific format.
b4-1

So lets write the script for the same

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

$xl = new-object -ComObject Excel.Application   
$fileName = ''
$wb = $xl.Workbooks.Open($fileName) 
$wsData = $wb.WorkSheets.item(1) 

#Activating the Data sheet
$wsData.activate() 

#Selecting the source data - We cn select the first cell with Range and select CurrentRegion which selects theenire table
$DataforChart = $wsData.Range("A1").CurrentRegion

# Adding a new sheet where the chart would be created
$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

#Adding the Charts
$stockChart = $wsChart.Shapes.AddChart().Chart

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)

# Set it true if want to have chart Title
$stockChart.HasTitle = $true

# Providing the Title for the chart
$stockChart.ChartTitle.Text = "Stock like Chart"

# Setting up the position of chart (Not required if the sheet has just one chart). It will create the chart at top left corner
$wsChart.shapes.item("Chart 1").top = 0
$wsChart.shapes.item("Chart 1").left = 0

# Saving the sheet
$wb.Save();

# Closing the work book and xl
$wb.close() 
$xl.Quit()
# Releasting the com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

So the above code looks fine as similar to our previous posts but when we run it throws an exception as
exceptionb4

It does not show any specific details for this exception and the error code also does not lead to any details but we do get default bar chart.

So it a look a little while to find the cause as it was not a common as for other charts it was working very smoothly.

For stock charts, it expects the data before providing chart types so if we change the below lines


# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)

to


# Providing the source data
$stockChart.SetSourceData($DataforChart)

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

Its starts running. Let’s see a quick look on resultant chart
stock-chart

So it was very peculiar as the resolution does not seem to have any logic but next time you face similar issue, it may be helpful.

Cheers,
Brij

Advertisement