null712

bar.spread = ggplot(data = spread,aes(x = sym, y = avg.spread, colour = "seagreen3", fill = "seagreen3")) + 
              geom_bar(width = 0.5, stat = "identity") +
              xlab("") + ylab("Average Spread (%)") + 
              geom_hline(yintercept = min(spread$avg.spread), linetype = "dashed", colour = "red") +
              geom_hline(yintercept = max(spread$avg.spread), linetype = "dashed", colour = "red") +
              geom_hline(yintercept = 0) +
              theme(
               legend.position = "none",
               axis.title = element_text(size = 10),
               axis.text = element_text(size = 9))

null712

pushViewport(viewport(layout = grid.layout(2, 1, heights = unit(c(1,1), "null"))))
theme_set(theme_bw())
plot.both = ggplot(data = data) + geom_line(aes(x = date, y = buy),linetype = 1) +
                                 geom_line(aes(x = date, y = sell),linetype = 3) +
                                 xlab("") + ylab("Weighted Average Price") + 
                                 ylim(range(cbind(data$buy, data$sell))[1],range(cbind(data$buy, data$sell))[2]) +
                                 ggtitle(paste(c("ETF ID:", etf.id, " (",etf.sym,")"), collapse = "")) +
                                 theme(
                                 legend.position = "none",
                                 axis.title = element_text(size = 10),

null712

test_that("CalcAvg", {
	cust = c("28456", "19946", "56456", "12435", "76465")
	inst = rep("2345", 5)
	quant = c(12, 67, 60, 12, 56)
	price = c(794, 124, 69, 123, 678)
	fee = c(12, 0.02, 0.01, 3, 34)
	act = c("BUY", "SELL", "BUY", "SELL", "BUY")
	date = as.Date(c(rep("2014-04-02", 2), rep("2014-04-06", 2), "2012-06-03"))
	dftemp = data.frame(cust, inst, quant, price, fee, act, date)
	names(dftemp) = c("customer_id", "instrument_id", "quant", "price", "fees", "action", "date")

null712

dayi.trade = data[grep(data$date[i], data$date),]
dayi.buy = dayi.trade[grep(“BUY”, dayi.trade$action),]
dayi.sell = dayi.trade[grep(“SELL”, dayi.trade$action),]
trade.buy[i] = sum(dayi.buy$quant * dayi.buy$price)/sum(dayi.buy$quant)
trade.sell[i] = sum(dayi.sell$quant * dayi.sell$price)/sum(dayi.sell$quant)
trade.spread[i] = trade.buy[i] - trade.sell[i]

null712

inst_id = ‘8897’
data = ExecuteSqlQuery(paste(
        “SELECT * 
        FROM trades
        WHERE date_time >= ‘2014-01-01’
        AND instrument_id =”, inst_id,
        sep = ””)

null712

test_that("CalcAvg", {
	cust <- c("28456","19946","56456","12435","76465")
	inst <- rep("2345",5)
	quant <- c(12,67,60,12,56)
	price <- c(794,124,69,123,678)
	fee <- c(12,0.02,0.01,3,34)
	act <- c("BUY","SELL","BUY","SELL","BUY")
	date <- as.Date(c(rep("2014-04-02",2),rep("2014-04-06",2),"2012-06-03"))
	dftemp <- data.frame(cust,inst,quant,price,fee,act,date)
	names(dftemp) <- c("customer_id","instrument_id","quant","price","fees","action","date")

null712

dayi.trade <- data[grep(data$date[i],data$date),]
dayi.buy <- dayi.trade[grep(“BUY”,dayi.trade$action),]
dayi.sell <- dayi.trade[grep(“SELL”,dayi.trade$action),]
trade.buy[i] <- sum(dayi.buy$quant *
dayi.buy$price)/sum(dayi.buy$quant)
trade.sell[i] <- sum(dayi.sell$quant *
dayi.sell$price)/sum(dayi.sell$quant)
trade.spread[i] <- trade.buy[i]-trade.sell[i]

null712

inst_id <- '8897'
data <- ExecuteSqlQuery(paste(
"SELECT *
FROM trades
WHERE date_time >= '2014-01-01'
AND instrument_id =", inst_id,
sep="")