0

Afternoon! I'm just starting out with R and learning about data frames, packages, etc... read a lot of the messages here but couldn't find an answer.

I have a table I'm accessing with R that has the following fields: [Symbol],[Date],[Open],[High],[Low],[Close],[Volume]

And, I'm calculating SMAs on the close prices:

sqlQuery <- "Select * from [dbo].[Stock_Data]"
conn <- odbcDriverConnect(connectionString)
dfSMA <- sqlQuery(conn, sqlQuery)
sma20 <- SMA(dfSMA$Close, n = 20)
dfSMA["SMA20"] <- sma20

When I look at the output, it appears to be calculating the SMA without any regard for what the symbol is. I haven't tried to replicate the calculation, but I would suspect it's just doing it by 20 moving rows, regardless of date/symbol.

How do I restrict the calculation to a given symbol? Any help is appreciated - just need to be pointed in the right direction.

Thanks

Xiong Chiamiov
  • 13,076
  • 9
  • 63
  • 101
Adam Parker
  • 7
  • 4

1 Answers1

0

You're far more likely to get answers if you provide reproducible examples. First, let's replicate your data:

library(quantmod)
symbols <- c("GS", "MS")
getSymbols(symbols)
# Create example data:
dGS <- data.frame("Symbol" = "GS", "Date" = index(GS), coredata(OHLCV(GS)))
names(dGS) <- str_replace(names(dGS), "GS\\.", "")
dMS <- data.frame("Symbol" = "MS", "Date" = index(MS), coredata(OHLCV(MS)))
names(dMS) <- str_replace(names(dMS), "MS\\.", "")
dfSMA <- rbind(dGS, dMS)

> head(dfSMA)
  Symbol       Date   Open   High    Low  Close  Volume Adjusted
1     GS 2007-01-03 200.60 203.32 197.82 200.72 6494900 178.6391
2     GS 2007-01-04 200.22 200.67 198.07 198.85 6460200 176.9748
3     GS 2007-01-05 198.43 200.00 197.90 199.05 5892900 177.1528
4     GS 2007-01-08 199.05 203.95 198.10 203.73 7851000 181.3180
5     GS 2007-01-09 203.54 204.90 202.00 204.08 7147100 181.6295
6     GS 2007-01-10 203.40 208.44 201.50 208.11 8025700 185.2161

What you want to do is subset your long data object, and then apply technical indicators on each symbol in isolation. Here is one approach to guide you toward acheiving your desired result.

You could do this using a list, and build the indicators on xts data objects for each symbol, not on a data.frame like you do in your example (You can apply the TTR functions to columns in a data.frame but it is ugly -- work with xts objects is much more ideal). This is template for how you could do it. The final output l.data should be intuitive to work with. Keep each symbol in a separate "Container" (element of the list) rather than combining all the symbols in one data.frame which isn't easy to work with.

make_xts_from_long_df <- function(x) {

    # Subset the symbol you desire
    res <- dfSMA[dfSMA$Symbol == x, ]
    #Create xts, then allow easy merge of technical indicators
    x_res <- xts(OHLCV(res),  order.by = res$Date)
    merge(x_res, SMA(Cl(x_res), n = 20))
}

l.data <- setNames(lapply(symbols, make_xts_from_long_df), symbols)
FXQuantTrader
  • 6,821
  • 3
  • 36
  • 67