How to Change values by increment in Data Table

[maani]

Hello,

I have built a model which aims to calculate various information/statistics based upon 2 inputs which I can change (see 'Mov_Avg_Chart' tab cells C6 and C8). In cell C6 I have identified the moving average period that I would like, and in cell C8 the period for moving average of the gradient/standard deviation of the gradient.

I have created 4 data tables (one for annualized return, sharpe ratio,max drawdown, and annualized risk/reward). The first data table can be seen in cells AE15:AH18. In cells AE16:AE18, there are the moving average period inputs (as in cell C6) and in cells AF 16,AG16 and AH 16 are the moving average gradient inputs (as in cell C8). What I am trying to do is create a table that will allow me to state a minimum moving average period (as in C6) and a maximum moving average period (as in C6), and an increment which I would like to look at the data in. Thus, in this example, the minimum moving average period would be 20, the maximum would be 40 and the increment would be 10. Also, I want to create a minimum moving average gradient (as in C8) and a maximum moving average gradient (as in C8) and an increment figure. Thus, in this example, it would be minimum of 75, maximum of 125 and an increment of 25. The point in the example is to see which outcomes based on the inputs are the most favorable.

I am having difficulty with setting up a minimum and maximum range and the incremental period. The trouble is, I don't know how to put these as additional inputs and have them drop into the data table (in place of cells AE16 to AE18 for example). Would anyone have any ideas? Also, the automated recalculation of the data table is needlessly slowing down the spreadsheet; is there a way that the data table can be recalculated only when there are changes to the input functions? I have set the calculation method to 'automatic except data tables' because otherwise it takes 15 minutes for the file to open. Thus, the data table isn't calculating automatically. That is, cells C6, C8 and the minimum and maximum range cells that I am seeking to create? Would someone be able to help me with this?

Kind Regards,

Maani

(I have tried to zip the file and attach it but it kept giving me the upload file failed error. Thus, I have attached it below in a link)
http://www.4shared.com/file/12573353...t_15_2009.html

[maani]

Bump No Response

[maani]

Hi,

As I had no responses, I figured I would try and re-state the problem in simpler terms. I have a 2-variable data table under the What-if-analysis tools. I would like to create the ability to set a minimum and maximum value for each input and the increment at which I would like to test. For example, if my minimum value for the column input is 10 and my maximum value for the column input is 50, and the increment that I select is 10, then I would like to see the 10,20,30,40,and 50 values in the column of my data table. I would like to have this ability for both the row and column inputs in the data table. I have spent the better part of the past couple of days trying to figure this out and search online but have had no luck. Would someone be able to help me with this?

Thanks,

Maani

[StephenR]

Here is a generic way to do this. I haven't looked at your file.

Sub x()

Dim nMin As Long, nMax As Long, nStep As Long

nMin = Application.InputBox("Enter min", Type:=1)
nMax = Application.InputBox("Enter max", Type:=1)
nStep = Application.InputBox("Enter increment", Type:=1)

With Cells(1, 1)
    .Value = nMin
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=nStep, Stop:=nMax, Trend:=False
End With

End Sub

[maani]

Hi Stephen,

Thanks for your response. Based on the code provided, where would I need to put the minimum and maximum values for the column (and row), and the incremental period for each (anywhere particular on the spreadsheet)? Also, is there anywhere in particular in the Visual Basic Editor that I should put the code? I have just attached a much smaller and condensed version of my spreadsheet. You can find the 4 data table in cells AE15:AH37 of the 'Mov_Avg_chart' tab. Additionally, my partially constructed optimisation table is in cells A21:B27 of the 'Mov_Avg_chart' tab.

Thanks for your time.

Maani

[StephenR]

Maani - can you explain a bit more what you're trying to do. I'm struggling to understand it all. Can you add your expected output based on your data?

[maani]

EDIT:

Hello Stephen,

My apologies. I had made a mistake in adjusting the code to my worksheet. When I inputted your code and adjusted it correctly, I receive the same charts as you. I just have 2 final questions for you (I promise that will be it )

1) The chart is taking the row and column headers as the x-axis and y-axis of the charts. However, on the y-axis it states 'Series 1', 'Series 2', 'Series 3', etc. and on the x-axis it states (1,2,3,4,5,etc). Would it be possible to have these replaced with the actual values in the x and y-axes? As it stands, it seems to be listing them by the number of values in the x-axis and y-axis instead of putting the exact values from the row and column headers as the axes values. Also, it seems to have the x and y-axes mixed up. The values on the x-axis are supposed to be the changing input from C8, and the values on the y-axis are supposed to be the changing inputs from C6. Currently, it seems to be doing the opposite.

2) Once I hit the update button and the data tables populate and the charts load, the calculation method changes from 'automatic except for data tables' to 'manual.' Is there a way to have this remain as 'automatic except for data tables'?

Thanks again,

Maani

[StephenR]

Maani - add the bold lines below. That seems to name the series - they y-axis? Am slightly flummoxed by x-axis, it doesn't seem to be labelled.

ActiveChart.Location xlLocationAsObject, "Charts"
For j = 1 To rData.Rows.Count - 1
    ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)
Next j

That calculation thing doesn't happen for me - could you have set something manually? O/w I'm not sure, perhaps somebody else will drop in.
EDIT: added last line below - does this cover the other axis? Not sure if they're the right way round. As you may have picked up, this is flying by the seat of pants...

For j = 1 To rData.Rows.Count - 1
            ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)
Next j
ActiveChart.SeriesCollection(1).XValues = rData(1, 2).Resize(, rData.Columns.Count - 1)

[maani]

Thanks Stephen. Where should I insert these codes within the larger code you sent me? Could you send the whole code with this included within it?

Does the code adjust for the x and y-axes being mixed up? That is,will it switch the x and y-axes on the charts (not sure if you had gotten my last edit prior to sending your mail).

Thank you so much for all of your patience and help!

Maani

[StephenR]

Here's the full code. You lost me a bit there so run it and report back on what needs changing.

Sub x()

Dim nMinAP As Long, nMaxAP As Long, nStepAP As Long
Dim nMinAG As Long, nMaxAG As Long, nStepAG As Long
Dim i As Long, nLastcol As Long, nlastrow As Long
Dim rRow, rCol, rRef, j As Long
Dim rStart As Range, rData As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Sheets("Charts").Delete
On Error GoTo 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Charts"

Sheets("Mov_Avg_Chart").Activate
nMinAP = Range("B22").Value
nMaxAP = Range("B23").Value
nStepAP = Range("B24").Value
nMinAG = Range("B25").Value
nMaxAG = Range("B26").Value
nStepAG = Range("B27").Value
Set rRow = Range("C8")
Set rCol = Range("C6")
rRef = Array("G7", "G9", "G10", "G11")

nlastrow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
nLastcol = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If nLastcol < 9 Then
    Range(Cells(15, "I"), Cells(nlastrow, "I")).Clear
Else
    Range(Cells(15, "I"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("I15")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 4
        .Offset(1).Value = nMinAP
        .Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=nStepAP, Stop:=nMaxAP
        .Offset(, 1).Value = nMinAG
        .Offset(, 1).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=nStepAG, Stop:=nMaxAG
        With .CurrentRegion
            .Rows(1).Font.Bold = True
            .Columns(1).Font.Bold = True
        End With
    End With
    Set rData = rStart.Offset(1, 1).CurrentRegion
    With rData
        .NumberFormat = "0.00"
        .Table RowInput:=rRow, ColumnInput:=rCol
        With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
            .Select
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range("Mov_Avg_Chart!" & .Address)
            ActiveChart.ChartType = xlSurfaceTopView
            ActiveChart.Location xlLocationAsObject, "Charts"
            For j = 1 To rData.Rows.Count - 1
                ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)
            Next j
            ActiveChart.SeriesCollection(1).XValues = rData(1, 2).Resize(, rData.Columns.Count - 1)
            Sheets("Mov_Avg_Chart").Activate
        End With
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

[maani]

Hi Stephen,

When I put the new code in, it gave me the error 'Run-time error 1004: Invalid parameter.'

When I hit debug, it highlighted the below portion of code

ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)

Maani

[maani]

Yes, I totally wonder if we'll ever get there. But I'm learning quite a bit, which I am very thankful for to you.

The code you provided works and the axes are labeled with the row and column headers. The only issue is that the axes are mixed up. That is, the values on the x-axis should be on the y-axis and the values on the y-axis should be on the y-axis. I tried to change the row and column values in the code you provided but that didn't work.

[maani]

Hi Stephen,

As I was playing around with the spreadsheet this weekend, I kept noticing that whenever the data table values are recalculated in the worksheet, the calculation method changes from 'automatic except data tables' to 'automatic'. Could it possibly be due to

.Calculation = xlCalculationAutomatic

being part of the code? To see this error, try checking the calculation method in the worksheet and then change values in the optimisation table and hit the 'update' button. When you do this, see if you get the workbook recalculating. It would be fine, but the actual workbook I have is very large and this recalculation takes a very long time and slows down the workbook.

Also, as the axes were mixed up, I tried switching the row and column in the code you provided but it gave me a invalid parameter issue.

Could you please advise on how I would switch the axes based on your latest code?

Thanks,

Maani

[StephenR]

For calculation, try adding/amending:

ActiveSheet.Calculate 'added
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
       .Calculation = xlCalculationSemiautomatic ' amended
End With

For the axes, it's not clear to me how to achieve what you want - as I say perhaps post in the chart forum where no doubt there will be an expert or two. I can't really visualise how the chart would be different if you switched axes?

[maani]

Thanks for your reply Stephen. When amending the calculation code, sometimes it gives me a 'run time error-1004' and when I hit debug it highlights the following bit of code

ActiveChart.SeriesCollection(j).Name = rStart.Offset(, j)

However, other times it works fine. I think the issue is when I increase the range of values in the optimisation table. It is at those times that it gives me the run-time error.

Is the revised code I have below correct?

Sub x()

Dim nMinAP As Long, nMaxAP As Long, nStepAP As Long
Dim nMinAG As Long, nMaxAG As Long, nStepAG As Long
Dim i As Long, nLastcol As Long, nlastrow As Long
Dim rRow, rCol, rRef, j As Long
Dim rStart As Range, rData As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Sheets("Charts").Delete
On Error GoTo 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Charts"

Sheets("Mov_Avg_Chart").Activate
nMinAP = Range("B22").Value
nMaxAP = Range("B23").Value
nStepAP = Range("B24").Value
nMinAG = Range("B25").Value
nMaxAG = Range("B26").Value
nStepAG = Range("B27").Value
Set rRow = Range("C8")
Set rCol = Range("C6")
rRef = Array("AF7", "AF9", "AF10", "AF11")

nlastrow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
nLastcol = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If nLastcol < 9 Then
    Range(Cells(21, "AE"), Cells(nlastrow, "AE")).Clear
Else
    Range(Cells(21, "AE"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("AE21")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 17
        .Offset(1).Value = nMinAP
        .Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=nStepAP, Stop:=nMaxAP
        .Offset(, 1).Value = nMinAG
        .Offset(, 1).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=nStepAG, Stop:=nMaxAG
        With .CurrentRegion
            .Rows(1).Font.Bold = True
            .Columns(1).Font.Bold = True
        End With
    End With
    Set rData = rStart.Offset(1, 1).CurrentRegion
    With rData
        .NumberFormat = "0.00"
        .Table RowInput:=rRow, ColumnInput:=rCol
        With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
            .Select
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range("Mov_Avg_Chart!" & .Address)
            ActiveChart.ChartType = xlSurfaceTopView
            ActiveChart.Location xlLocationAsObject, "Charts"
            For j = 1 To rData.Columns.Count - 1
                ActiveChart.SeriesCollection(j).Name = rStart.Offset(, j)
            Next j
            ActiveChart.SeriesCollection(1).XValues = rStart.Offset(1).Resize(rData.Rows.Count - 1)
            Sheets("Mov_Avg_Chart").Activate
        End With
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i
ActiveSheet.Calculate
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
       .Calculation = xlCalculationSemiautomatic 
End With
End Sub

I have also posted the charting questions for the axes in the charting forum. Thanks again,

Maani

[StephenR]

The code for the chart was generated by the macro recorder so I'm not completely au fait with it, and as you suggest larger tables do seem to throw up errors. In any case, Andy Pope seems to have sorted it out now so I think everything has been resolved?

[maani]

Yes, you are correct. Thanks so much with your help with creating a fix; I cannot begin to thank you for all you have done.

Regards,

Maani