none
Excel OHLC Chart with line as secondery RRS feed

  • Question

  • Hi.. I'm trying to add Line on an OHLC chart on XL 2010 Pro Plus, I can do it from the XL, but when i am trying to automate it [walking blindly] it first thrown me an Exception, then i fixed it, but then it does nothing and doesn't add the 5th series to the OHLC chart..

    That's my code:

    // Chart
    Excel.ChartObjects xlCharObj=(Excel.ChartObjects)SYSws.ChartObjects(Type.Missing);

    if(!First){First=true;}
    else{xlCharObj.Delete();}

    Excel.ChartObject xlChart=(Excel.ChartObject)xlCharObj.Add(300,300,730,540);
    Excel.Chart xlCpage = xlChart.Chart; 


    Excel.Range ChrtRng = SYSws.get_Range("A2","E120");
    xlCpage.SetSourceData(ChrtRng,MV);
    xlCpage.ChartType = Excel.XlChartType.xlStockOHLC; 

    xlCpage.HasTitle = true;
    xlCpage.HasLegend = true;
    xlCpage.ChartTitle.Text = "EJ H1"; 
    Excel.SeriesCollection xlC_SerColl = (Excel.SeriesCollection)xlChart.Chart.SeriesCollection(MV);

    Excel.Axis XAxs = (Excel.Axis)xlChart.Chart.Axes(Excel.XlAxisType.xlCategory,Excel.XlAxisGroup.xlPrimary);
    XAxs.HasMajorGridlines = false; 
    XAxs.HasTitle = true; 
    XAxs.AxisTitle.Text = "Cumm%";
    XAxs.CategoryType = Microsoft.Office.Interop.Excel.XlCategoryType.xlCategoryScale;

    //-----------OHLC Chart Fine

    Excel.Range R1Val = SYSws.get_Range("F2","F120");
    Excel.Range R1Cat = SYSws.get_Range("A2","A120");

    Excel.Series xlC_Ser = xlC_SerColl.Add(Excel.XlSourceType.xlSourceChart, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns,MV,MV,MV);
    xlC_Ser.Values = R1Val; 
    xlC_Ser.XValues = R1Cat;

    Excel.Axis YAxs = (Excel.Axis)xlChart.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
    //YAxs.HasMajorGridlines = false; YAxs.HasTitle = true; YAxs.AxisTitle.Text = "Ranges";

    //object oMin = SYSws.Evaluate("MIN(A2:F120)");
    //object oMax = SYSws.Evaluate("MAX(A2:F120)");
    //YAxs.MinimumScaleIsAuto = false; YAxs.MaximumScaleIsAuto = false; 
    //YAxs.MinimumScale = (double)oMin; YAxs.MaximumScale = (double)oMax;

    Then it shows the OHLC chart on XL alright, with 4 series, but without the Fifth Line series.. 

    Any help?..

    Thanx ahead..

    PEACE..


    • Edited by YakirCohen Wednesday, November 19, 2014 10:49 PM
    • Moved by Kristin Xie Thursday, November 20, 2014 3:29 AM move to better forum
    Wednesday, November 19, 2014 3:20 AM

All replies

  • Hi YakirCohen,

    Based on your code, your case related to Excel, so i moved your case to  Microsoft Office for Developers > Excel for Developers for more efficient responses.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 20, 2014 3:32 AM
  • Hi YakirCohen,

    As far as I know, Open-high-low-close type of stock chart requires four series of values in the correct order (open, high, low, and then close). You can get more detail about stock chart in link below:
    Present your data in  a stock chart

    Could you add the fifth line series manually? If yes, I suggest that you record macros to get the code and transform it into C#.

    If not, it is the limitation of this type of chart. I suggest that you submit feedback from link below:
    Submit Feedback - Microsoft Office

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 21, 2014 2:11 AM
    Moderator
  • Hi Fei.. Thanks.. Yes i can add the 5th line manually, but i dont know abt macros and transforming then into CS code.. I'll c abt that.. 
    Saturday, November 22, 2014 9:49 PM
  • Hi YarkirCohen,

    Thanks for the detail information of this issue.

    I suggest that you record macros to get the code corresponding to add the 5th line manully. If you have any issue translating the macro to CSharp, you can post the macros here.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 24, 2014 9:45 AM
    Moderator
  • Anybody?
    Friday, December 5, 2014 4:53 AM
  • Hi Yakircohen,

    Have you fixed the issue? If not, wolud mind sharing with which step block you?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 5, 2014 7:15 AM
    Moderator
  • Hello.. No i did not find any solution for that anywhere online.. It just doesn't show the 5th linear series on the OHLC chart or on it's properties..
    Friday, December 5, 2014 9:39 PM
  • Hi YakirCohen,

    Since the OHLC chart support 4 series by default, would you mind sharing with us detail steps how did you add 5th line?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 8, 2014 12:32 PM
    Moderator
  • Sure, The code is this:

    xlCpage.set_HasAxis(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary, true);  
    xlCpage.set_HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlSecondary, true);

    Excel.SeriesCollection SerColl = (Excel.SeriesCollection)xlChart.Chart.SeriesCollection(MV);

    Excel.Range R1Val = SYSws.get_Range("AN11","AN"+(reader.Count+10).ToString());
    Excel.Range R1Cat = SYSws.get_Range("AD11","AD"+(reader.Count+10).ToString());

    Excel.Series WMAser = SerColl.NewSeries();
    WMAser.XValues = R1Cat;
    WMAser.Values = R1Val;
    //WMAser.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
    WMAser.Format.Line.Weight = 2;
    WMAser.Name = "WMA3";

    Where xlCpage is Excel.Chart, This code adds the 5th line into the OHLC chart, but not as a Secondary, So i need to add it manually as a Secondary to see it properly [otherwise it's just mixing with the OHLC bars and improper up/down bars is shown], then it's also doesn't sync the two axes [Primary and Secondary] well, i.e. it's not showing the same Min and Max values for the Axes, and the line and bars aren't make sense..

    This is what i got for now, it's just picking here and then online, trial and error.. now i am trying to auto Second the WMA line [Make it auto Secondary] and also automatically define the same MinMax for the Axes by values i got..

    Hope someone to have an answer as this bugger is almost a month now..

    Thanx ahead and have a good 1..

    CHEERS! 

    Monday, December 8, 2014 5:11 PM
  • Hi YakirCohen,

    >>So i need to add it manually as a Secondary to see it properly [otherwise it's just mixing with the OHLC bars and improper up/down bars is shown], then it's also doesn't sync the two axes [Primary and Secondary] well, i.e. it's not showing the same Min and Max values for the Axes, and the line and bars aren't make sense..<<

    Have you tried to recrd macros to get the similar code corrsonpoding the manul option?

    Since this issus is complex, I am also trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 10, 2014 8:50 AM
    Moderator
  • Hello there.. No i have't tried Macros, i am not familiar with it's language and structure..

    And it's okay no worries, thanks for the help much appreciated..

     
    Wednesday, December 10, 2014 1:36 PM
  • Hi YakirCohen,

    Glad to hear that the issue was fixed. Would you mind sharing the solution with us so it can benifit others who have the same issue.

    >>No i have't tried Macros, i am not familiar with it's language and structure<<

    Record Macros is a feature provieded by some of Office product that help users to create their own function easily and quicly. And it is very useful to help us to learn the Office object model.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 11, 2014 8:01 AM
    Moderator
  • No i havn't solved the issue yet just no worries abt the time you said and thanx for the help..
    • Edited by YakirCohen Thursday, December 11, 2014 8:10 AM
    Thursday, December 11, 2014 8:09 AM
  • Sure, The code is this:

    xlCpage.set_HasAxis(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary, true);  
    xlCpage.set_HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlSecondary, true);

    Excel.SeriesCollection SerColl = (Excel.SeriesCollection)xlChart.Chart.SeriesCollection(MV);

    Excel.Range R1Val = SYSws.get_Range("AN11","AN"+(reader.Count+10).ToString());
    Excel.Range R1Cat = SYSws.get_Range("AD11","AD"+(reader.Count+10).ToString());

    Excel.Series WMAser = SerColl.NewSeries();
    WMAser.XValues = R1Cat;
    WMAser.Values = R1Val;
    //WMAser.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
    WMAser.Format.Line.Weight = 2;
    WMAser.Name = "WMA3";

    Where xlCpage is Excel.Chart, This code adds the 5th line into the OHLC chart, but not as a Secondary, So i need to add it manually as a Secondary to see it properly [otherwise it's just mixing with the OHLC bars and improper up/down bars is shown], then it's also doesn't sync the two axes [Primary and Secondary] well, i.e. it's not showing the same Min and Max values for the Axes, and the line and bars aren't make sense..

    This is what i got for now, it's just picking here and then online, trial and error.. now i am trying to auto Second the WMA line [Make it auto Secondary] and also automatically define the same MinMax for the Axes by values i got..

    Hope someone to have an answer as this bugger is almost a month now..

    Thanx ahead and have a good 1..

    CHEERS! 

    Anybody?.. I just need now to make the 5th linear series as a secondary, and set the max and min values for both Y axes via c#.. it's should be pretty easy, can anyone help please?..

    • Edited by YakirCohen Wednesday, December 17, 2014 6:18 PM
    Wednesday, December 17, 2014 6:18 PM