Jump to content

ExcelChart UDF - Help & Support


water
 Share

Recommended Posts

I think this format may be easier to work with, but am still working on the_XLChart_ChartCreate() parameters.  

_ExcelWriteCell($oExcel, "first visit", 1, 2)
_ExcelWriteCell($oExcel, "yes", 2,1)
_ExcelWriteCell($oExcel, "no", 3,1)
_ExcelWriteArray($oExcel, 2, 2, $values, 1)
Link to comment
Share on other sites

If you check the internal function _XLChart_Example in the UDF you can find an example for a 3D pie chart.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

func pie()

    Local $oExcel = ObjCreate("Excel.Application")

    $oExcel.Visible = 1
    $oExcel.WorkBooks.Add
    $oExcel.ActiveWorkbook.Sheets(1).Select()
    
    $oExcel.Activesheet.Cells(1, 1).Value = "Answer"
    $oExcel.Activesheet.Cells(2, 1).Value = "Yes"
    $oExcel.Activesheet.Cells(3, 1).Value = "No"
    $oExcel.ActiveSheet.Columns(1).AutoFit
    $oExcel.Activesheet.Cells(1, 2).Value = "First Time"
    $oExcel.Activesheet.Cells(2, 2).Value = "52"
    $oExcel.Activesheet.Cells(3, 2).Value = "205"
    $oExcel.ActiveSheet.Columns(2).AutoFit
    
    ; Set the names of the worksheets
    $oExcel.ActiveSheet.Name = "_XLChart_Example"

    Local $XValueRange = "=_XLChart_Example!R2C1:R3C1"
    _XLChart_ChartCreate($oExcel, 1, $xl3DPie, "A9:D18", "", $XValueRange, "=_XLChart_Example!R2C2:R3C2", "=_XLChart_Example!B1", True, "First Time")

endfunc

pie()

I studied _XLChart_Example() and made the example above, which works.

R stands for row.

C stands for column.

Thanks,

-John

Edited by jftuga
Link to comment
Share on other sites

:D

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 4 weeks later...

Hi again. This UDF is awsome and i use it alot. However for my current projekt i need to be able to format the title Title.

I know i can chnage the Title with the _XLChart_TitleSet function, and that works great. However i also need to be able to use formating of the chart-title.

In my case i use this code:
 

_XLChart_TitleSet($array1[1][0].ChartTitle,"String1" & @CR & "String2")

String1 needs to be size 12 and "fat". String2 needs to be Size 8. Any idees?

I also need to be able to change the Size of the chart. If its 10cm in hight, i need to be able to change it based on the number of values i put in.
Thanx for any help/suggestions

Edited by Tjalve
Link to comment
Share on other sites

_XLChart_TitleSet($array1[1][0].ChartTitle,"String1" & @CR & "String2")
String1 needs to be size 12 and "fat". String2 needs to be Size 8. Any idees?

 

How would you do it in pure Excel?

I also need to be able to change the Size of the chart. If its 10cm in hight, i need to be able to change it based on the number of values i put in.

Use function _XLChart_ChartPositionSet to resize and reposition a chart object

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

How would you do it in pure Excel?

Use function _XLChart_ChartPositionSet to resize and reposition a chart object

Great stuff. I will try.

One more thing. The "area" inside the chart that hold the chart itself (the bars) also need rizing. Is it posible to rixe thoose so that they are always the same number of pixels from the edge?

The VBA Code look like this when i changed it:

ActiveSheet.ChartObjects("Diagram 2").Activate
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("Diagram 2").Activate
    Selection.Height = 57.948

Regarding the Title. I just mark the text and change the size of it. I tried to to it while recording a macro, but it doesnt record formating (or I did somthing wrong). I did find this VBA code. Perhaps you can make an sense of it.

 

With ActiveChart
        ActiveChart.ChartTitle.Select
        With Selection.Border
            .Weight = xlHairline
            .LineStyle = xlAutomatic
        End With
        Selection.Shadow = True
        Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
        With Selection
            .Fill.Visible = True
            .Fill.ForeColor.SchemeColor = 58
            .Fill.BackColor.SchemeColor = 57
        End With
        Selection.AutoScaleFont = True
        With Selection.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 18
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
    End With  '  With ActiveChart
Link to comment
Share on other sites

The "area" inside the chart that hold the chart itself (the bars) also need rizing. Is it posible to rixe thoose so that they are always the same number of pixels from the edge?

I would try function _XLChart_ObjectPositionSet and pass the PlotArea object to the function. The math needs to be done by you.

The VBA you provided alters a lot of properties for the WHOLE title. But there is no way I know of to only alter PART of the title.

Could you record a macro and select the first part of the title and then the second part ("String1 needs to be size 12 and "fat". String2 needs to be Size 8.")

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

i Thank you for you help water.

I tried selecting as you said and i just got this:

ActiveSheet.ChartObjects("Diagram 2").Activate
    ActiveChart.ChartTitle.Select
    ActiveSheet.ChartObjects("Diagram 2").Activate

I use a template so i might fix the formatting in th eoriginal template. But is there a way for me to "replace" some parts of the text and keep the original formatting?

In this case. I set String1 to 12 and fat. And String2 to 8. I then replace the text with the text i want? Is that possible?

Link to comment
Share on other sites

I haven't done it myself and can't test at the moment but something like this should work:

$oTitle = $oWorkbook.ActiveSheet.ChartObjects("Diagram 2").ChartTitle ; Returns the chart title
$oString1 = $oTitle.Characters(Startposition, Length) ; Returns a substring of the chart title. Modify Startposition and Length
_XLChart_FontSet($oString1, ...) ; Set the font properties for the substring

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

 

I haven't done it myself and can't test at the moment but something like this should work:

$oTitle = $oWorkbook.ActiveSheet.ChartObjects("Diagram 2").ChartTitle ; Returns the chart title
$oString1 = $oTitle.Characters(Startposition, Length) ; Returns a substring of the chart title. Modify Startposition and Length
_XLChart_FontSet($oString1, ...) ; Set the font properties for the substring

HAHA you are a king! That works just pefectly! Thank you!

Edited by Tjalve
Link to comment
Share on other sites

Glad to be of service :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 1 year later...

Version 0.4.0.0 of the UDF has been released. Only changes made are to make it run with Autoit 3.3.12.0!

Have fun testing!

For download please see my signature.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 years later...

Hello Water,

Hope you are well. I am trying to work with UNIX time on my x-axis and Excel plots it in descending order (when in fact UNIX time is ascending!).

Is there anyway to modify the plotting order in your UDF? I don't need example code, just need to know if the option exists and what it is (didn't find it in the UDF files).

Edit: The workaround I am using is to multiply the unix time value by -1... but that looks ugly and is a recipe for disaster by "best practice"

Edited by AlwaysLearning
Link to comment
Share on other sites

Could you please provide a simple reproducer script so I can play with it?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Hello Water

I will have to extract that portion of the script as there are private API keys involved and the function that executes this chart generation is dependent on several other functions in the script which use these API keys.

I can remove this and rebuild it but it will take me some time because I learned as I went making this and didn't manage my code very well. Half my mistakes were fixed with duct taping "if/then" and random variables (once I have everything functioning, I plan to rebuild it with less "workarounds" -- your UDF and others has helped me understand why a quick and sloppy fix can end up costing you time in the long run). 

For now I am attaching a photo which may be sufficient.

Notice on the x-axis that the UNIX timestamps are time passed in seconds since 1970.
As such, the most recent time is always the higher number. 
Excel's plotting order doesn't like this as it goes descending by default.

chart.jpg

Edit: extracting an example was easier than I thought!

Here is the code:

#include <String.au3>
#include <Array.au3>
#include <Inet.au3>
#include <Excel.au3>
#include <ExcelChart.au3>

Global $oExcelProcess = _Excel_Open() ;; ---- Set to false to make invisible
Global $iXLC_Debug = 2 ;(or = 1)
Global $oExcel = _Excel_BookNew($oExcelProcess)

$aChartData = ChartData()

#Region Chart Rendering ;; Render chart on first run to save time when refreshing the JPEG for GUI later
    _XLChart_ScreenUpdateSet($oExcel, 2)
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "Date", "A1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "O", "B1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "H", "C1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "L", "D1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "C", "E1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "P", "G1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "S2", "H1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "S1", "I1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "R2", "J1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "R1", "K1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, $aChartData, "A2")

    $Graph_position = "B8:K32"
    $XValueRange = "=Sheet1!R2C1:R6C1"
    Local $DataRange[5] = [4, "=Sheet1!R2C2:R6C2", "=Sheet1!R2C3:R6C3", "=Sheet1!R2C4:R6C4","=Sheet1!R2C5:R6C5"]
    Local $DataName[5] = [4, "=Sheet1!B1", "=Sheet1!C1", "=Sheet1!D1", "=Sheet1!E1"]
    Local $iWorksheet = 1
    Global $oChart1 = _XLChart_ChartCreate($oExcel, $iWorksheet, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName)

    ; Format chart
    _XLChart_LayoutSet($oChart1, Default, 46)
    _XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4) ; Set the UpBars to green
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    _XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3) ; Set the DownBars to red
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    _XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 450) ; Make the bars smaller (by making the gap between the bars bigger)
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ;_XLChart_FillSet($oChart1.ChartArea, 0xF0F0F0)
    ;If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)


    ; Add a data series for P
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C7:R" & (6) & "C7", "=Sheet1!G1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(5), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(5), 7, $xlMarkerStyleDiamond, default, 0x0057e7)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(5))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(5).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(5), Default, Default, False, Default, Default, $xlSheetHidden)

    ; Add a data series for S2
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C8:R" & (6) & "C8", "=Sheet1!H1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(6), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(6), 10, $xlMarkerStyleDash, Default, 0xC80014)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(6))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(6).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(6), Default, Default, False, Default, Default, $xlSheetHidden)

    ; Add a data series for S1
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C9:R" & (6) & "C9", "=Sheet1!I1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(7), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(7), 10, $xlMarkerStyleDot, Default, 0xDE6672)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(7))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(7).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(7), Default, Default, False, Default, Default, $xlSheetHidden)

    ; Add a data series for R2
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C10:R" & (6) & "C10", "=Sheet1!J1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(8), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(8), 10, $xlMarkerStyleDash, Default, 0x78c800)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(8))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(8).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(8), Default, Default, False, Default, Default, $xlSheetHidden)

    ; Add a data series for R1
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C11:R" & (6) & "C11", "=Sheet1!K1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(9), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(9), 10, $xlMarkerStyleDot, Default, 0xc9e999)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(9))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(9).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(9), Default, Default, False, Default, Default, $xlSheetHidden)


    ; Align primary and secondary Y-axis
    _XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)
    ; Delete the secondary Y-axis
    _XLChart_ObjectDelete($oChart1.Axes($xlValue, 2))
    ; Enable Screen updating
    _XLChart_ScreenUpdateSet($oExcel, 1)
    ; get the values for the Y-axis
    ;MsgBox(0, "Y-axis minimum value", $oChart1.Axes($xlValue).MinimumScale)
    ;MsgBox(0, "Y-axis maximum value", $oChart1.Axes($xlValue).MaximumScale)


    ; Save chart as PNG
    Local $iFile = @ScriptDir & "\chart.jpg"
    _XLChart_ChartExport($oChart1, $iFile, "JPEG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
#EndRegion


While 1
    $aChartData = ChartData()
    GUIGraph($aChartData)
    Sleep ( 10 )
WEnd

Func ChartData($sParameters = '') ; ChartData -- Time, Open, High, Low, Close, Volume =========>

    $sChartData = _INetGetSource("https://coincheck.com/api/charts/candle_rates?limit=20&market=coincheck&pair=btc_jpy&unit=900&v2=true")
    $sChartData = StringTrimLeft($sChartData, 2)
    $sChartData = StringTrimRight($sChartData, 2)
    $sChartData = StringReplace($sChartData, '],[', @LF)
    $sChartData = StringReplace($sChartData, ',', " ")
    $aChartData = _VarTo2D($sChartData)

    ReDim $aChartData[UBound($aChartData)][UBound($aChartData, 2) + 5]

    ;; === Pivot Point
    $tot = 0
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        For $j = 1 To 4 ; Column by column
            $tot += $aChartData[$i][$j] ; 1 * current cell = tot, then tot * next cell
        Next
        $tot = Round(($tot / 4), 0)
        $aChartData[$i][UBound($aChartData, 2) - 5] = $tot ; write the final total
        $tot = 0 ;reset and move to next row
    Next

    ;; === S1=P-(H-L)

    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 4] = $p - ($h - $l)
    Next

    ;; === S2= P + 2×P-H
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 3] = $p - (2 * ($h - $p))
    Next

    ;; === R1
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 2] = $p + (2 * ($p - $l))
    Next

    ;; === R2

    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 1] = $p + ($h - $l)
    Next


    ;_ArrayDisplay($aChartData)

    Return $aChartData

EndFunc   ;==>ChartData

Func GUIGraph($aChartData)

    ; Disable Screen updating (forced even when debugging is activated)
    _XLChart_ScreenUpdateSet($oExcel, 2)
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "Date", "A1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, $aChartData, "A2")


    ; Align primary and secondary Y-axis
    _XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)
    ; Delete the secondary Y-axis
    _XLChart_ObjectDelete($oChart1.Axes($xlValue, 2))
    ; Enable Screen updating
    _XLChart_ScreenUpdateSet($oExcel, 1)
    ; get the values for the Y-axis
    ;MsgBox(0, "Y-axis minimum value", $oChart1.Axes($xlValue).MinimumScale)
    ;MsgBox(0, "Y-axis maximum value", $oChart1.Axes($xlValue).MaximumScale)

    ; Save chart as JPEG ===> The GUI part is commented out because my GUI code isn't necessary here.
    _XLChart_ChartExport($oChart1,  @ScriptDir & "\chart.jpg", "JPEG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ;GUICtrlSetImage($ChartImage, @ScriptDir & "\chart.jpg")
    Return
EndFunc   ;==>Example

Func _VarTo2D($var, $sSeparator = " ")
    Local $aRows = StringSplit(StringStripCR($var), @LF), $aColumns, $aResult[$aRows[0]][1]
    For $iRow = 1 To $aRows[0]
        $aColumns = StringSplit($aRows[$iRow], $sSeparator)
        If $aColumns[0] > UBound($aResult, 2) Then ReDim $aResult[$aRows[0]][$aColumns[0]]
        For $iColumn = 1 To $aColumns[0]
            $aResult[$iRow - 1][$iColumn - 1] = $aColumns[$iColumn]
        Next
    Next
    Return $aResult
EndFunc   ;==>_VarTo2D

Since we have the code there, one more issue I had was trying to change the GraphPosition based on Ubound($aChartData).
I actually wanted to change the Chart Data size by just clicking buttons in my GUI, but this would mean the GraphPosition and Graph Size would depend on the size of $aChartData.
While this works and generates the expected Excel Result, all my exported JPEGs/PNGs are 0KB unless I keep the Position of the chart relatively near the top of the spreadsheet.

Edited by AlwaysLearning
Edit1 : Adding code Edit 2: removing unnecessary #include UDFs
Link to comment
Share on other sites

I created this little reproducer script and it works as expected:

#AutoIt3Wrapper_UseX64=n
#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include-once
#include <ExcelChart.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
$oExcel.ActiveSheet.Name = "_XLChart_Example"

; *****************************************************************************
; Example 4
; OPEN-HIGH-LOW-CLOSE Chart
; *****************************************************************************
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Time", "H1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517614200, "H2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517615100, "H3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517616000, "H4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517616900, "H5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517617800, "H6")
$oExcel.ActiveSheet.Columns(8).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Open", "I1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.682", "I2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.342", "I3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.203", "I4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.204", "I5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.485", "I6")
$oExcel.ActiveSheet.Columns(9).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "High", "J1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.826", "J2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.375", "J3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.463", "J4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.364", "J5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.598", "J6")
$oExcel.ActiveSheet.Columns(10).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Low", "K1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.263", "K2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.654", "K3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.085", "K4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.891", "K5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.251", "K6")
$oExcel.ActiveSheet.Columns(11).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Close", "L1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.403", "L2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.882", "L3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.307", "L4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.135", "L5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.450", "L6")
$oExcel.ActiveSheet.Columns(12).AutoFit
Global $Graph_position = "A1:F19"
Global $XValueRange = "=_XLChart_Example!R2C8:R6C8"
Global $DataRange[5] = [4, "=_XLChart_Example!R2C9:R6C9", "=_XLChart_Example!R2C10:R6C10", "=_XLChart_Example!R2C11:R6C11", "=_XLChart_Example!R2C12:R6C12"]
Global $DataName[5] = [4, "=_XLChart_Example!O1", "=_XLChart_Example!P1", "=_XLChart_Example!Q1", "=_XLChart_Example!R1"]
Global $oChart1 = _XLChart_ChartCreate($oExcel, 1, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName, Default, "Open-High-Low-Close Chart")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)
; Format chart
_XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4)    ; Set the UpBars to green
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3)  ; Set the DownBars to red
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 250)   ; Make the bars smaller (by making the gap between the bars bigger)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Water, 

Unfortunately, reversing the order of the input data isn't an option.

In your example, you have fixed my issue by reversing the order of data inputted in column H (it is in ascending order which will of course work as expected -- that is the default operating mode).

However, my data comes from a live array fed in by an API in DESCENDING order.
I realize I could add a command to filter this into ascending order in excel, but that would make it very ineffective because the script is expected to cycle 5 times per second or more.

Essentially, I need to edit the chart's x-axis plotting order to Descending mode, which is a feature I don't see in the UDF.

Link to comment
Share on other sites

Can't test at the moment but I think this could be the solution:

$oChart1.Axes($xlValue).ReversePlotOrder = True

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...