|
|
11-29-2021, 04:57 PM
|
#21
|
Thinks s/he gets paid by the post
Join Date: Aug 2007
Posts: 2,873
|
Quote:
Originally Posted by 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/FBGR....tsrc=fin-srch) it shows 24.10% which jives with the fidelity site ( https://fundresearch.fidelity.com/mu...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.
__________________
Eat, Drink and Be Merry.
|
|
|
|
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!
Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!
You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!
|
11-29-2021, 05:07 PM
|
#22
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,706
|
delete
|
|
|
11-30-2021, 04:25 AM
|
#23
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,720
|
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.
|
|
|
11-30-2021, 08:35 AM
|
#24
|
Recycles dryer sheets
Join Date: May 2016
Posts: 313
|
Quote:
Originally Posted by cathy63
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/...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.
|
|
|
11-30-2021, 09:49 AM
|
#25
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,706
|
Quote:
Originally Posted by OverThinkMuch
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.
|
|
|
12-01-2021, 08:43 AM
|
#26
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
Quote:
Originally Posted by cathy63
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.
|
I also, would be interested if there is a solution to this that Cathy may be able to explore.
Bob
|
|
|
12-01-2021, 10:10 AM
|
#27
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,720
|
Quote:
Originally Posted by 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/FBGR....tsrc=fin-srch) it shows 24.10% which jives with the fidelity site ( https://fundresearch.fidelity.com/mu...type=sq-NavBar)
I'm not sure why the discrepancy.
|
FBGRX or FGBRX? You have it two ways.
|
|
|
12-01-2021, 10:25 AM
|
#28
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
Quote:
Originally Posted by target2019
FBGRX or FGBRX? You have it two ways.
|
Sorry, FBGRX.
|
|
|
12-01-2021, 06:08 PM
|
#29
|
Thinks s/he gets paid by the post
Join Date: Aug 2013
Location: North
Posts: 4,043
|
I just have two columns...
One with last year's last market close date:
=INDEX(GoogleFinance(AAPL, "close", "12/31/2020","2"),2,2)
And one that has the current price:
=GOOGLEFINANCE(AAPL,"price")
And then I do the math between the two in a third column to get my current YTD %
__________________
Time > $$$ ~ 100% equities ~ FIRE @2031
|
|
|
12-01-2021, 07:25 PM
|
#30
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,706
|
Quote:
Originally Posted by kgtest
I just have two columns...
One with last year's last market close date:
=INDEX(GoogleFinance(AAPL, "close", "12/31/2020","2"),2,2)
And one that has the current price:
=GOOGLEFINANCE(AAPL,"price")
And then I do the math between the two in a third column to get my current YTD %
|
To do the same calc that Morningstar is doing, you need to include divs and cap gains. The formula should be:
ytd return % = 100*(today's price - eoy price + ytd divs + ytd cap gains)/eoy price
|
|
|
12-02-2021, 07:16 AM
|
#31
|
Thinks s/he gets paid by the post
Join Date: Aug 2013
Location: North
Posts: 4,043
|
Quote:
Originally Posted by cathy63
To do the same calc that Morningstar is doing, you need to include divs and cap gains. The formula should be:
ytd return % = 100*(today's price - eoy price + ytd divs + ytd cap gains)/eoy price
|
Ahh, interesting. SO the google function does not include DIVs?
__________________
Time > $$$ ~ 100% equities ~ FIRE @2031
|
|
|
GoogleFinance functions...
12-02-2021, 07:40 AM
|
#32
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2011
Location: NC Triangle
Posts: 5,807
|
GoogleFinance functions...
I use a similar approach in Sheets to track a fund I use as a benchmark against my retirement portfolio.
The reason I can get away with it (mostly) is that the fund (a target date fund) only posts one distribution very late in the year.
[ADDED] I used to check (by hand) the year-end close and enter it manually. I like kgtest’s snippet better and have updated.
__________________
|
|
|
12-02-2021, 08:28 AM
|
#33
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,706
|
Quote:
Originally Posted by kgtest
Ahh, interesting. SO the google function does not include DIVs?
|
I have no idea what the Google function is doing. For mutual funds, the "ytdreturn" it's calculating is still wrong even if you exclude the distributions, so there's something really messed up in their code.
Usually historical price tables have a column called "adjusted price" which is the closing price for that date minus all distributions that have occurred since. I didn't look at Google Finance to see if it can produce an adjusted price, but if it can that's what you should use for 12/31 of last year instead of "close" in order to get an accurate ROI.
|
|
|
12-02-2021, 08:51 AM
|
#34
|
Recycles dryer sheets
Join Date: Feb 2011
Location: Arlington Heights
Posts: 271
|
I've fond the googlefinance functions to be fairly unreliable, at least for mutal funds, which is what I am interested in. but for reference the mutal fund parametes for googlefinance are:
"closeyest" - The previous day's closing price.
"date" - The date at which the net asset value was reported.
"returnytd" - The year-to-date return.
"netassets" - The net assets.
"change" - The change in the most recently reported net asset value and the one immediately prior.
"changepct" - The percentage change in the net asset value.
"yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
"returnday" - One-day total return.
"return1" - One-week total return.
"return4" - Four-week total return.
"return13" - Thirteen-week total return.
"return52" - Fifty-two-week (annual) total return.
"return156" - 156-week (3-year) total return.
"return260" - 260-week (5-year) total return.
"incomedividend" - The amount of the most recent cash distribution.
"incomedividenddate" - The date of the most recent cash distribution.
"capitalgain" - The amount of the most recent capital gain distribution.
"morningstarrating" - The Morningstar "star" rating.
"expenseratio" - The fund's expense ratio.
Bob
|
|
|
12-02-2021, 09:34 AM
|
#35
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,720
|
Quote:
Originally Posted by cathy63
I have no idea what the Google function is doing. For mutual funds, the "ytdreturn" it's calculating is still wrong even if you exclude the distributions, so there's something really messed up in their code.
Usually historical price tables have a column called "adjusted price" which is the closing price for that date minus all distributions that have occurred since. I didn't look at Google Finance to see if it can produce an adjusted price, but if it can that's what you should use for 12/31 of last year instead of "close" in order to get an accurate ROI.
|
It's calculating YTD just to end of previous month? I seem to recall somethiing like that.
|
|
|
12-03-2021, 06:33 AM
|
#36
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,328
|
Quote:
Originally Posted by tulak
What are you using to pull in data for the TSP funds? Can you share the function?
|
I could but that one stopped working a couple of weeks ago too.
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|