key ratios no longer works

[Sbirch556]

library(tidyquant)

aapl_key_ratios <- tq_get("AAPL", get = "key.ratios")

Warning messages: 1: x = 'AAPL', get = 'key.ratios': Error in UseMethod("as_list"): no applicable method for 'as_list' applied to an object of class "NULL"

2: x = 'AAPL', get = 'key.ratios': Error in eval(lhs, parent, parent): object 'key_ratios_bind' not found

appears these URLs

http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=AAPL&reportType=bs&period=3&dataType=A&order=asc&columnYear=10&number=3

or

http://financials.morningstar.com/finan/ajax/exportKR2CSV.html?&callback=?&t=:AAPL&region=usa&culture=en-US&cur=&order=asc

no longer work to scrape the data files. However the option to download key ratios/statements still exists, i assume the URL has been changed.

[Sbirch556]

New JS function appears to be

SRT_stocFund.Export = function () { //return false; var params = this.GetPara(); document.location = hostPath+"/ajax/ReportProcess4CSV.html?" + params+"&denominatorView="+denominatorView+"&number="+number; };

[Sbirch556]

This is the new link, but no results when not pressed from Morningstar website.

http://financials.morningstar.com/ajax/ReportProcess4CSV.html?&t=XNAS:AAPL&region=usa&culture=en-US&cur=&reportType=bs&period=3&dataType=A&order=asc&columnYear=5&curYearPart=1st5year&rounding=3&view=raw&r=232981&denominatorView=raw&number=3

[mdancho84]

Thanks, I'm looking into.

[mdancho84]

There has been a change in how Morningstar's website exports CSVs, which has modified the link. This will likely take some time and digging to fix.

[Sbirch556]

I have been very actively using their fundamental data. Quantconnect appears to still have access to Morningstar fundamentals from https://www.quantconnect.com/docs/research/fundamental-data though written in python/C#. I plan on looking into this and see what they are doing differently.

[mdancho84]

Let me know what you can find out.

The download on the Excel File seems to have changed.

For JPM I can access the Key Ratios website: https://financials.morningstar.com/ratios/r.html?t=0P0000031C&culture=en&platform=sal

But when I go to download, it triggers

<a class="large_button" href="javascript:exportKeyStat2CSV();"><span class="csv"></span><div>Export</div></a>

And the exportKeyStat2CSV() method produces:

function exportKeyStat2CSV(){
            var orderby = SRT_keyStuts.getOrderFromCookie("order");
            var urlstr = "//financials.morningstar.com/finan/ajax/exportKR2CSV.html?&callback=?&t=0P0000031C&region=usa&culture=en-US&version=SAL&cur=&order="+orderby;
            document.location = urlstr;
        }

But for some reason this link will not download the CSV:

https://financials.morningstar.com/finan/ajax/exportKR2CSV.html?&callback=?&t=0P0000031C&region=usa&culture=en-US&version=SAL&cur=&order=asc

[mdancho84]

I've finished investigating. Morningstar has changed to a Javascript Key Ratios websites, which now requires users to load the page to render. This is different than in the past.

There is a way to get render the site, pull the information either via webscrape or download the CSV, but it requires something like RSelenium to load the page. It's a bigger project, which will take some time to figure out.

My time commitment at the moment is on Business Science University, so any help on this will be much appreciated.

[r00tguard]

I've a python script for downloading CSV from MS key ratios page. I am sending here in hopes of it can be of help

For selenium to work with Firefox you first need to download and extract the geckodriver to some directory existing in your system PATH enviroment variable. https://github.com/mozilla/geckodriver/releases

from selenium import webdriver
downloadFolder = 'C:\\Temp'
# Profile to ensure that the file is downloaded to location without user prompt
profile = webdriver.FirefoxProfile()
profile.set_preference('browser.download.folderList', 2) # custom location
profile.set_preference('browser.download.manager.showWhenStarting', False)
profile.set_preference('browser.download.dir', downloadFolder)
profile.set_preference('browser.helperApps.neverAsk.saveToDisk', 'text/html')
profile.set_preference("browser.helperApps.alwaysAsk.force", False)
profile.set_preference("browser.download.manager.useWindow", False)
profile.set_preference("browser.download.manager.focusWhenStarting", False)
profile.set_preference("browser.helperApps.neverAsk.openFile", True)
# options to have firefox work in the background without creating a window
opts = webdriver.FirefoxOptions()
opts.headless = True
browser = webdriver.Firefox(options=opts,firefox_profile=profile)
stockCode = "AAPL"
url = "http://financials.morningstar.com/ratios/r.html?t=" + stockCode
browser.get(url)
# Find the export button and click on it
try:
    browser.find_element_by_class_name('large_button').click()
except Exception:
    print("Can't find page for " +  stockCode)

# Some other examples for downloading from other pages of MS
url="http://financials.morningstar.com/income-statement/is.html?t=" + stockCode
browser.get(url)
browser.find_element_by_class_name('rf_export').click()
url="http://performance.morningstar.com/stock/performance-return.action?p=price_history_page&t=" + stockCode
browser.get(url)
browser.find_element_by_link_text("10Y").click()
browser.find_element_by_link_text("Yearly").click()
browser.find_element_by_class_name('csv_export').click()

[luceydav]

I noted that those M'Star links are denied on robots.txt. I would be willing to help look into a possibly more permanent solution with Edgar data via finreportr and some functions to calculate ratios. I haven't contributed before and would likely need coaching, but have 2.5 years of daily rstats experience.

[ksun91]

Is it possible to use this data_source as a possible replacement?

http://www.stockpup.com/data/

[mdancho84]

That looks good! Let me see what I can do.

[chalg]

Ideally we need a source that covers most stock markets, not just the US.

[Sbirch556]

Something to start for anyone who doesn't need their data in Tidy format....if you follow Matt's code for tq_get should be easy to turn this into Tidy

 library(httr)
 library(xml2)
 library(purrr)

 base <- "http://www.stockpup.com/data/"
 end <- "_quarterly_financial_data.csv"

 stock <- "AAPL"

 url <- paste0(base,stock,end)

 for (i in 1:length(url)) {

   text <- httr::RETRY("GET", url[i], times = 5) %>%
   httr::content(type = "text/html", encoding = "UTF-8", stringAsFactors=FALSE)

 }

character_chunk <- text %>%
  xml2::as_list() %>%
  unlist() %>%
  strsplit(split = "\n") %>%
  purrr::flatten_chr()

flat_file <- utils::read.csv(text = character_chunk)

[JrRobls]

Might want to check out this website http://finra-markets.morningstar.com/MarketData/EquityOptions/detail.jsp?query=126:0P000000GY Looks like that have 10 years of Morningstar data, though you have to take it in 5 year chunks. Just starting to learn programming. Might be able to import into Excel.

[dazz2017]

I've been looking into this too and have found out a couple of things that might be useful to someone but also have a question.

Firstly, navigating to the URL below goes directly to the Mstar key ratios page (I'm interested in AU stocks) http://financials.morningstar.com/ratios/r.html?t=BHP&region=aus&culture=en-US

On this page (as was mentioned earlier) when the Export button is pushed, some javascript is run "javascript:exportKeyStat2CSV()".

I execute this within VBA using IE.Navigate "javascript:exportKeyStat2CSV()" which results in a dialog asking me where I want to save this file.

So this is all working so far as I want but I'm now at the point where I don't know how to control this save process within the browser using VBA. Can anyone point me in the right direction for how to do this please?

[dazz2017]

Answering my own question I've found a link that has answer, UIAutomationClient.

https://www.mrexcel.com/board/threads/using-uiautomationclient-to-automate-the-save-as-file-download-in-ie11.1086615/

I wonder if there is a less code intensive way though....

[Sbirch556]

I think Financial Modeling Prep is currently the way to go with this. As noted here the data will be consistently updated and is coming from SEC.gov. I will provide code I have been using for FMP.

That being said good FREE fundamental data API's tend to be short lived...intrinio, google finance, yahoo finance...so I am working on my own open source solution to this problem.

[mdancho84]

@Sbirch556 Let me know how you make out with your project. There's a big gap right now for free fundamentals data.

[Sbirch556]

My use case for this information was for a shiny app...so this can be made better, I found this worked well for what I needed in my app though. Adjust the endpt with whatever endpoint from FMP you want to use, same goes for stmt for the statement, then specify whether you want quarterly or annual information....then stock. flip_FMP provides the information in a standard table format, hc_FMP is in a tidy format, I specifically used hc_FMP to create Highchart visuals.

library(jsonlite)
library(dplyr)
library(tidyr)
library(highcharter)

flip_FMP <- function(endpt,stmt,qrt,stck){

  if(endpt=="financials"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stmt,"/",stck))
      obj_nm <- obj[[endpt]][["date"]]
      obj <- t(as.data.frame(obj[[endpt]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stmt,"/",stck,"?period=quarter"))
      obj_nm <- obj[[endpt]][["date"]]
      obj <- t(as.data.frame(obj[[endpt]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else {

      print("something is wrong with the financials endpoint")

    }

  }else if(endpt=="company-key-metrics"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
      obj_nm <- obj[["metrics"]][["date"]]
      obj <- t(as.data.frame(obj[["metrics"]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck,"?period=quarter"))
      obj_nm <- obj[["metrics"]][["date"]]
      obj <- t(as.data.frame(obj[["metrics"]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else {

      print("something is wrong with the company key metrics endpoint")

    }

  }else if(endpt=="financial-ratios"){

    obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
    obj_nm <- obj[["ratios"]][["date"]]
    obj <- t(as.data.frame(obj[["ratios"]]))
    metrics <- row.names(obj)
    attr(obj,"dimnames") <- NULL
    obj <- as.data.frame(obj)
    names(obj) <- obj_nm
    obj <- obj[-1,]
    obj$metric <- metrics[-1]
    obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

    return(obj)

  }else if(endpt=="financial-statement-growth"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
      obj_nm <- obj[["growth"]][["date"]]
      obj <- t(as.data.frame(obj[["growth"]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck,"?period=quarter"))
      obj_nm <- obj[["growth"]][["date"]]
      obj <- t(as.data.frame(obj[["growth"]]))
      metrics <- row.names(obj)
      attr(obj,"dimnames") <- NULL
      obj <- as.data.frame(obj)
      names(obj) <- obj_nm
      obj <- obj[-1,]
      obj$metric <- metrics[-1]
      obj <- obj[,c(ncol(obj),1:ncol(obj)-1)]

      return(obj)

    }else {

      print("something went wrong with the growth endpoint")

    }
  }
}

hc_FMP <- function(endpt,stmt,qrt,stck){

  if(endpt=="financials"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stmt,"/",stck))
      obj_date <- obj[[endpt]][["date"]]
      obj <- lapply(obj[[endpt]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stmt,"/",stck,"?period=quarter"))
      obj_date <- obj[[endpt]][["date"]]
      obj <- lapply(obj[[endpt]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else {

      print("something is wrong with the financials endpoint")

    }

  }else if(endpt=="company-key-metrics"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
      obj_date <- obj[["metrics"]][["date"]]
      obj <- lapply(obj[["metrics"]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck,"?period=quarter"))
      obj_date <- obj[["metrics"]][["date"]]
      obj <- lapply(obj[["metrics"]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else {

      print("something is wrong with the company key metrics endpoint")

    }

  }else if(endpt=="financial-ratios"){

    obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
    obj_date <- obj[["ratios"]][["date"]]
    obj <- lapply(obj[["ratios"]],as.numeric)
    obj <- as.data.frame(obj)
    obj$date <- obj_date
    obj[is.na(obj)] <- 0

    obj <- obj %>%
      pivot_longer(cols = c(-date),
                   names_to = "metric",
                   values_to = "value",
                   values_drop_na = TRUE)

    return(obj)

  }else if(endpt=="financial-statement-growth"){

    if(qrt=="Annual"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck))
      obj_date <- obj[["growth"]][["date"]]
      obj <- lapply(obj[["growth"]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else if(qrt=="Quarterly"){

      obj <- fromJSON(paste0("https://financialmodelingprep.com/api/v3/",endpt,"/",stck,"?period=quarter"))
      obj_date <- obj[["growth"]][["date"]]
      obj <- lapply(obj[["growth"]],as.numeric)
      obj <- as.data.frame(obj)
      obj$date <- obj_date
      obj[is.na(obj)] <- 0

      obj <- obj %>%
        pivot_longer(cols = c(-date),
                     names_to = "metric",
                     values_to = "value",
                     values_drop_na = TRUE)

      return(obj)

    }else {

      print("something went wrong with the growth endpoint")

    }
  }
}

income <- flip_FMP(endpt = "financials",stmt = "income-statement",qrt = "Quarterly",stck = "AAPL")
income_tidy <- hc_FMP(endpt = "financials",stmt = "income-statement",qrt = "Quarterly",stck = "AAPL")
hchart(income_tidy,type = "column",hcaes(y=value,group=metric,x=date))

[Sbirch556]

@mdancho84 will do Matt I believe what I'm working on will bridge the gap.

[Sbirch556]

Though this data is not live just yet, I'm planning on it being live in the coming weeks. Documentation can be found here, I'm able to classify the majority of data tables found within 10-Q and 10-K's. I'm starting with the most common statements and trying to classify everything else moving forward. I've found there is a lot of really cool things I've been able to do with this data, looking forward to sharing it with others.

[luceydav]

This new FinancialModelingPrep api seems like it might be part of the solution (https://jpiburn.github.io/fmpapi/), supposed to be on CRAN imminently

[mdancho84]

@luceydav this sounds great. Thanks for the heads up. Let me know when it arrives on CRAN, and I'll take a look. Should be able to incorporate into tidyquant fairly easily.