Code to get ASX constituents at a point in time

get_asx_100.R

library(dplyr, warn.conflicts = FALSE)
library(RPostgreSQL)

pg <- dbConnect(PostgreSQL())

g_names_ix <- tbl(pg, sql("SELECT * FROM comp.g_names_ix")) 
g_idxcst_his <- tbl(pg, sql("SELECT * FROM comp.g_idxcst_his"))

asx_100 <-
    g_names_ix %>% 
    filter(conm %~% 'ASX 100') %>%
    select(gvkeyx) %>%
    inner_join(g_idxcst_his) %>%
    compute()

get_asx_100 <- function(date) {
    asx_100 %>%
        filter(date >= from_ | is.na(from_), thru >= date | is.na(thru)) %>%
        collect()
}

get_asx_100("2011-12-31")
get_asx_100("2014-12-31")
get_asx_100("2016-12-31")