matplotlib how to parse right datetime factor of the candlestick image

Question

I have a excel file download on internet. file looks like this:

> 2017-08-01 00:00  1269.50000  1269.70000  1269.50000  1269.670000
> 2017-08-01 00:01  1269.690000 1269.70000  1269.650000 1269.670000
> 2017-08-01 00:02  1269.680000 1269.690000 1269.650000 1269.670000
> 2017-08-01 00:03  1269.650000 1269.70000  1269.650000 1269.680000
> 2017-08-01 00:04  1269.660000 1269.70000  1269.560000 1269.630000
> 2017-08-01 00:05  1269.650000 1269.680000 1269.620000 1269.660000
> 2017-08-01 00:06  1269.670000 1269.690000 1269.640000 1269.660000
> 2017-08-01 00:07  1269.680000 1269.680000 1269.540000 1269.580000
> 2017-08-01 00:08  1269.570000 1269.60000  1269.540000 1269.550000
> 2017-08-01 00:09  1269.570000 1269.590000 1269.540000 1269.570000

first I load the file into numpy.array filestyle: it look like this:

array([[Timestamp('2017-07-02 22:01:00'), 1241.65, 1241.85, 1241.63,
        1241.85],
       [Timestamp('2017-07-02 22:02:00'), 1241.95, 1241.95, 1241.85,
        1241.95],
       [Timestamp('2017-07-02 22:03:00'), 1242.05, 1242.15, 1242.05,
        1242.15],
       ..., 
       [Timestamp('2017-07-31 23:57:00'), 1267.49, 1267.67, 1267.45,
        1267.63],
       [Timestamp('2017-07-31 23:58:00'), 1267.65, 1267.67, 1267.4, 1267.56],
       [Timestamp('2017-07-31 23:59:00'), 1267.55, 1267.55, 1267.36,
        1267.43]], dtype=object)

because matplotlib.candelstick_ohlc command only support datenum format, I transform the [0]column into this:

array([[736512.9173611111, 1241.65, 1241.85, 1241.63, 1241.85],
       [736512.9180555556, 1241.95, 1241.95, 1241.85, 1241.95],
       [736512.91875, 1242.05, 1242.15, 1242.05, 1242.15],
       ..., 
       [736541.9979166667, 1267.49, 1267.67, 1267.45, 1267.63],
       [736541.9986111111, 1267.65, 1267.67, 1267.4, 1267.56],
       [736541.9993055556, 1267.55, 1267.55, 1267.36, 1267.43]], dtype=object)

then I use matplotlib.candlestick_ohlc to plot candlestick picture but result is not good,looks like this command did not parse the date rightly(not just can not show on the X-axis but also can not map the data in the right position of XY-graph, it causes the whole image to be a mass).image like this: image1 can not find even a single symbol

then I split o,h,l,c information from file and use matplotlib.candlestick2_ohlc ,it can work but does not contain time factor because of the command not having one,image like this: image2 overview image. it can expand to detail which can show one candlestick

how to figure out the problem,i think it has two way:(1)use matplotlib.candlestick_ohlc to parse right datetime, so it can map the information in the right position of XY-graph;(2)use matplotlib.candlestick2_ohlc and add time factor into it. my code like this:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import datetime as dt
import matplotlib.finance as fin
import matplotlib as mpl

df_data=pd.read_excel('HSI.xlsx')
candle_1=df_data.values

for record in candle_1:
    record[0]=mpl.dates.date2num(np.datetime64(record[0]).astype(dt.datetime))

t=df_data.iloc[:,0]
o=df_data.iloc[:,1]
h=df_data.iloc[:,2]
l=df_data.iloc[:,3]
c=df_data.iloc[:,4]

fig,ax1=plt.subplots()

##市场数据图表实现
fin.candlestick_ohlc(ax1,candle_1,colorup='r', colordown='g', alpha=0.75,width=0.4)
plt.style.use('ggplot') 
plt.show()

and another just change a little:

fin.candlestick2_ohlc(ax1,o,h,l,c,colorup='r', colordown='g', alpha=0.75,width=0.4)

the right candlestick image when i use candlestick2_ohlc command to picture,but this command has no time factor:

thanks, I got that, I mean in which module do you find it? I cannot reproduce your code without knowing that and I cannot find it in datetime or numpy. Where do you import it from? — Vincenzooo, Sep 09 '17 at 13:48
i used pandas.read_excel to read hsi.xlsx to get a dataframe and first time column type is datetime64, then used dataframe.value and got this numpy.array automatically. the excel is like what i paste on. i can upload the excel file tomorrow maybe the original file is the key？ — wang ning, Sep 09 '17 at 16:16
dataframe.values , because df_data is a pandas.DataFrame type,so i can use df_data.values directly — wang ning, Sep 09 '17 at 16:58
i just import module like the first 6 lines of the code,nothing more — wang ning, Sep 09 '17 at 17:00
It would be easier if you provide the excel file, I tried to reproduce it from text, but don't know how to merge the first two columns in a single field. I guess I can make it with some effort, but let me know if the solution I posted works already. Also, you don't specify which formatting you want for the label, so I have put a couple of generic solution, I imagine you can work it out. As I said it's not tested, so I hope it works. — Vincenzooo, Sep 09 '17 at 21:30

Vincenzooo · Answer 1 · 2017-09-11T15:12:06.993

0

You can try this:

ax1.xaxis_date() #convert x axis labels to date
#you can rotate the labels following last line of next code segment
plt.draw()

If you want a specific format or spacing, you can follow the example here and adjust it to your case: http://matplotlib.org/examples/pylab_examples/finance_demo.html something like this:

from matplotlib.dates import DateFormatter, WeekdayLocator,DayLocator, MONDAY

mondays = WeekdayLocator(MONDAY)        # major ticks on the mondays
alldays = DayLocator()              # minor ticks on the days
weekFormatter = DateFormatter('%b %d')  # e.g., Jan 12
dayFormatter = DateFormatter('%d')      # e.g., 12

ax1.xaxis.set_major_locator(mondays)
ax1.xaxis.set_minor_locator(alldays)
ax1.xaxis.set_major_formatter(weekFormatter)

plt.setp(plt.gca().get_xticklabels(), rotation=45, horizontalalignment='right')

After getting the data file (from here DAT_XLSX_XAUUSD_M1_201708.xlsx shortened to first 109 lines for test purpose), this is the complete code:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import datetime as dt
import matplotlib.finance as fin
import matplotlib as mpl

df_data=pd.read_excel('HSI_test.xlsx')
candle_1=df_data.values

for record in candle_1:
  record[0]=mpl.dates.date2num(np.datetime64(record[0]).astype(dt.datetime))

fig,ax1=plt.subplots()

fin.candlestick_ohlc(ax1,candle_1,colorup='r', colordown='g', alpha=0.75,width=0.4)
plt.style.use('ggplot') 
ax1.xaxis_date() #convert x axis labels to date
plt.show()

and the result:

edited Sep 11 '17 at 15:12

answered Sep 09 '17 at 21:27

Vincenzooo

2,013
1
19
33

unfortunately, it can not work. I think ax1.xaxis_date() is used to convert str labels to dateformat? but the candlestick code maybe not parse date label correct, and candlestick2 do not add labels in xaxis just rank data in series. and I do not find a way to upload xlsx file. I paste on the internet web where I download the data file(http://www.histdata.com/download-free-forex-historical-data/?/excel/1-minute-bar-quotes/XAUUSD). if you are interested in finding the solvement of this problem, you may download files on the web.thx. – wang ning Sep 10 '17 at 03:31
thanks for the file, indeed I can reproduce your code now. After the plot is made it should be handled by matplotlib anyway, so it doesn't matter if it was created by candlestick (I have used the first one, and ignored candlestick2, it works for me). Are you sure you are updating the plot with plt.draw() (or show) and wait for the plot to update? – Vincenzooo Sep 10 '17 at 03:41
xaxis_date() was new to me too, I actually found it by chance and was not sure how it worked, but apparently what it does is exactly what you need, telling to xaxis that the x data are a date format and to visualize them as such. – Vincenzooo Sep 10 '17 at 04:00
it makes me crazy:( – wang ning Sep 11 '17 at 00:52
Are the last version of the code and the plot in my answer what you are looking for? What is not working for you? – Vincenzooo Sep 11 '17 at 15:17
it does not work. the result does not parse the date correctly, I think it causes the bad quality image. and x-axis label formatter setting is used to format the date label already being exist correctly, so it is also not working. – wang ning Sep 11 '17 at 15:34
the image 2 is a good show in my question, but it does not contain datetime x-axis, so it is not useful too. – wang ning Sep 11 '17 at 15:37
and image2's X-axis does not contain datetime at all. its problem is not just to show datetime unsuitable. – wang ning Sep 11 '17 at 15:43
I don't understand. Look at the image at the end of my answer. Is it that what you want? If you copy and paste the "complete code" at the end of my answer, do you still get something like your image 2? – Vincenzooo Sep 11 '17 at 16:18
The code I have posted works for me in producing a plot like the one I posted. If that is not what you need, specify what it is. Or are you copying and pasting my code (that is basically yours cleaned up + 1 line) and getting a different results? In that case you might want to update the library. – Vincenzooo Sep 11 '17 at 16:24
the plot at the end is not a good show,except x-axis presentation,it looks like my image1, and image1 does not present correctly, so i quit to set format for the x-axis. i run a right candlestick image and upload at the end of my question, it has the right show but it does not contain datetime.you will find the different. – wang ning Sep 12 '17 at 01:02
and i does not have 10 reputation to put more than 2 links, so i delete the first two image i posted on. if you see the comments and understand,please tell me ,i will post the first two pictures on. if you think my question is not precise, please tell me. – wang ning Sep 12 '17 at 01:07
maybe you can find something useful here: https://stackoverflow.com/questions/36334665/how-to-plot-ohlc-candlestick-with-datetime-in-matplotlib note also "This module is deprecated in 2.0 and has been moved to a module called mpl_finance" – Vincenzooo Oct 03 '17 at 02:48

matplotlib how to parse right datetime factor of the candlestick image

1 Answers1