GoogleFinance functions...

[SoReady]

For quite sometime I've been able to use an IMPORTXML function within my google sheet. The exact function is...

=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=",":","VBIAX"), "//table/tbody[1]/tr[1]/td[5]")/100

...where it supplied the YTD returns for a particular MF, VBIAX in this case.

But about a week or so ago I get "Error Resource at url not found." Can I assume that morningstar has changed something to break this?

This is not a piece of code I can write. I got this from somewhere else. The goal is to get YTD performance for MF's. Does anyone know of an alternative?

Thanks,

Bob

 

[tulak]

They probably changed the website and you’ll
have to tweak the import function. That’s a problem with scrapers, even though I’ve been using morningstar to scrape prices and I think it still works. I’ll have to check later when I’m at the computer.

But if you’re only looking for ytd, price info, you could use the GoogleFinance function.

https://support.google.com/docs/answer/3093281?hl=en

The only downside is you have to wait until late in the evening before the eod prices are updated.

 

[SoReady]

Thanks. I would use the googlefinance function if it worked. The numbers it returns are not close to what morningstar, Vanguard or Fidelity has for YTD info.

For instance, Vanguard Tot Stk Mkt (VTSAX) google returns 7.3, where the other sites show 22.17.

 

[cathy63]

I can't retrieve anything from that Morningstar page to Google sheets. It works fine from Excel though, so I suspect that Morningstar is rejecting requests from the Google server that operates the ImportXml function.

I agree that GoogleFinance returns for mutual funds are very strange and look nothing like the data on the Morningstar page, even though their doc says they get the info from Morningstar.

 

[SoReady]

Thanks for checking.

 

[marko]

I've had the same problem over the past week or so. I does give the accurate daily price but not the YTD. Drivin' me crazy.

 

[SoReady]

Me too, but I don't know enough about importXML to be able to try and adjust the parameters to get it to work, if that is even possible.

 

[tulak]

It looks like it could be related to Morningstar. I tried using ImportXML with Fidelity and it's pulling in data, but nothing from Morningstar. I was originally thinking that maybe Google broke something.

Is Morningstar the only site that has accurate data?

 

[target2019]

For quite sometime I've been able to use an IMPORTXML function within my google sheet. The exact function is...

=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=",":","VBIAX"), "//table/tbody[1]/tr[1]/td[5]")/100

...where it supplied the YTD returns for a particular MF, VBIAX in this case.

But about a week or so ago I get "Error Resource at url not found." Can I assume that morningstar has changed something to break this?

This is not a piece of code I can write. I got this from somewhere else. The goal is to get YTD performance for MF's. Does anyone know of an alternative?

Thanks,

Bob

You can build a portfolio in M*. Then look at a custom view which includes YTD. Export to XLS.

 

[tulak]

I was curious to see how this works and came up with a solution for yahoo (with some googling help):

=IMPORTXML("https://finance.yahoo.com/quote/VBIAX","//*[@id='quote-summary']/div[1]/table/tbody/tr[2]/td[2]")

Not sure how reliable this is and it only pulls in YTD from the summary tab. The table provided by morningstar is easier to deal with. Theoretically, you could pull in the values from the performance tab, but that would require more investigation.

 

[target2019]

Tupak,
Check out an advanced search for "googlefinance". Some of the more recent threads may also help you fill in your cells.

I recall there are some links to google sheets in there also, but as expected, and others found out in this thread, those importxml functions do break.

If one right-clicks on a page, you can see all the elements used to construct a page. In there you can divine out what the elements are for a data point you're looking for.

 

[marko]

I got tired of fighting it and just entered my year's starting stock price--easy to look up--and compare it to my current price and calculate whatever I need from there. Thats fine for a set of stocks you own but not if you're researching; M* does that better anyway.

At least Google still provides todays closing price.

 

[donheff]

I can't retrieve anything from that Morningstar page to Google sheets. It works fine from Excel though, so I suspect that Morningstar is rejecting requests from the Google server that operates the ImportXml function.

I agree that GoogleFinance returns for mutual funds are very strange and look nothing like the data on the Morningstar page, even though their doc says they get the info from Morningstar.

Same here. I was going to ask Cathy to weigh in but now I am despairing of a good solution. At the same time my YTD pull died, my TSP fund price quotes also died. I am probably going to just skip the YTD and enter TSP manually since it is such a minor part of my portfolio.

 

[SoReady]

I was curious to see how this works and came up with a solution for yahoo (with some googling help):

=IMPORTXML("https://finance.yahoo.com/quote/VBIAX","//*[@id='quote-summary']/div[1]/table/tbody/tr[2]/td[2]")

Not sure how reliable this is and it only pulls in YTD from the summary tab. The table provided by morningstar is easier to deal with. Theoretically, you could pull in the values from the performance tab, but that would require more investigation.

Thank for this! It appears to work for me. I'll use this for now and keep an eye on the morningstar one. Maybe it will clear up after the new year.

You all are a great resource. Thanks again!

 

[tulak]

Same here. I was going to ask Cathy to weigh in but now I am despairing of a good solution. At the same time my YTD pull died, my TSP fund price quotes also died. I am probably going to just skip the YTD and enter TSP manually since it is such a minor part of my portfolio.

What are you using to pull in data for the TSP funds? Can you share the function?

 

[OverThinkMuch]

For quite sometime I've been able to use an IMPORTXML function within my google sheet. The exact function is...

=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=",":","VBIAX"), "//table/tbody[1]/tr[1]/td[5]")/100

From what I recall of html, this code tries to look inside a table. "tr" means table row, and "td" means table data.

When I view "page source", there's no table, rows or <td> on the page. It looks like Morningstar switched their internal representation to use <li>, meaning lists. As long as they use lists, a table-based approach won't work.

 

[tulak]

I still see a table. I can’t import anything from morningstar, so either I’m doing something wrong or it’s blocked. I wonder if there’s any debug for the importxml function? That would help narrow down where it’s failing. I’ll look into that later.

 

[cathy63]

From what I recall of html, this code tries to look inside a table. "tr" means table row, and "td" means table data.

When I view "page source", there's no table, rows or <td> on the page. It looks like Morningstar switched their internal representation to use <li>, meaning lists. As long as they use lists, a table-based approach won't work.

We're not seeing the same thing at all then.

When I visit this page (I cleaned up the URL a bit): https://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?t=VBIAX

I still get back HTML that looks like this:

        <table class="r_table3 width955px  print97" cellspacing="0" cellpadding="0" border="0">
        	<colgroup>...
            </colgroup>
            
             	<tr>...
             	</tr>
             <thead>
            	<tr>... </tr>
            </thead>
            <tbody>
                <tr class="action">
                	<th scope="row" class="row_lbl" style="word-break:break-all;overflow:hidden; ">VBIAX</th>
                		<td class="row_data">-1.11</td>
                		<td class="row_data">-1.47</td>
                		<td class="row_data">0.10</td>
                		<td class="row_data">1.38</td>
[COLOR="Red"]                		<td class="row_data">12.29</td>
[/COLOR]                		<td class="row_data">15.33</td>
                		<td class="row_data">15.55</td>
                		<td class="row_data">12.16</td>
                		<td class="row_data">11.39</td>
                		<td class="row_data_0">8.28</td>
				</tr>
....

the number in red should be targeted as "//table/tbody/tr[1]/td[5]". I tried using various other higher level targets to see if Google was getting different html, but I always get "Resource at url not found.", which I think means that when Google requests the page from Morningstar, the Morningstar server returns a 404 error (page not found).

 

[tulak]

An interesting side note - and maybe obvious to others - but it looks like the import functions in GoogleSheets aren't sent from your local computer. I'm trying to get a packet capture to see if I can see the response from Morningstar and I don't see anything.

I guess this makes sense, since if it was sent from your computer there wouldn't be an easy way for Morningstar to block the request. Since it's coming from Google, it's easier for them to block the request.

I suspect this is different for other spreadsheet programs, since they're not cloud based.

Unfortunately this is a black box. I haven't been able to find a way to see why the import functions fail.

 

[SoReady]

Just as a follow up. I started replacing the Yahoo ImportXML function for other MF's I have. For some reason it seems fine for VG but not so for FIDO.

For example, for FGBRX (Fidelity® Blue Chip Growth Fund) I use; =IMPORTXML("https://finance.yahoo.com/quote/FBGRX","//*[@id='quote-summary']/div[1]/table/tbody/tr[2]/td[2]") and it returns 26.88%. If I go to the Yahoo Finance web page (https://finance.yahoo.com/quote/FBGRX?p=FBGRX&.tsrc=fin-srch) it shows 24.10% which jives with the fidelity site ( https://fundresearch.fidelity.com/mutual-funds/performance-and-risk/316389303?type=sq-NavBar)

I'm not sure why the discrepancy.

 

[tulak]

Just as a follow up. I started replacing the Yahoo ImportXML function for other MF's I have. For some reason it seems fine for VG but not so for FIDO.

For example, for FGBRX (Fidelity® Blue Chip Growth Fund) I use; =IMPORTXML("https://finance.yahoo.com/quote/FBGRX","//*[@id='quote-summary']/div[1]/table/tbody/tr[2]/td[2]") and it returns 26.88%. If I go to the Yahoo Finance web page (https://finance.yahoo.com/quote/FBGRX?p=FBGRX&.tsrc=fin-srch) it shows 24.10% which jives with the fidelity site ( https://fundresearch.fidelity.com/mutual-funds/performance-and-risk/316389303?type=sq-NavBar)

I'm not sure why the discrepancy.

It looks like it's getting old data. If I use the following query: IMPORTXML("https://finance.yahoo.com/quote/FBGRX","//*[@id='quote-summary']/div[1]/table/tbody/tr[1]/td[2]")

I get 192.55 for the previous close. This was the previous close value on Nov 26th. So the YTD value you're seeing is from yesterday.

It looks like wherever Google is getting their updates isn't directly from the yahoo site, but some cached version. That is odd. You should eventually get the right YTD once Google updates their version of Yahoo's site, but who knows when that'll happen. Probably later today.

It's kind of annoying that you can't query the website directly from Google Sheets.

Btw, I use Google Sheets, but gave up on using any of the finance/scraping functions. I only have a handful of funds and decided it was easier to manual enter the prices (that's all I track). This would be a pain if I ever decided to track more data within a spreadsheet and could be enough to get me to move to Numbers (I'm on a Mac) or OpenOffice if I ever wanted to do anything more complex.

 

[cathy63]

delete

 

[target2019]

As noticed by others, Google has vast cache of requests, and you may or may not see a correct result.

When a source becomes too popular, it may get throttled to save processing time. You can't have confidence in your spreadsheets that dip into free resources. This doesn't apply just to Google.

 

[OverThinkMuch]

We're not seeing the same thing at all then.

When I visit this page (I cleaned up the URL a bit): https://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?t=VBIAX
...
the number in red should be targeted as "//table/tbody/tr[1]/td[5]". I tried using various other higher level targets to see if Google was getting different html, but I always get "Resource at url not found.", which I think means that when Google requests the page from Morningstar, the Morningstar server returns a 404 error (page not found).

With that URL, we are seeing the same thing. The URL from OP didn't work, so I tried my own. When I plug your cleaned up URL into IMPORTHTML(), I also get "Error ... Resource at url not found", even if I use "http" instead.

 

[cathy63]

With that URL, we are seeing the same thing. The URL from OP didn't work, so I tried my own. When I plug your cleaned up URL into IMPORTHTML(), I also get "Error ... Resource at url not found", even if I use "http" instead.

Sorry, I'm still confused. What's the URL of the Morningstar quote page you're looking at with the li tags? All the Morningstar links posted in this thread have tables, but you said you saw one with a list. I could probably parse that one if Google sheets will load it.