Download Funds

download_funds.Rmd

---
title: "Funds"
output:
  html_document:
    df_print: paged
---


```{r include=FALSE, results="hide"}
library(dplyr)
library(lubridate)
library(jsonlite)
library(writexl)
library(tidyr)
library(stringr)
library(kableExtra)

securities <- read.csv(text="Security,Currency
LU0119124278,EUR
LU0099574567,EUR
LU0197230542,EUR
LU0251127410,EUR
LU0061175625,EUR
LU0261952419,EUR
LU0702159772,EUR
LU0772969993,USD
LU0823431720,EUR
LU0260870158,EUR
LU0109394709,USD
LU0552385295,EUR
LU0868753731,EUR
LU1378878430,EUR
LU0073232471,EUR
LU2050612402,USD
LU0117843481,USD
LU0432979614,USD
LU0107398538,EUR
LU0210527015,USD
LU0225506756,USD
LU0051755006,USD
LU0208853514,EUR
LU0318933057,EUR
")

#URL Search: "https://www.morningstar.it/it/util/SecuritySearch.ashx?q=lu0702159772"

#URL Details: https://tools.morningstar.it/api/rest.svc/security_list/jbyiq3rhyf/?ModuleId=131&languageId=it-IT&viewId=Snapshot&ColumnList=BaseCurrencyId&outputtype=compactjson&v=1.1&secIdList=F00000OTNK&multipleuniverseids=FOITA$$ALL

url.searchid<-function(x){return(paste0("https://www.morningstar.it/it/util/SecuritySearch.ashx?q=",x))}
url.details<-function(x){return(paste0("https://tools.morningstar.it/api/rest.svc/security_list/jbyiq3rhyf/?ModuleId=131&languageId=it-IT&viewId=Snapshot&ColumnList=BaseCurrencyId&outputtype=compactjson&v=1.1&secIdList=",x,"&multipleuniverseids=FOITA$$ALL"))}
url.prices<-function(x,curr){ return(paste0("https://tools.morningstar.it/api/rest.svc/timeseries_price/jbyiq3rhyf?currencyId=",curr,"&idtype=Morningstar&frequency=daily&startDate=1970-01-01&priceType=&outputType=COMPACTJSON&id=",x,"]2]0]FOITA$$ALL&applyTrackRecordExtension=true"))}


df_securities_all <-data.frame()
df_raw_prices <-data.frame(timestamp=numeric(0))

securities %>%
  apply(1, function(x) {
    cat(x["Security"], "\n")
    raw <- read.csv(url.searchid(tolower(x["Security"])), stringsAsFactors = FALSE, sep = "|", header = FALSE, quote = "")

  df <- raw %>% rowwise() %>%
    mutate(Security = x["Security"]) %>%
    mutate(Name = V1) %>%
    mutate(ID = fromJSON(as.character(V2))$i) %>%
    mutate(Currency = str_sub(unlist(fromJSON(url.details(ID))$rows$cell),-3,-1)) %>%
    #mutate(Currency = if_else( grepl("EUR",Name), "EUR", "USD" ) ) %>%
    arrange(Currency) %>%
    select(Security, ID, Name, Currency)

  df_securities_all <<- df_securities_all %>% rbind(df)
})

df_securities <- securities %>% inner_join(df_securities_all,by=c("Security","Currency"))

df_raw_prices <-data.frame(Security=character(0),
                           ID=character(0),
                           Date=as.Date(character()),
                           timestamp=numeric(0),
                           Price=numeric(0),
                           Currency=character(0))


#Test: download.prices("LU0197230542","F0GBR055WP","EUR")
download.prices <- function(sec,id,curr) {
  prices <- fromJSON(url.prices(id,curr))
  colnames(prices) <- c("timestamp", "Price")
  prices <- prices %>% data.frame
  prices$Currency <- rep(curr,nrow(prices))
  prices$ID <- rep(id,nrow(prices))
  prices$Security <- rep(sec, nrow(prices))
  prices$Date = as_datetime(prices$timestamp/1000)
  return(prices)
}

df_securities %>% rowwise() %>%
  apply(1, function(x) {
      cat("Downloading: ",x["Security"],x["ID"], x["Currency"],"\n")
      prices <- download.prices(x["Security"],x["ID"], x["Currency"])
      df_raw_prices <<- df_raw_prices %>% rbind(prices)

      if(x["Currency"] != "EUR") {
        prices <- download.prices(x["Security"],x["ID"], "EUR")
        df_raw_prices <<- df_raw_prices %>% rbind(prices)
      }
})


df_prices<-df_raw_prices %>% select(-ID,-timestamp) %>% pivot_wider(names_from=Currency, values_from=Price)

df_prices_latest <- df_securities %>% left_join(df_prices %>%
                              group_by(Security) %>%
                              arrange(Date) %>%
                              top_n(1,Date)
                            , by=c("Security")) %>%
  mutate(Date = ymd(Date)) %>%
  mutate(Price = if_else(is.na(USD),EUR, USD)) %>%
  select(Security, Name, Currency, Price, Date)
```

# Funds

```{r}
df_prices_latest %>% kable()
```