6

I have an excel file with dates and stock prices. I read this data into a dataframe with DataFrames.jl

using DataFrames, StatsPlots, Indicators

df = DataFrame(XLSX.readtable("Demo-sv.xlsx", "Blad3")...)

This works great and here I print the first 6 entries.

6×2 DataFrame
│ Row │ Date       │ Closeprice │
│     │ Any        │ Any        │
├─────┼────────────┼────────────┤
│ 1   │ 2019-05-03 │ 169.96     │
│ 2   │ 2019-05-02 │ 168.06     │
│ 3   │ 2019-04-30 │ 165.58     │
│ 4   │ 2019-04-29 │ 166.4      │
│ 5   │ 2019-04-26 │ 167.76     │
│ 6   │ 2019-04-25 │ 167.46     │

I then plot this data with StatsPlots.jl @df df plot(df.Date, df.Closeprice) and get a nice plot graph.

The problem is when I want to plot a simple moving average with Indicators.jl

movingaverage = sma(df, n=200)
plot!(movingaverage, linewidth=2, color=:red)

I get this error message

ERROR: LoadError: MethodError: no method matching sma(::DataFrame; n=200)
Closest candidates are:
sma(::Array{T,N} where N; n) where T<:Real at 
/Users/HBrovell/.julia/packages/Indicators/QGmEX/src/ma.jl:8
sma(::Temporal.TS{V,T}; args...) where {V, T} at 
/Users/HBrovell/.julia/packages/Indicators/QGmEX/src/temporal.jl:64

What I understand, I need to convert the DataFrame so I will be able to use the Indicators.jl sma function. I have tried with convert(Array{Float64}, df[2]) to only convert the Closeprice column, but that didn't work the way I wanted. I guess I don't want to convert the date column?

So how can I convert the DataFrame, so I can use the sma function in Indicators.jl, or is there a better way than using DataFrames.jl?

hbrovell
  • 547
  • 6
  • 17

1 Answers1

5

I assume what you need is:

sma(sort(df, :Date).ClosePrice, n=200)

One additional problem you have is the data type of your ClosePrice column that should be numeric rather than Any

You need to convert it somehow, for an example:

df[!, :ClosePrice] .= Float64.(df.ClosePrice)
Przemyslaw Szufel
  • 40,002
  • 3
  • 32
  • 62
  • I seem to get the same error message if I do this, which I assume you mean. ```df = DataFrame(XLSX.readtable("Demo-sv.xlsx", "Blad3")...)``` ```@df df plot(df.Date, df.Closeprice)``` ```movingaverage = sma(sort(df, :Date).Closeprice, n=200)``` ```plot!(movingaverage, linewidth=2, color=:red)``` – hbrovell Dec 21 '20 at 13:59
  • Or correction I now get the following error message, where the "no method matching" is now Array instead of DataFrame. ERROR: LoadError: MethodError: no method matching sma(::Array{Any,1}; n=200) Closest candidates are: sma(::Array{T,N} where N; n) where T<:Real at /Users/HBrovell/.julia/packages/Indicators/QGmEX/src/ma.jl:8 sma(::Temporal.TS{V,T}; args...) where {V, T} at /Users/HBrovell/.julia/packages/Indicators/QGmEX/src/temporal.jl:64 – hbrovell Dec 21 '20 at 14:09
  • yeah you have one additional issue in the data - I edited the answer. – Przemyslaw Szufel Dec 21 '20 at 14:26
  • That works, now I don't get any error messages. But my dates get very wrong in the plot graph. I get dates like -0059-01-01 and 0476-01-01. Do I also need to convert that from any, to a date format? – hbrovell Dec 21 '20 at 14:35
  • This depends on how you are parsing and formatting the dates. If you have problem with that I recommend asking a separate question. – Przemyslaw Szufel Dec 21 '20 at 15:39