Search code examples
pythonexcelmatplotlibalpha-vantagemplfinance

how do you set alpha vantage date as a column?

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)