[FIXED] how do you set alpha vantage date as a column?

Issue

I am trying to plot data from Alpha Vantage.

data = pd.read_excel(file) 

when I do

print(data.columns)
    

I get:

    Index(['1. open', '2. high', '3. low', '4. close', '5.
    adjusted close', '6. volume', dtype='object')

as you can see, 'date' is not on there. this is causing me problems when I start using mplfinance and matplotlib. Can anyone help?

ps: my excel sheet looks like this

date	1. open ...
2021-02-03	243
2021-02-02	245

Solution

Glancing at what you have shown as your excel file, it is possible that the data is backwards for mplfinance.

After data = pd.read_excel(file) try this before calling mpf.plot():
data = data[::-1].

Then call mpf.plot(data)

Also, it looks like you have numbers in your column names:

Index(['1. open', '2. high', '3. low', '4. close', '5.
    adjusted close', '6. volume', dtype='object')

(note: '1. open' instead of 'open')

So try reassigning the column names:

Thus, this should work:

data = pd.read_excel(file)
data = data[::-1]
data.columns = ['open', 'high', 'low', 'close', 'adjusted close', 'volume']
mpf.plot(data)

Answered By - Daniel Goldfarb