This is my code for creating O’Shaughnessy’s Trending Value screen. Not my best code since it was origionally for my personal and private
consumption. It uses AAII Stock Investor Pro's exported data. I figured that it might not be bad to share.
# Trend Value
#https://www.valuesignals.com/Screens/Details/OShaughnessy_Trending_Value
#fix dplr undef13
if(!require("pacman")){
install.packages("pacman")
}
rm(list = ls(all = TRUE))
# library(dplyr)
# library(quantmod)
# library(xlsx)
pacman::p_load(bindrcpp,dplyr, quantmod, xlsx, rio)
#Momentum will be Regression Relative Strength instead. 126 == 6 month
# 21,42,63,126,189
trenddays <- 126
SIGMA <- 0
ifelse(dir.exists("C:/Users/msghe/OneDrive/Stocks/R"),
setwd("C:/Users/msghe/OneDrive/Stocks/R"), setwd("C:/Users/michael/SkyDrive/Stocks/R"))
stockdata <-
read.csv("data/WEEKLY.TXT",
header = FALSE,
stringsAsFactors = FALSE,
na.strings = '-99999999.990')
stocknames <-
read.csv(
"data/WEEKLY_KEY.TXT",
header = FALSE,
stringsAsFactors = FALSE,
na.strings = '-99999999.990'
)
stocknames[, 1]
names(stockdata) <- stocknames[, 1]
#clean ticker name for yahoo finance
stockdata$TICKER <- gsub('.', '-', stockdata$TICKER, fixed = TRUE)
# Remove bad tickers
#NA bad data
# stockdata[stockdata == -99999999.990] <- NA
stockdata <- stockdata[complete.cases(stockdata$TICKER),]
#Create all stock universe
# All stocks is 150 million in 1995 $$. It is adjusted to todays dollors. All
# stocks exclude over the counter.
# min.mark.cap <- quantile(stockdata$MKTCAP, na.rm = TRUE)[[2]]
#find inflation Market Cap 150 @ 1995
getSymbols("CPIAUCSL", src = "FRED")
deflator <-
last(Cl(to.yearly(CPIAUCSL)))[[1]] / Cl(to.yearly(CPIAUCSL))['1995'][[1]]
tvminmarketcap <- 150 * deflator
allstock <- stockdata[stockdata$MKTCAP > tvminmarketcap,]
# stockdata[stockdata$MKTCAP > quantile(stockdata$MKTCAP, na.rm = TRUE)[[4]],]
#Minimum market cap for later sanity
#I will not invest in over the counter
condition <- c("N - New York", "A - American", "M - Nasdaq")
allstock <- filter(allstock, EXCHG_DESC %in% condition)
# min.mark.cap <- median(allstock$MKTCAP, na.rm = TRUE)
# min.mark.cap <- quantile(allstock$MKTCAP, na.rm = TRUE)[[2]]
#add market cap requirement
# allstock <- filter(allstock, MKTCAP > min.mark.cap)
#Minimum Market Cap
min(allstock$MKTCAP)
#Start Ranking the stocks
#Calculate VC2
#subtract ntile from 101 to reverse (correct) Order. So Small is big
allstock$PBVPS.Rank <- 101 - ntile(allstock$PBVPS, 100)
allstock$PE.Rank <- 101 - ntile(allstock$PE, 100)
allstock$PSPS.Rank <- 101 - ntile(allstock$PSPS, 100)
allstock$EVEDA_12M.Rank <- 101 - ntile(allstock$EVEDA_12M, 100)
allstock$PCFPS.Rank <- 101 - ntile(allstock$PCFPS, 100)
allstock$SHY.Rank <- ntile(allstock$SHY, 100)
#Stocks with no rank get 50
allstock$PBVPS.Rank[is.na(allstock$PBVPS.Rank)] <- 50
allstock$PE.Rank[is.na(allstock$PE.Rank)] <- 50
allstock$PSPS.Rank[is.na(allstock$PSPS.Rank)] <- 50
allstock$EVEDA_12M.Rank[is.na(allstock$EVEDA_12M.Rank)] <- 50
allstock$PCFPS.Rank[is.na(allstock$PCFPS.Rank)] <- 50
allstock$SHY.Rank[is.na(allstock$SHY.Rank)] <- 50
#Sum the Ranks
allstock$SumRank <-
allstock$PBVPS.Rank + allstock$PE.Rank + allstock$PSPS.Rank + allstock$EVEDA_12M.Rank + allstock$PCFPS.Rank + allstock$SHY.Rank
# Calculate VC2
allstock$VC2 <- ntile(allstock$SumRank, 100)
#Get top 10% of allstock
tvstocks <- filter(allstock, VC2 > 70)
#I like Fscore >= 5, Z score >=3
# However, just use Fscore >= 7
#Clean data
tvstocks <- tvstocks[complete.cases(tvstocks$FSCORE_12M),]
# tvstocks <- tvstocks[complete.cases(tvstocks$ZSCORE_Q1),]
#Zscore Prime
# tvstocks <- tvstocks[complete.cases(tvstocks$UDEF13),]
#weed out must sells
# tvstocks <- filter(tvstocks, FSCORE_12M >= 4, ZSCORE_Q1 >= 1.8)
tvstocks <- filter(tvstocks, FSCORE_12M >= 7)
#My Market Cap Needs
# tvstocks <- filter(tvstocks, MKTCAP > min.mark.cap)
# tvstocks <- filter(, YIELD > 0)
#Final Cleaning
# tvstocks <- tvstocks[complete.cases(tvstocks$RPS_6M),]
# tvstocks <- tvstocks[complete.cases(tvstocks$RS_26W),]
# tvstocks <- filter(tvstocks,RS_26W > 0)
# tvstocks <- filter(tvstocks,RS_26W >= median(allstock$PRCHG_26W, na.rm = TRUE))
tvstocks <- filter(tvstocks,PRCHG_13W >= median(allstock$PRCHG_13W, na.rm = TRUE))
# head((screen[order(as.numeric(screen$SCORE), decreasing = TRUE),]),25) #[1]
# head((screen[order(as.numeric(screen$SCORE), decreasing = TRUE),]),25) #[1]
# names(stockdata) <- stocknames$V1
# screen.rpt <- left_join(screen, stockdata, by = "TICKER")
# screen.rpt <- arrange(screen.rpt, desc(as.numeric(SCORE)))
# screen.rpt$SCORE <- round(as.numeric(screen.rpt$SCORE), 4)
# # screen.rpt <- arrange(screen.rpt, desc(as.numeric(PRCHG_26W)))
# nrow(screen.rpt)
# stockenv <- new.env()
# getSymbols(tvstocks$TICKER, env = stockenv, adjust=TRUE)
# getSymbols(tvstocks$TICKER, env = stockenv)
# rm(screen)
# Keep it simple, stop using RRS
# tvstocks[,"krs26"] <- NA
# for (i in ls(stockenv)) {
# print(i)
# # tvstocks[tvstocks$TICKER == i,"krs26"] <- last(Ad(stockenv[[i]]),1)[[1]]/last(SMA(Ad(stockenv[[i]]),126),1)[[1]]
# tvstocks[tvstocks$TICKER == i,"krs26"] <- last(Ad(stockenv[[i]]),1)[[1]]/ mean(last(Ad(stockenv[[i]]),126))
# }
screen.rpt <- arrange(tvstocks,desc(as.numeric(PRCHG_26W)))
# screen.rpt <- arrange(tvstocks,desc(as.numeric(RPS_6M)))
# screen.rpt <- arrange(tvstocks,desc(as.numeric(krs26)))
screen.rpt <-
select(screen.rpt, TICKER, COMPANY, SMG_DESC, PRCHG_26W, MKTCAP,FSCORE_12M,VC2)
head(screen.rpt,25)
write.xlsx(
head(screen.rpt, 25),
"MoneyPicks.xlsx",
sheetName = "Money",
append = FALSE,
row.names = FALSE
)
rio::export(head(screen.rpt, 25),"MoneyPicks.csv")
