Morning Star Integration

Question

I've been trying to make a comprehensive Google Sheet containing information about different stocks that can have stocks dynamically added and removed for a school project. To that end, I've done some research into loading data from tables with IMPORTHTML, and come up with this:

=IMPORTHTML(CONCAT("http://financials.morningstar.com/ratios/r.html?t=", B3),"table", 1)

Where B3 is NFLX or any other stock ticker that would be added. However, Sheets is returning with `Imported content is empty", and I've got no clue why.

The reason your importhtml is not working is because morningstar fills that information using AJAX ... — Ahmed Masud, Mar 02 '17 at 18:38
@AhmedMasud Ahh... Any way that I could interface with AJAX? — Derpleferple, Mar 02 '17 at 18:39
@AhmedMasud Do you have a way to get that file for the Ownership category as well? — Derpleferple, Mar 02 '17 at 21:39

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Partial Answer:

yes, after a bit of fiddling I found that the best url to look at for Financial Parts is

http://financials.morningstar.com/finan/financials/getFinancePart.html?t=XNAS:NFLX&region=usa&culture=en-US&ops=clear

You can use this and combine it with

https://github.com/fastfedora/google-docs/blob/master/scripts/ImportJSON/Code.gs

which adds ImportJSON functionality to google sheets.

This should get you started. However the data comes as bunch of divs/tds that you would have to clean it up but it's possible after a bit of fiddling :)

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 02 '17 at 18:53

Ahmed Masud

21,655
3
33
58

May I ask where you got that other URL? I've been looking for a similar one for Key Ratios – Derpleferple Mar 02 '17 at 19:54
Also one for Ownership – Derpleferple Mar 02 '17 at 20:23

Ed Nelson · Answer 2 · 2017-03-03T01:56:02.183

I can't tell you where Ahmed Masud got the link (and I would like to know too). I get a lot of data from morningstar and they can be strange. However, using his link, you can get the Key Ratios and clean the resulting data with importhtml and also reference your B3 cell with this:

   =arrayformula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IMPORTHTML("http://financials.morningstar.com/finan/financials/getFinancePart.html?t=XNAS:"& B3 &"&region=usa&culture=en-US&ops=clear","table", 1), "<\/td>" , "" ),"<\/tr>",""),"<\/th>",""),"<\/thead>",""),"<\/span>",""))

Sorry, I put in the wrong formula. This will get the B3 entry.

This will get ownership:

=arrayformula(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IMPORTHTML("http://investors.morningstar.com/ownership/shareholders-overview.html?t=XNAS:"& B3 &"&region=usa&culture=en-US&ops=clear","table", 4), "<\/td>" , "" ),"<\/tr>",""),"<\/th>",""),"<\/thead>",""),"<\/span>",""))

Again the stock symbol should be in B3.

Yeah, that works really well. How do you get your data? Or is it only the Key Ratios stuff? — Derpleferple, Mar 02 '17 at 22:58

Morning Star Integration

2 Answers2

Partial Answer: