Calculate if trend is up, down or stable

Question

I'm writing a VBScript that sends out a weekly email with client activity. Here is some sample data:

a       b       c       d       e      f        g
2,780   2,667   2,785   1,031   646    2,340    2,410

Since this is email, I don't want a chart with a trend line. I just need a simple function that returns "up", "down" or "stable" (though I doubt it will ever be perfectly stable).

I'm terrible with math so I don't even know where to begin. I've looked at a few other questions for Python or Excel but there's just not enough similarity, or I don't have the knowledge, to apply it to VBS.

My goal would be something as simple as this:

a       b       c       d       e      f        g        trend
2,780   2,667   2,785   1,031   646    2,340    2,410    ↘

If there is some delta or percentage or other measurement I could display that would be helpful. I would also probably want to ignore outliers. For instance, the 646 above. Some of our clients are not open on the weekend.

Perhaps you need to calculate the slope of the best fit line. If the slope is positive, then up and if negative then down. If the slope is zero, then stable. — S L, Jun 04 '15 at 16:47
Find the value of Beta in [this](http://en.wikipedia.org/wiki/Simple_linear_regression#Fitting_the_regression_line) formula. For 'x' variable here you are using alphabets, 'a', 'b', 'c' ... you need to assign numerals for that. If you are taking these data in equal interval, then assign natural number 0 1 2 3 .. — S L, Jun 04 '15 at 17:03
And yes the values are already in an array, the letters above were just for readability. — , Jun 04 '15 at 18:13
It's not difficult i'll write the answer on how to do it .. you only need to calculate the average of it. add up the variables etc etc. — S L, Jun 04 '15 at 18:21

score 19 · Accepted Answer · answered Jun 04 '15 at 18:53

First of all, your data is listed as

a       b       c       d       e      f        g      
2,780   2,667   2,785   1,031   646    2,340    2,410

To get a trend line you need to assign a numerical values to the variables a, b, c, ...

To assign numerical values to it, you need to have little bit more info how data are taken. Suppose you took data a on 1st January, you can assign it any value like 0 or 1. Then you took data b ten days later, then you can assign value 10 or 11 to it. Then you took data c thirty days later, then you can assign value 30 or 31 to it. The numerical values of a, b, c, ... must be proportional to the time interval of the data taken to get the more accurate value of the trend line.

If they are taken in regular interval (which is most likely your case), lets say every 7 days, then you can assign it in regular intervals a, b, c, ... ~ 1, 2, 3, ... Beginning point is entirely your choice choose something that makes it very easy. It does not matter on your final calculation.

Then you need to calculate the slope of the linear regression which you can find on this url from which you need to calculate the value of b with the following table.

enter image description here

On first column from row 2 to row 8, I have my values of a,b,c,... which I put 1,2,3, ...
On second column, I have my data.
On third column, I multiplied each cell in first column to corresponding cell in second column.
On fourth column, I squared the value of cell of first column.
On row 10, I added up the values of the above columns.

Finally use the values of row 10.

        total_number_of_data*C[10] - A[10]*B[10]
  b =  -------------------------------------------
        total_number_of_data*D[10]-square_of(A[10])

the sign of b determines what you are looking for. If it's positive, then it's up, if it's negative, then it's down, and if it's zero then stable.

This almost makes sense. Can you plug the numbers into the equation and show it to me that way? I think then I'll understand. (I'm confused by `total_number_of_data` currently) — , Jun 05 '15 at 13:20
((7*54734) - (28*14660)) / ((7*140) - (28^2)) Is that right? — , Jun 05 '15 at 13:25
@ScottBeeson `total_number_of_data=7` and check it [out here](https://www.wolframalpha.com/input/?i=linear+fit+2780%2C++2667%2C++2785%2C+1031%2C+646%2C+2340%2C+2410&dataset=&equal=Submit) yes it must be -139.393 — S L, Jun 05 '15 at 13:35

score 3 · Answer 2 · answered Aug 02 '21 at 00:38

This was a huge help! Here it is as a function in python

def trend_value(nums: list):
    summed_nums = sum(nums)
    multiplied_data = 0
    summed_index = 0 
    squared_index = 0

    for index, num in enumerate(nums):
        index += 1
        multiplied_data += index * num
        summed_index += index
        squared_index += index**2

    numerator = (len(nums) * multiplied_data) - (summed_nums * summed_index)
    denominator = (len(nums) * squared_index) - summed_index**2
    if denominator != 0:
        return numerator/denominator
    else:
        return 0

val = trend_value([2781, 2667, 2785, 1031, 646, 2340, 2410])
print(val)  # -139.5

score 0 · Answer 3 · answered Apr 13 '21 at 17:33

in python:

def get_trend(numbers):
rows = []
total_numbers = len(numbers)
currentValueNumber = 1
n = 0
while n < len(numbers):
    rows.append({'row': currentValueNumber, 'number': numbers[n]})
    currentValueNumber += 1
    n += 1
sumLines = 0
sumNumbers = 0
sumMix = 0
squareOfs = 0
for k in rows:
    sumLines += k['row']
    sumNumbers += k['number']
    sumMix += k['row']*k['number']
    squareOfs += k['row'] ** 2
a = (total_numbers * sumMix) - (sumLines * sumNumbers)
b = (total_numbers * squareOfs) - (sumLines ** 2)
c = a/b
return c

trendValue = get_trend([2781,2667,2785,1031,646,2340,2410])
print(trendValue) # output:  -139.5

Calculate if trend is up, down or stable

3 Answers3