pair look-through matrix has more pairs than pair P&L matrix about pairs HOT 9 OPEN

> dim(pair_pnl_matrix)
[1] 731 101
> 

duke_pair_look_vs_outright<-readLines(
  if(off_site){
    "https://raw.githubusercontent.com/satrapade/pairs/master/data/duke_pair_look_vs_outright.csv"
  } else {
    "N:/Depts/Share/UK Alpha Team/Analytics/duke_summary/duke_pair_look_vs_outright.csv"
  }
) %>% paste0(collapse="\n") %>% fread

look_through_pair_exposure<-duke_pair_look_vs_outright[
  TRUE,
  .(
    Exposure=sum(Outright+LookThrough)
  ),
  keyby=c("Pair","SuperSectorIndex")
]

pair_lt_matrix<-NNcast(
  look_through_pair_exposure,
  i_name="Pair",
  j_name="gsub(' Index','',SuperSectorIndex)%>%{ifelse(.=='','Unknown',.)}",
  v_name="Exposure"
)%>%{.[,colnames(.)!="Unknown"]}

> dim(pair_lt_matrix)
[1] 103  19

> setdiff(rownames(pair_lt_matrix),colnames(pair_pnl_matrix))
[1] "MC140" "MC154"

> mapply(fetch_index_weights,duke_index_exposure$Ticker)
Error: <SQL> 'SELECT MAX(HistoricalDate) FROM tHistoricalProductHolding WHERE ProductId=NA'
  nanodbc/nanodbc.cpp:1587: 42S22: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NA'. 
 Show Traceback
 
 Rerun with Debug
 Error: <SQL> 'SELECT MAX(HistoricalDate) FROM tHistoricalProductHolding WHERE ProductId=NA'
  nanodbc/nanodbc.cpp:1587: 42S22: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NA'. 
> 

> fetch_index_weights
function(
  equity_index_ticker="UKX Index",
  db=get("db",parent.frame())
){
  
  product_types<-query("SELECT * FROM tProductType",db=db)[,.SD,keyby=Name]
  
  equity_index_products<-query(make_query(
    product_type=product_types["Equity Index",ProductTypeId],
    query_string = "SELECT * FROM tProduct WHERE ProductTypeId=--R{product_type}--"
  ),db=db)[,.SD,keyby=PrimaryDataSourceProductCode]
  
  equity_index_update<-query(make_query(
    product_id=equity_index_products[equity_index_ticker,ProductId],
    query_string = "SELECT MAX(HistoricalDate) FROM tHistoricalProductHolding WHERE ProductId=--R{product_id}--"
  ),db=db)[[1]]
  
  equity_index_weights<-query(make_query(
    product_id=equity_index_products[equity_index_ticker,ProductId],
    update_date=equity_index_update,
    query_string = "
      SELECT 
        tProduct.PrimaryDataSourceProductCode AS IndexTicker,
        tSecurity.Ticker AS Ticker,
        tSecurity.UniqueId AS UniqueId,
        tHistoricalProductHolding.SecurityUnits AS Weight
      FROM tHistoricalProductHolding 
      LEFT JOIN tProduct ON tProduct.ProductId=tHistoricalProductHolding.ProductId
      LEFT JOIN tSecurity ON tSecurity.SecurityId=tHistoricalProductHolding.SecurityId
      WHERE tHistoricalProductHolding.ProductId=--R{product_id}-- 
      AND tHistoricalProductHolding.HistoricalDate='--R{update_date}--'
    "
  ),db=db)
  
  equity_index_weights
  
}
<bytecode: 0x000000002ee42678>

> mapply(
+   function(ndx){
+     cat(ndx,"\n")
+     fetch_index_weights(ndx)
+   },
+   duke_index_exposure$Ticker
+ )
CAC Index 
DAX Index 
F3BANK Index 
F3FINS Index 
Error: <SQL> 'SELECT MAX(HistoricalDate) FROM tHistoricalProductHolding WHERE ProductId=NA'
  nanodbc/nanodbc.cpp:1587: 42S22: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NA'. 
 Show Traceback
 
 Rerun with Debug
 Error: <SQL> 'SELECT MAX(HistoricalDate) FROM tHistoricalProductHolding WHERE ProductId=NA'
  nanodbc/nanodbc.cpp:1587: 42S22: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NA'. 
>