Try to find Average Standard deviation in python like in excel function avgstd()

user_14378441 0
Viewed 107

While trying to find CCI index for below attached data. I am facing an issue. If we use below code to find CCI Value for 9 period moving average the value is coming around 89. But actual CCI value is 105. The issue is way the std deviation is calculated. For CCI we have to calculate Average Standard deviation.

df["TP"] = (df['HIGH'] + df['LOW'] + df['CLOSE']) / 3
df["SMATP"]=df["TP"].rolling(n, min_periods=n).mean()
df["AVSTDTP"]=df["TP"].rolling(n, min_periods=n).std()
df["CCI"]=(df["TP"]-df["SMATP"])/(.015*df["AVSTDTP"])

Wrong CCI values image

For that I have to use for loop to find correct CCI value. Is there any better value to perform AVGSTD deviation. So correct CCI value can be found. Below code work fine but is there any better way to get the result.

df["TP"] = (df['HIGH'] + df['LOW'] + df['CLOSE']) / 3
df["SMATP"]=df["TP"].rolling(n, min_periods=n).mean()
df["AVSTDTP"]=None
#df["AVSTDTP"]=df["TP"].rolling(n, min_periods=n).std()
for row in range(len(df)):
    if(row >= n-1):
        variances=0
        for row1 in range(n):
            variances=variances+abs(df["SMATP"].iloc[row] - df["TP"].iloc[row-row1])
        df["AVSTDTP"].iloc[row] = variances/n
df["CCI"]=(df["TP"]-df["SMATP"])/(.015*df["AVSTDTP"])

Correct CCI Values

Below is the data for reference.

DATE_TIME       OPEN    HIGH    LOW CLOSE
18-08-2020 09:19    492.2   496.8   491.85  494.5
18-08-2020 09:24    494.5   498 493.25  497.45
18-08-2020 09:29    497.1   497.7   494.85  496.25
18-08-2020 09:34    496.35  496.75  495 495.5
18-08-2020 09:39    495.5   496.35  495 495.35
18-08-2020 09:44    495.3   496.45  495 496.4
18-08-2020 09:49    496.35  501.35  495.65  501.2
18-08-2020 09:54    501.2   502 499.35  501.45
18-08-2020 09:59    501.55  501.85  499.2   500.2
18-08-2020 10:04    500.45  500.65  499.4   500.15
18-08-2020 10:09    500.05  502.8   499.6   501.05
18-08-2020 10:14    501.3   504 501.3   503.5
18-08-2020 10:19    503.8   505.25  503.55  505
standard-deviation standards average python
1 Answers

Looks like you're a new user. Welcome to StackOverflow

You don't actually need to do any of that work, there is a Python module for calculating and returning Standard Deviation. numpy has a callable function std that would be extremely useful, here.

If you do want to use your own function, instead, you could organize it to look something like this.

import math

data = [2, 2, 5, 7, 1, 2, 3, 5, 7]
deviations = [] # this is where we will store the deviations 


# Calculate the mean
mean = 0 
for a in range(0, len(data) ):
    mean = mean + data[a]
mean = mean / len(data)

# Find the deviations 
for a in range(0, len(data) ):
    dev = data[a] - mean 
    deviations.append(dev)
print(deviations)

# Square those values 
for a in range(0, len(data) ):
    dev = deviations[a] * deviations[a] # squared
    deviations[a] = dev 
    
# Take len(data) minus one, and divide the sum(data^2) by that
summation = sum( deviations )
meanmean = summation / ( len(data) - 1)

# Take the square root of that value as our standard deviation 
standard = math.sqrt(meanmean)

Personally, though, I'd recommend against this; building everything from the ground up is just going to waste your time. If you need to calculate the standard deviation over time with continuously added data, I reckon there'd be two easy ways to do this:

  1. Just keep recalculating it from the entire dataset. This will be slower, but your code will be easier to look at and make adjustments to.

  2. Calculate the standard deviation the first time, and then for each new piece of data you want to add, calculate how much it will change the standard deviation, and just update the value that way. That might look something like this:

# where "add" is the new piece of data we are adding to the set 
deviation = add - mean # how much does this new piece of data deviate from the mean 
devsquare = deviation * deviation 
dev = math.sqrt(devsquare)

offset = dev / len(data)
data.append(add)

Then you'll just need to update the mean using something similar.

user_117056970
Related