Moving Averages and Trend Lines (1 Viewer)

[guytort2]

Hi. I am trying to understand how to determine if a line within a graph is sloping up or down using VBA. My app is related to Baseball. In Excel, I can create a Quick Analysis using 2 variables, Today's Batting Average (Today's Hits/Todays At Bats) and Overall Batting Average (Overall Hits / Overall At Bats) over time (a Game Date). So plotting Today's Average on the y axis and Game Date on the x axis as a line chart. Then adding Overall Average as a separate line on the chart. Finally, adding a Moving Average dotted line which shows the trend as it relates to the Overall Batting Average. On any particular day, the slope of the moving average trend line can be trending up, down or be flat. Very easy to see on the graph for a single player in Excel. I have an Access table with all of this baseball data, their hits and batting averages for each day of the baseball season. So, this is what I am looking for - if I were to take say 10 consecutive game dates (using 10 , but could be any amount of dates) for a player, on that 10th and last game date, I would like to determine whether the Moving Average which is tied to the Overall Batting Average is trending up, down or flat - within code. Any code snippets would be greatly appreciated! Here is some sample data


Player John Smith

Game Date - Overall Average - Today's Average

March 28 ---------- .400 --------------.400

March 29 -----------.300 --------------.200

March 30 -----------.429 --------------.750

March 31 -----------.533 -------------1.000


If you add to an excel spreadsheet and do a quick Analysis on that data adding series 1, series 2 and moving average you will see what I am trying to describe (hopefully ).

 

[June7]

So exactly what is the formula you want to code? How would you manually calculate this?

 

[plog]

This is the slope formula for a line from points A to B:

(By - Ay) / (Bx - Ax)

A negative number means its sloping down, a positive, sloping up.

So create a function, pass it your 4 pieces of data (Ax, Ay, Bx, By), implement the above formula and then determine if its a negative or positive number and return the appropriate response.

 

[The_Doc_Man]

Are interested in doing more than just a day-to-day or end-to-end trend line? If so, take a look at the following article.

https://access-programmers.co.uk/forums/showthread.php?t=289522&highlight=linear+regression

 

[June7]

I plotted your data in Excel. X-axis is dates and y-axis is the averages. Two series: Overall and Today's.

Using the sample data, Overall slope is 0.04433, Today's slope is 0.221667.

That the type of result you want?

 

[isladogs]

I plotted your data in Excel. X-axis is dates and y-axis is the averages. Two series: Overall and Today's.

Using the sample data, Overall slope is 0.04433, Today's slope is 0.221667.

Am I being dense here? Firstly there is no sample data for today. Secondly there is only one record per date. How can you do a trend for 'today's data?

 

[June7]

Beats me how OP derived that data nor why it has those headers. Numbers are numbers. They plot. Whether or not they mean anything is another matter. And I don't know what was meant by "moving average" and how that would be displayed on graph. An image of OP-created graph might be informative.