Dynamically updating charts in Excel
I have some graphs in excel which I update on a weekly and monthly basis.
Everytime I add a new data point, how can I have the chart automatically update so that it only shows the last e.g. 10-data point rolling period?
Example:
I have 12 monthly stock prices, one price each month. Now that we are in Feb, I might want the chart to show Feb 2010 to Feb 2010 after I have added a new column in the worksheet.
At present, I have to right click on the chart, select Source Data and then incrementally increase the the cell referencing values by one letter (column) to reflect the rolling period.
How can this be done automatically somehow?
Everytime I add a new data point, how can I have the chart automatically update so that it only shows the last e.g. 10-data point rolling period?
Example:
I have 12 monthly stock prices, one price each month. Now that we are in Feb, I might want the chart to show Feb 2010 to Feb 2010 after I have added a new column in the worksheet.
At present, I have to right click on the chart, select Source Data and then incrementally increase the the cell referencing values by one letter (column) to reflect the rolling period.
How can this be done automatically somehow?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hedgeselect,
Stage 1 is to define a set of data as a named range, but also make it a dynamic range at the same time - as per my instructions above.
Stage 2 is to use the named dynamic ranges in the Source data for the chart.
As for the file itself, can I suggest you put in random numbers instead of the data that is curently there and then upload the file. At least that way you are not revealing any confidential numbers.
Patrick
Stage 1 is to define a set of data as a named range, but also make it a dynamic range at the same time - as per my instructions above.
Stage 2 is to use the named dynamic ranges in the Source data for the chart.
As for the file itself, can I suggest you put in random numbers instead of the data that is curently there and then upload the file. At least that way you are not revealing any confidential numbers.
Patrick
ASKER
Patrick,
Apologies for the delay. I've attached anonomised data in this spreadsheet.
Could you please add the dynamic rolling returns to column to column G on the first worksheet and also create a COPY of the chart on the third worksheet so that the new chart dynamically updates only the last 12-months of rolling returns, as I add a new monthly return figure for each month? This would also need to reference the cum returns for the benchmarks in columns C, E and H on worksheet 2 please.
If you wish, please also feel free to participate in my other question which can be found at:
https://www.experts-exchange.com/questions/26867086/Dynamically-updating-ranges-in-Excel-please-update-my-spreadsheet.html
Thanks.
Master-Statistics-template-1.xls
Apologies for the delay. I've attached anonomised data in this spreadsheet.
Could you please add the dynamic rolling returns to column to column G on the first worksheet and also create a COPY of the chart on the third worksheet so that the new chart dynamically updates only the last 12-months of rolling returns, as I add a new monthly return figure for each month? This would also need to reference the cum returns for the benchmarks in columns C, E and H on worksheet 2 please.
If you wish, please also feel free to participate in my other question which can be found at:
https://www.experts-exchange.com/questions/26867086/Dynamically-updating-ranges-in-Excel-please-update-my-spreadsheet.html
Thanks.
Master-Statistics-template-1.xls
@Hedselect - dynamic rolling returns for all return columns of fund and 3 benchmarks - also updated with your updated graphs from prior question.
Dave
Master-Statistics-template-1-r5.xls
Dave
Master-Statistics-template-1-r5.xls
Unlike the last question, this one appears to want the last 12 entries (last 12 months for a calendar year). If so, then the attached modified to achieve that.
Dave
Master-Statistics-template-1-r6.xls
Dave
Master-Statistics-template-1-r6.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If the formula for one of the company lines is =SERIES(Graphs!$B$31,Graph
I have inherited the spreadsheet (which is sensitive so I cannot post it) and not sure how I can do what you have described above.
Thx.