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.

Answer 1

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.

1. On first column from row 2 to row 8, I have my values of a,b,c,... which I put 1,2,3, ...

2. On second column, I have my data.

3. On third column, I multiplied each cell in first column to corresponding cell in second column.

4. On fourth column, I squared the value of cell of first column.

5. On row 10, I added up the values of the above columns.

6. 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.

Answer 2

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

Answer 3

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