View Full Version : Extracting Chart Data and Automated VBA Procedure for Slope

06-23-2009, 03:08 AM
Hello all,

I have an interactive moving average chart that needs a few modifications that I cannot seem to accomplish with my lack of VBA. Currently, I have the price and dates for a set of data. I have written a code that automatically plots the moving average of this price data on the chart. It also allows for the moving average to be dynamic (that is, I have constructed it to be adjustable). The problems I am having is as follows:

1) I want to obtain the slope of the individual moving averages and plot the slopes on the same chart as the price and moving average data. However, the only way I know how to do this would be to manually calculate the slope of the moving averages. But I cannot extract the moving average data points from the chart to a workbook because the trendlines do not contain data. The moving averages are calculated automatically by excel through a vba procedure and plotted on the chart. When I tried to extract the data from the chart through a VBA procedure, it simply extracted the price data from the chart (which I already had) and not the moving average data points.

Ideally, I would like to create a VBA procedure that calculates the slope of the individual moving averages and plots them on the chart. I would think it may be possible as I have already put in a VBA procedure that calculates moving averages based on the price and plots them on the chart (please see macro entitled 'Extract Chart Data').

I am a VBA beginner and any help would be appreciated. I have attached my spreadsheet below.



06-24-2009, 04:32 PM
See attached which adds a column of moving average values and a column of MA gradient values to the Data sheet. Only the gradient is plotted on the chart. I did briefly plot the calculated MA to check that it matched yours.