GoogleFinance functions...

[SoReady]

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

 

[target2019]

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.

FBGRX or FGBRX? You have it two ways.

 

[SoReady]

FBGRX or FGBRX? You have it two ways.

Sorry, FBGRX.

 

[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 %

 

[cathy63]

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

 

[kgtest]

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?

 

[steelyman]

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.

 

[cathy63]

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.

 

[SoReady]

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

 

[target2019]

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.

 

[donheff]

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.