22

I am trying to use Python (with Pandas) to calculate the 20-day Exponential Moving Averages (EMA) of daily stock data for Intel (INTC). Pandas has a number of ways of doing this, and I've also tried stockstats, which runs on Pandas, but they never return the same EMA as I get from stock/finance websites.

I've double checked the close prices, and they match, but the EMA always comes out "wrong".

This is the CSV I'm using: INTC Stock Data

It contains the daily Date, Month Name, Open, High, Low, Close, Day Avg, and Volume for Intel's stock (Ticker: INTC) from 4/20/2016 to 2/1/2018.

When I look to the bigger stock websites like MarketWatch or Fidelity, their numbers don't match mine. They match each other, but not me.

For example...

df2['Close'].ewm(span=20,min_periods=0,adjust=False,ignore_na=False).mean()

or...

df2['Close'].ewm(span=20, min_periods=20, adjust=True).mean()

or...

df2["Close"].shift().fillna(df["Close"]).ewm(com=1, adjust=False).mean()

Give me EMA's for 2/1/2018 like $44.71, $47.65, $46.15, etc. when the real 20-Day EMA on any finance site is $45.65. And I get the wrong numbers no matter what date I try to compute the EMA for. It's even wrong when I just try for 5-Day EMAs.

I've read, watched and followed tutorials on the subject, but their results also don't match the accepted/published EMA's you'd find on any finance site. The people creating the tutorials and videos simply never check them against each other after Panda's crunches the numbers. And I need my numbers to match.

How do I arrive at the same figures every other finance site on the internet is getting for EMAs? I don't think this has anything to do with adjusted close prices because I'm using old/settled data and my close prices and dates are the same as theirs.

smci
  • 32,567
  • 20
  • 113
  • 146
Rob M
  • 298
  • 1
  • 4
  • 18

3 Answers3

54

Sort the DataFrame so that the dates are in increasing order. Since your data is in decreasing order by date, if you don't sort the dates first, your ewm calculation exponentially weights the earliest dates the most, rather than the latest date (as it should be).

import pandas as pd

df = pd.read_csv('intc_data.txt', parse_dates=['Date'], index_col=['Date'])
df['backward_ewm'] = df['Close'].ewm(span=20,min_periods=0,adjust=False,ignore_na=False).mean()
df = df.sort_index()
df['ewm'] = df['Close'].ewm(span=20,min_periods=0,adjust=False,ignore_na=False).mean()
print(df[['ewm', 'backward_ewm']].tail())

yields

                  ewm  backward_ewm
Date                               
2018-01-26  45.370936     48.205638
2018-01-29  45.809895     48.008337
2018-01-30  46.093714     47.800794
2018-01-31  46.288599     47.696667
2018-02-01  46.418256     47.650000

This agrees with Marketwatch which says the EWMA(20) on 2018-02-01 was 46.42.

enter image description here

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • 5
    You are a damned wizard :) This works like a charm, thank you SO much! I'm still learning Pandas (as I'm sure you guessed). Cheers! – Rob M Feb 13 '18 at 23:31
9

I suggest using Pandas TA to calculate technical indicators in python. I find it more accurate and has many more indicators than the ones that come with pandas.

Using Pandas TA, the 20 period exponential moving average is calculated like:

import pandas_ta as ta

data["EMA20"] = ta.ema(df2["Close"], length=20)
carloslockward
  • 335
  • 3
  • 8
0

If you want to use EMA, you need more data, such as to compute EMA (period = 14), you need at least 28 K line data Below I provide OKX ETH - USDT - SWAP - 1 h to do the test,In the code to use limit = 14 is wrong,Limit effect is right = 100:

import pandas as pd
import requests
import json
import time

#Data:OKX ETH-USDT-SWAP 1H
limit= 20 #When using limit = 100 data is normal
url = 'https://www.okx.com/api/v5/market/candles?instId=ETH-USDT-SWAP&bar=1H&limit={}'.format(limit)    
headers={'User-Agent' : 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36'}
result =requests.get(url=url,headers=headers).json()
df=pd.DataFrame(result['data'])
df = df.rename(columns={0: 'open_time', 1: 'open', 2: 'high', 3: 'low',  4: 'close', 5: 'volz',6:'voleth',7:'volusdt',8:'KlineState'})
for i in list(df):
    df[i]=list(reversed(df[i]))   


#Pandas ema14 example:
'''
DataFrame.ewm(com=None, span=None, halflife=None, alpha=None, min_periods=0, adjust=True, ignore_na=False, 
axis=0, times=None, method='single')[source]
'''
period=14
while True:
    example_ema_1=df['close'].ewm(span=period).mean().values[-1].round(2)

    example_ema_2=df['close'].ewm(span=period, adjust=False).mean().values[-1].round(2)

    example_ema_3=df['close'].ewm(span=period, adjust=False,ignore_na=True).mean().values[-1].round(2)

    print('example_ema_1',example_ema_1)
    print('example_ema_2',example_ema_2)
    print('example_ema_3',example_ema_3)

    time.sleep(1)
ZRT_1998
  • 1
  • 1