1 files changed, 44 insertions, 0 deletions
diff --git a/R/latestprices.R b/R/latestprices.R
new file mode 100644
index 00000000..6394e695
--- /dev/null
+++ b/R/latestprices.R
@@ -0,0 +1,44 @@
+library(RODBC)
+conn <- odbcConnect("MLP-PROD")
+deltas.hist <- sqlQuery(conn,"select * from ET_CusipDeltasHist where IndexType='LCDX' and ReinvSpreadScenario='MID'")
+deltas.live <- sqlQuery(conn,"select * from ET_CusipDeltas where IndexType='LCDX' and ReinvSpreadScenario='MID'")
+deltas <- rbind(deltas.live,deltas.hist)
+deltas$PriceDate <- as.Date(deltas$PriceDate,format="%m/%d/%Y")
+deltas <- deltas[order(deltas$PriceDate,decreasing=T),]
+cusiplist <- unique(deltas$Cusip)
+deltas <- deltas[match(cusiplist,deltas$Cusip),]
+
+walduration <- sqlQuery(conn,"select * from ET_CusipSumProduct_WALDuration")
+prices_lcdx <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='LCDX'")
+prices_t1 <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='T1'")
+prices_t2 <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='T2'")
+prices_hy <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='HY' and IndexSeries='10' and IndexTenor='7y'")
+odbcClose(conn)
+selectlatest <- function(data){
+  data$PriceDate <- as.Date(data$PriceDate,format="%m/%d/%Y")
+  data <- data[order(data$PriceDate,data$UpdateDate,decreasing=T),]
+  data_mid <- data[data$ReinvSpreadScenario=="MID",]
+  data_mid <- data_mid[match(unique(data_mid$Cusip),data_mid$Cusip,),]
+  data_high <- data[data$ReinvSpreadScenario=="HIGH",]
+  data_high <- data_high[match(unique(data_high$Cusip),data_high$Cusip,),]
+  data_low <- data[data$ReinvSpreadScenario=="LOW",]
+  data_low <- data_low[match(unique(data_low$Cusip),data_low$Cusip,),]
+  data_noreinv <- data[data$ReinvSpreadScenario=="NOREINV",]
+  data_noreinv <- data_noreinv[match(unique(data_noreinv$Cusip),data_noreinv$Cusip,),]
+  data <- rbind(data_mid,data_noreinv,data_low,data_high)
+  data[order(data$Cusip),]
+}
+
+prices_t1 <- selectlatest(prices_t1)
+prices_t2 <- selectlatest(prices_t2)
+prices_hy <- selectlatest(prices_hy)
+prices_lcdx <- selectlatest(prices_lcdx)
+walduration <- selectlatest(walduration)
+prices_lcdx <- prices_lcdx[prices_lcdx$Cusip%in%prices_t1$Cusip,]
+prices_hy <- prices_hy[prices_hy$Cusip%in%prices_t1$Cusip,]
+deltas <- deltas[deltas$Cusip%in%prices_t1$Cusip,]
+walduration <- walduration[walduration$Cusip%in%prices_t1$Cusip,]
+deltas <- deltas[pmatch(prices_t1$Cusip,deltas$Cusip,dup=T),]
+data <- cbind(prices_lcdx[,c("Cusip","ReinvSpreadScenario","AWJuniorWgts_Price","AWSeniorWgts_Price")],prices_t1[,"AWJuniorWgts_Price"],prices_t2[,"AWJuniorWgts_Price"],prices_hy[,c("AWJuniorWgts_Price","AWSeniorWgts_Price")],walduration[,c("AW_WAL","AW_Duration","PriceDate")],deltas[,c("FullTranche","Tranche1","Tranche2","Tranche3","Tranche4")])
+colnames(data)<-c("Cusip","ReinvScen","LCDX12 5y Jr","LCDX12 5y Sr","T1","T2","HY10 7y Jr","HY10 7y Sr","Wal","Duration","Price Date","LCDX index","LCDX 0-8","LCDX 8-15","LCDX 15-30","LCDX 30-100")
+write.table(data,file="W:/CorpCDOs/latestprices_Prod.txt",row.names=F,col.names=T,sep=",")