How to Change values by increment in Data Table

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

Hi Stephen,

Thanks for your response. The TABLE formula is part of the Data Table calculation. In excel 2007, under the data tab, then what-if-analysis, you select data table, and highlight the entire data table (my first data table for example are cells AE15:AH18); then it asks you what your column and row input are. In my case, it is cells C6 and C8. What it is doing is calculating other scenarios and the results based on those inputs (C6 and C8) changing to the values that are in the row (AE16:AE18) and column headings (AF15:AH15) in the data table. So, it is a matrix of sorts that shows me the various results if I optimise my inputs. For some reason the formulas in the data table show up as an array formula with TABLE. However, it is not a formula in itself as far as I know; just something that comes into the cells when you use data tables.

With regard to the setting of the row/column values in my data tables (in this case 3 rows down and 3 columns across), it is a totally random. That is, the numbers of rows/columns will depend on the minimum value in the optimisation table (see cells B22 and B24) and the increment with which I want to see the data (see cells B23 and B25). Ideally, I would like to set a minimum and maximum value and an increment. For example, I may specify the minimum value as 10 and the maximum as 50, and the increment as 10. In this situation, it would show me 5 rows going down in the data table (10,20,30,40,50). The reason I have left out maximum in the optimsation table is because I do not know how to automate the data table while including maximum in the optimsation table (I have simply linked it via formulas). That is why I thought that code would perhaps be a better way to go about this.

With this in mind, would the code you provided be a suitable option for me? I am a novice at code and thus am still not too comfortable with the in's and out's of it all.

Thanks for your patience,

Maani

Hello Stephen,

Thanks for your response and for providing the code. When I downloaded your file, it said that excel opened the file by removing certain elements, so I'm not sure I am seeing everything you did. However, when I recreated what you did in my spreadsheet, I was unable to get the data to populate.

I went into the Visual Basic Editor, added a new module and put in the code you provided. Additionally, I copied your optimisation table and copied the 'press me' button as well. I set Q1 and Q2 equal to cells C8 and C6. However, I could not get the correct data to populate. Should I be filling in cell A1 in Sheet 2 to the statistic that I am seeking or just leave it empty? I have put A1 equal to the annualized return statistic because that is the statistic that I want to optimise. However, the cells inside the data table simply equal the value of cell A1 even though the formula including TABLE is in the cells. Would you be able to explain what further I am supposed to do to get the data to populate? Or perhaps send a different version of your excel file?

Also, will this populate for four data tables?

Thanks,

Maani

Strange, can you get this one to work?

Or try this 2003 version - I'm floundering here really! If neither of these work, try attaching the file you worked up and I'll see if I can figure it out that way.

Thanks so much Stephen! That did it and now it works!

Is there a way to have the data table update when any of the cells B22:B27 change? Currently, the data tables don't update unless we hit update. The other macros I have in the workbook were based on cell color, so that the graphs update when the colored cells change. However, since that has already been used, I wanted to get something similar to that, such as when the cells B22:B27 change and not have the button need to be pressed.

Also, is there a way that the column and row values in the data tables are bolded automatically and the optimised statistic is highlighted?

Thanks so much again.

Maani

Maani - glad we got there. Have amended the block of code below, and picked out the optimised stat in green but you can change to suit:

Please Login or Register  to view this content.

Add this line to your existing worksheet change code to make it run automatically. A possible snag with this approach is that if you want to change more than one parameter it will get rather slow.

Please Login or Register  to view this content.

EDIT: the code may be speeded up by adding this line after the EnableEvents line

Please Login or Register  to view this content.

Remember to turn it back on.

EDIT: Thanks Stephen. This works perfectly. How does the code know when I am finished updating the optimisation table inputs? That is, what if I only update 3 inputs (out of the 6) or if I update all 6. How does it know when to start updating the table? Also, what would I need to change on the code in order to make the results in the data tables come out to 2 decimal places (it currently comes out to 9 decimal places)? As the spreadsheet is currently set up, the calculation method is set to automatic except for data tables. However, when saving the document before closing, it recalculates the data tables again. Is there a way to make it so that before saving the worksheet it doesn't recalculate the data tables (as this takes quite a bit of time).

Finally, is there a way to have a contour chart (Insert»Other Charts»Surface»Contour) dump out into another worksheet automatically for each data table? I would do this the usual way of simply adding a chart after the data table is populated, but as the data table ranges will change everytime (and the row and column ranges), I wanted to see if there was a way to automate this through code.

Also, what did you mean by remember to turn it back on. I am a bit confused on what I should turn back on.

Thanks,

Maani



Thanks,

Maani

Maani - this should adjust the range for the charts:

Please Login or Register  to view this content.

For your question about chart placement and naming I suggest you start a new thread, probably in the Chart forum, as it is sort of separate and it's not really my area.

EDIT:

Hi Stephen,

Thanks again. I will put a post in the chart forum.

The code you provided seems to be taking the data from each row. For example, AE22:AK22, instead of taking the entire data stream of AF22:AK26. I just don't want it to include the row and column headers as is currently being done. Would you know of a way to do this?

Finally, would you be able to send me the same version of the code that you have provided (your most recent one) with the "press me" button being used instead of the automatic update? I'm just not sure how to change the code to reflect the use of the button.

Maani

Oops, I posted the wrong code. I've amended the code in the my previous post. It puts all the charts on top of each other but they are all there.

EDIT: can you check the graphs are correct as somehow I lost the code I wrote and which worked, and I'm not entirely sure if the current charts are showing the right data.

Hi Stephen,

The latest code you sent is mixing up the data more than the previous one. The charts are now taking pieces of 2 data tables. It doesn't seem to be adjusting to the varying sizes of the data tables (as the row and column dimensions change). For example, in one example it took part of one data table, and another part of another data table.

Could you confirm that the code you provided (see below) for the updating of the data tables is based on changing the cells in the optimisation table?

The code you sent before (see below) this one was the one that had worked the best except that it was plotting the row and column headers as values in the chart (instead of using the row and column headers as axis values). Also, the top left value shouldn't be plotted or put on the axis at all.

Interestingly, if I change the values in the optimisation table, it simply adds 4 more charts; it doesn't delete the previous 4. That is, I then have 8 charts. Is there a way to avoid this?

Please Login or Register  to view this content.

Sorry for the confusion. Is there a way that it could be adjusted to only plot the data in the data table and use the row and column headers as the axes values (and top-left cell not charted)?

Thanks,

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

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.

Please Login or Register  to view this content.

[/B]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...

Please Login or Register  to view this content.

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

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.

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

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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

Maani