CODEWITHSUNDEEP
pythonpython-3.xpandasnumpymatplotlib
Denzel
Denzel

Reputation: 358

Plotting Moving Average on top of Candlestick Chart

I have calculated Moving Average using pandas.DataFrame.rolling. So my Dataframe look something like this, 

   CurrencyPair           TimeStamp    Open    High     Low   Close      MA50
40       EURUSD 2017-07-10 16:00:00  1.1397  1.1401  1.1396  1.1397       NaN
41       EURUSD 2017-07-10 15:00:00  1.1389  1.1396  1.1386  1.1396       NaN
42       EURUSD 2017-07-10 14:00:00  1.1393  1.1396  1.1389  1.1390       NaN
43       EURUSD 2017-07-10 13:00:00  1.1393  1.1394  1.1387  1.1391       NaN
44       EURUSD 2017-07-10 12:00:00  1.1390  1.1395  1.1386  1.1392       NaN
45       EURUSD 2017-07-10 11:00:00  1.1392  1.1393  1.1384  1.1390       NaN
46       EURUSD 2017-07-10 10:00:00  1.1387  1.1395  1.1385  1.1395       NaN
47       EURUSD 2017-07-10 09:00:00  1.1397  1.1398  1.1387  1.1389       NaN
48       EURUSD 2017-07-10 08:00:00  1.1417  1.1418  1.1399  1.1403       NaN
49       EURUSD 2017-07-10 07:00:00  1.1400  1.1416  1.1400  1.1416  1.142272
50       EURUSD 2017-07-10 06:00:00  1.1410  1.1411  1.1399  1.1399  1.142154
51       EURUSD 2017-07-10 05:00:00  1.1405  1.1409  1.1404  1.1409  1.142068
52       EURUSD 2017-07-10 04:00:00  1.1406  1.1407  1.1402  1.1404  1.141952
53       EURUSD 2017-07-10 03:00:00  1.1406  1.1407  1.1403  1.1406  1.141804

I managed to plot my candlestick chart using data from TimeStamp and OHLC but I am not sure how to plot my moving average using the same axis on top of my candlestick chart. I have tried using eurusd['MA50'].plot(ax = ax) but I get an error of ValueError: ordinal must be >= 1.

Here is my code,

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import datetime as dt
from mpl_finance import candlestick_ohlc

eurusd = pd.read_csv('fxhistoricaldata_EURUSD_hour.csv')
eurusd['TimeStamp'] = pd.to_datetime(eurusd['TimeStamp'], format = '%d/%m/%Y %H:%M')

# Set date & time range
# start_date (Y-m-d H-M) & end_date (Y-m-d H-M)
mask = (eurusd['TimeStamp'] >= '2017-07-10 3:00') & (eurusd['TimeStamp'] <= '2017-07-12 8:00')
eurusd = eurusd.loc[mask]

# Calculate Moving Averages MA50 
eurusd = eurusd.reindex(columns = np.append(eurusd.columns, ['MA50']))
eurusd['MA50'] = eurusd[['Close']].rolling(50).mean()

print(eurusd)

eurusd['TimeStamp'] = eurusd['TimeStamp'].map(lambda d: mdates.datestr2num(dt.datetime.strftime(d, '%Y-%m-%d %H:%M')))

fig, ax = plt.subplots()
ax.xaxis_date()
ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y/%m/%d %H:%M'))
ax.grid(True, color = 'k', alpha = 0.5)
plt.xticks(rotation = 45)
plt.xlabel("Date")
plt.ylabel("Price")
plt.title("EUR/USD")

candlestick_ohlc(ax, eurusd[['TimeStamp','Open','High','Low','Close']].values, width = 0.01, colorup = 'g')
eurusd['MA50'].plot(ax = ax)

plt.show()

Here is the full error log,

    ticks = self.get_major_ticks()
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\axis.py", line 1320, in get_major_ticks
    numticks = len(self.get_major_locator()())
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\dates.py", line 986, in __call__
    self.refresh()
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\dates.py", line 1006, in refresh
    dmin, dmax = self.viewlim_to_dt()
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\dates.py", line 763, in viewlim_to_dt
    return num2date(vmin, self.tz), num2date(vmax, self.tz)
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\dates.py", line 401, in num2date
     return _from_ordinalf(x, tz)
  File "C:\Users\Meng Da\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\matplotlib\dates.py", line 254, in _from_ordinalf
    dt = datetime.datetime.fromordinal(ix).replace(tzinfo=UTC)
ValueError: ordinal must be >= 1

Any help is greatly appreciated. Thank you!

Upvotes: 5

Views: 9646

Answers (2)

Denzel
Denzel

Reputation: 358

I managed to solve the issue by using ax.plot(eurusd['TimeStamp'], eurusd['MA50']) instead. Just in case anyone is encountering similar problems.

Upvotes: 3

Oleg Medvedyev
Oleg Medvedyev

Reputation: 1594

It seems like you have an issue with ax. What exactly you are trying to achieve by subplot2grid? If you remove it then it works out of box. Note that I used candlestick2_ohlc. You will also need to add dates, which I believe you figured out already.enter image description here

from pandas_datareader import data
import matplotlib.pyplot as plt
from matplotlib.finance import candlestick2_ohlc

aapl = data.DataReader('AAPL', 'google', '2017-06-01')
aapl['MA50'] = aapl["High"].rolling(10).mean()
aapl.reset_index(inplace=True)

fig, ax = plt.subplots()
plt.xticks(rotation = 45)
plt.xlabel("Date")
plt.ylabel("Price")
plt.title("EUR/USD")

candlestick2_ohlc(ax, aapl.Open, aapl.High, aapl.Low, aapl.Close, width=1, colorup='g')
aapl.MA50.plot(ax=ax)

plt.show()

Upvotes: 2

Related Questions