Replacement for Google Finance price quote function

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Hiwatter
Posts: 54
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Tue Mar 27, 2018 5:04 pm

Does anyone have a formula for a 'YTD return' and '5 year return' of a stock? I already have the this for a mutual fund.

Here's the formula for a 1 year return, but I can't quite figure it out for the other 2 I mentioned above

=ROUND( ( GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2)

thanks!
Last edited by Hiwatter on Wed Mar 28, 2018 10:20 am, edited 1 time in total.

Sparky1500
Posts: 17
Joined: Tue May 10, 2016 2:59 pm

Re: Replacement for Google Finance price quote function

Post by Sparky1500 » Tue Mar 27, 2018 6:50 pm

Ata boy, the529guy! Love this solution - no longer have to wait overnight for the updated market values from Googlefinance.

stocknoob4111
Posts: 170
Joined: Sun Jan 07, 2018 12:52 pm

Re: Replacement for Google Finance price quote function

Post by stocknoob4111 » Tue Mar 27, 2018 7:08 pm

Great piece of info, thanks! GoogleFinance API was choking on VOO today for some reason and was looking for alternatives :D Also Google Finance's quotes are seriously delayed for Mutual Funds. I took your solution and added it to a script, in Google Sheets you can use Tools > Script Editor and paste the functions below and then just use the functions as =getETF("VOO") or =getFund("VTIAX") etc.

Code: Select all

var prefix = "http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=";
var etfPostfix = "&insttype=ETF";
var fundPostfix = "&insttpye=Fund";

function getETF(symbol) {
  var url = prefix + symbol + etfPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();

  return extractValue(resp);
}

function getFund(symbol) {
  var url = prefix + symbol + fundPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();
  
  return extractValue(resp);
}

function extractValue(inputMarkup) {
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("Last:"));
  
   return +inputMarkup.substring(0, inputMarkup.indexOf("</div>"))
     .substring(inputMarkup.indexOf("<div>"))
     .replace(/<div>/, "");
}

User avatar
sperry8
Posts: 1519
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Replacement for Google Finance price quote function

Post by sperry8 » Tue Mar 27, 2018 7:12 pm

Hiwatter wrote:
Tue Mar 27, 2018 5:04 pm
Does anyone have a formula for a 'YTD return' and '5 year return' of a stock? I already have the this for a mutual fund.

Here is is for a 1 year return, but I can't quite figure it out for the other 2 I mentioned above

=ROUND( ( GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2)

thanks!
See this: https://support.google.com/docs/answer/3093281?hl=en
Humbling BH contest results: 2017: #516 of 647 | 2016: #121 of 610 | 2015: #18 of 552 | 2014: #225 of 503 | 2013: #383 of 433 | 2012: #366 of 410 | 2011: #113 of 369 | 2010: #53 of 282

User avatar
The529guy
Posts: 576
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy » Tue Mar 27, 2018 8:09 pm

Sparky1500 wrote:
Tue Mar 27, 2018 6:50 pm
Ata boy, the529guy! Love this solution - no longer have to wait overnight for the updated market values from Googlefinance.
Thanks. You know what they say... spreadsheet code is the gift that keeps on giving.

DrDrXanderLi
Posts: 11
Joined: Wed Jan 25, 2017 3:36 pm

Re: Replacement for Google Finance price quote function

Post by DrDrXanderLi » Wed Mar 28, 2018 5:39 am

Expanding stacknoob4111's code snippet to include a function of yield... but does anyone know what the googlefinance yield is different from the marketwatch yield for ETFs?

Code: Select all

var prefix = "http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=";
var etfPostfix = "&insttype=ETF";
var fundPostfix = "&insttpye=Fund";

function getETF(symbol) {
  var url = prefix + symbol + etfPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();

  return extractValue(resp);
}

function getFund(symbol) {
  var url = prefix + symbol + fundPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();
  
  return extractValue(resp);
}

function getETFyield(symbol) {
  var url = prefix + symbol + etfPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();

  return extractYield(resp);
}

function getFundyield(symbol) {
  var url = prefix + symbol + fundPostfix;
  var resp = UrlFetchApp.fetch(url).getContentText();
  
  return extractYield(resp);
}

function extractValue(inputMarkup) {
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("Last:"));
  
   return +inputMarkup.substring(0, inputMarkup.indexOf("</div>"))
     .substring(inputMarkup.indexOf("<div>"))
     .replace(/<div>/, "");
}

function extractYield(inputMarkup) {
  inputMarkup = inputMarkup.substring(inputMarkup.indexOf("Yield:"));
  
   return +inputMarkup.substring(0, inputMarkup.indexOf("</div>"))
     .substring(inputMarkup.indexOf("<div>"))
     .replace(/<div>/, "")
     .replace(/%/, "");
}

User avatar
munemaker
Posts: 2730
Joined: Sat Jan 18, 2014 6:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker » Wed Mar 28, 2018 8:21 am

sperry8 wrote:
Tue Mar 27, 2018 2:00 pm
The529guy wrote:
Fri Feb 02, 2018 7:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.

Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.

Before:

Code: Select all

=googlefinance(B2, "price")
After:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",B2,"&insttype=Fund"), "table"),3,1)),7))
In both cases, cell B2 is where the ticker symbol (i.e., VTSAX) lives in my spreadsheet.
Marketwatch is still working (even though GOOGLEFINANCE is having troubles today). Thanks!
Today, March 28: The various I import from WSJ and stock prices using the google finance formulas seem to be working today. However, the data from Big Charts through MarketWatch is very very slow. Can't seem to get everything updated at one time.

Update: 9:25 am - Everything did update just in time for the market open!

Hiwatter
Posts: 54
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Wed Mar 28, 2018 10:18 am

sperry8 wrote:
Tue Mar 27, 2018 7:12 pm
Hiwatter wrote:
Tue Mar 27, 2018 5:04 pm
Does anyone have a formula for a 'YTD return' and '5 year return' of a stock? I already have the this for a mutual fund.

Here is is for a 1 year return, but I can't quite figure it out for the other 2 I mentioned above

=ROUND( ( GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2)

thanks!
See this: https://support.google.com/docs/answer/3093281?hl=en
I know that page well, but "returnytd" and "return260" do not work for stocks... only mutual funds

User avatar
randomizer
Posts: 1172
Joined: Sun Jul 06, 2014 3:46 pm

Re: Replacement for Google Finance price quote function

Post by randomizer » Wed Mar 28, 2018 10:20 am

I really don’t need my price information to be that up-to-date.

User avatar
Bylo Selhi
Posts: 1058
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Replacement for Google Finance price quote function

Post by Bylo Selhi » Wed Mar 28, 2018 1:36 pm

randomizer wrote:
Wed Mar 28, 2018 10:20 am
I really don’t need my price information to be that up-to-date.
Agreed. What's more, this was a temporary glitch whereas Yahoo's feed stopped working altogether. Other websites go down too from time to time. It happens.

P.S. If the people who were inconvenienced as a result of Google's recent data feed glitch contact Google to complain, I'm fairly sure Google will offer to reimburse them at least 10x the amount they paid Google for the data feed service.

User avatar
munemaker
Posts: 2730
Joined: Sat Jan 18, 2014 6:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker » Wed Mar 28, 2018 3:24 pm

Bylo Selhi wrote:
Wed Mar 28, 2018 1:36 pm
randomizer wrote:
Wed Mar 28, 2018 10:20 am
I really don’t need my price information to be that up-to-date.
Agreed. What's more, this was a temporary glitch whereas Yahoo's feed stopped working altogether. Other websites go down too from time to time. It happens.

P.S. If the people who were inconvenienced as a result of Google's recent data feed glitch contact Google to complain, I'm fairly sure Google will offer to reimburse them at least 10x the amount they paid Google for the data feed service.
Why the snarky reply?

User avatar
Bylo Selhi
Posts: 1058
Joined: Mon Feb 19, 2007 10:40 pm
Location: www.bylo.org in the Great White North
Contact:

Re: Replacement for Google Finance price quote function

Post by Bylo Selhi » Wed Mar 28, 2018 4:14 pm

munemaker wrote:
Wed Mar 28, 2018 3:24 pm
Why the snarky reply?
My apologies. No snarkyness was intended towards anyone. The P.S. was meant as a general joke to those who felt inconvenienced by the glitch.

User avatar
The529guy
Posts: 576
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy » Thu Mar 29, 2018 7:01 pm

randomizer wrote:
Wed Mar 28, 2018 10:20 am
I really don’t need my price information to be that up-to-date.
This isn't the thread you're looking for.
Image

Hiwatter
Posts: 54
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Tue Apr 03, 2018 4:48 pm

Hiwatter wrote:
Tue Mar 27, 2018 5:04 pm
Does anyone have a formula for a 'YTD return' and '5 year return' of a stock? I already have the this for a mutual fund.

Here's the formula for a 1 year return, but I can't quite figure it out for the other 2 I mentioned above

=ROUND( ( GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", datevalue(today()-365) ) , 2,2) - 1 ) * 100 ,2)

thanks!
Bumping this question one more time... anyone? please!

libralibra
Posts: 142
Joined: Sat Jul 30, 2011 2:01 pm

Re: Replacement for Google Finance price quote function

Post by libralibra » Tue Apr 03, 2018 6:44 pm

replace as the first arg to your INDEX function (the bolded part below):

1yr ago=GOOGLEFINANCE("IBM","price", today()-365)
5yr ago=GOOGLEFINANCE("IBM","price", today()-365*5)
beginning of this year=GOOGLEFINANCE("IBM","price", date(year(today()),1,1))

ps. you could simplify the formula to

= GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", today()-365) , 2,2) - 1

and just use the cell to format the %

User avatar
munemaker
Posts: 2730
Joined: Sat Jan 18, 2014 6:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker » Tue Apr 03, 2018 8:26 pm

libralibra wrote:
Tue Apr 03, 2018 6:44 pm
replace as the first arg to your INDEX function (the bolded part below):

1yr ago=GOOGLEFINANCE("IBM","price", today()-365)
5yr ago=GOOGLEFINANCE("IBM","price", today()-365*5)
beginning of this year=GOOGLEFINANCE("IBM","price", date(year(today()),1,1))

ps. you could simplify the formula to

= GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", today()-365) , 2,2) - 1

and just use the cell to format the %
Thanks for posting this.

Just a caution. This calculation does not include dividends and therefor is price appreciation only and not the total return.

need403bhelp
Posts: 514
Joined: Thu May 28, 2015 6:25 pm

Re: Replacement for Google Finance price quote function

Post by need403bhelp » Thu Apr 05, 2018 11:41 am

The529guy wrote:
Fri Feb 02, 2018 7:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.

Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.

Before:

Code: Select all

=googlefinance(B2, "price")
After:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",B2,"&insttype=Fund"), "table"),3,1)),7))
In both cases, cell B2 is where the ticker symbol (i.e., VTSAX) lives in my spreadsheet.
Thanks so much! Does this need to be placed in Google Sheets, or would it work directly in Excel also? Thank you again!

EDIT: I tried it in Excel but it didn't work. Works in Google Sheets, though. Thanks so much!

Hiwatter
Posts: 54
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Fri Apr 06, 2018 11:12 am

libralibra wrote:
Tue Apr 03, 2018 6:44 pm
replace as the first arg to your INDEX function (the bolded part below):

1yr ago=GOOGLEFINANCE("IBM","price", today()-365)
5yr ago=GOOGLEFINANCE("IBM","price", today()-365*5)
beginning of this year=GOOGLEFINANCE("IBM","price", date(year(today()),1,1))

ps. you could simplify the formula to

= GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", today()-365) , 2,2) - 1

and just use the cell to format the %
Thanks for this! However, I'm still struggling with the YTD formula...

I'd like it to display the 'YTD return' as a percentage. The formula above seems to give me the price of the stock on 1/2/18

Thanks again for the help. Much appreciated!

libralibra
Posts: 142
Joined: Sat Jul 30, 2011 2:01 pm

Re: Replacement for Google Finance price quote function

Post by libralibra » Mon Apr 16, 2018 12:32 pm

Hiwatter wrote:
Fri Apr 06, 2018 11:12 am

Thanks for this! However, I'm still struggling with the YTD formula...

I'd like it to display the 'YTD return' as a percentage. The formula above seems to give me the price of the stock on 1/2/18

Thanks again for the help. Much appreciated!
That's the denominator for your original equation,

= GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", date(year(today()),1,1)) , 2,2) - 1

Then click on the % to format your cell/column as a percent.

Hiwatter
Posts: 54
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Tue Apr 17, 2018 2:37 pm

libralibra wrote:
Mon Apr 16, 2018 12:32 pm
Hiwatter wrote:
Fri Apr 06, 2018 11:12 am

Thanks for this! However, I'm still struggling with the YTD formula...

I'd like it to display the 'YTD return' as a percentage. The formula above seems to give me the price of the stock on 1/2/18

Thanks again for the help. Much appreciated!
That's the denominator for your original equation,

= GOOGLEFINANCE("IBM","price") / index(GOOGLEFINANCE("IBM","price", date(year(today()),1,1)) , 2,2) - 1

Then click on the % to format your cell/column as a percent.
Got it. Thanks!

Post Reply