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: 67
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: 220
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: 1550
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: 610
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: 3107
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: 67
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: 1320
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.
75:25 — HODL the course!

User avatar
Bylo Selhi
Posts: 1067
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: 3107
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: 1067
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: 610
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: 67
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: 170
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: 3107
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: 560
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: 67
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: 170
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: 67
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!

Indigo_Jon
Posts: 4
Joined: Mon Jan 08, 2018 9:23 pm

Re: Replacement for Google Finance price quote function

Post by Indigo_Jon » Mon Apr 30, 2018 10:11 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.
Any idea how this would need to be changed to work with Open Office Calc, since I don't have MS Excel?

ucla-engineer
Posts: 16
Joined: Wed Apr 26, 2017 12:01 am

Re: Replacement for Google Finance price quote function

Post by ucla-engineer » Mon Apr 30, 2018 11:45 pm

Does anyone know if there is a similar excel function? 6-12 months ago yahoo shut down their server. I used to use:
=NUMBERVALUE(WEBSERVICE("https://download.finance.yahoo.com/d/qu ... EF!&"&f=l1"))

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Tue May 01, 2018 6:22 pm

The529guy wrote:
Sat Feb 03, 2018 10:31 am
BH13 wrote:
Fri Feb 02, 2018 9:56 pm
Any chance you have a daily change function using the marketwatch quote?
Let's look at the original function for the closing NAV and the source itself:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",B2,"&insttype=Fund"), "table"),3,1)),7))
Image
Image from bigcharts.marketwatch.com results

The "Last" NAV quote (68.81) is located in the 3rd row of the 1st column. ("VTSAX" in the 1st row, "hide quote" in the 2nd row.)

If you scroll over to the end of the code above, you'll see 3,1 to import the value from that table cell.

The daily change values are in the 3rd and 4th rows of the 2nd column. You'd use 3,2 for "Change" (-1.46) and 4,2 for "Percent Change" (-2.08%).

For reference, it's the INDEX function that returns a value based on a cell's row and column.
Scraping data from bigcharts per this post worked for me for quite a while, but lately it has totally stopped working. Anyone else experience this? Anyone have any ideas?


Thanks!

EHEngineer
Posts: 688
Joined: Sat Feb 28, 2015 4:35 pm

Re: Replacement for Google Finance price quote function

Post by EHEngineer » Tue May 01, 2018 7:37 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.
<snip>
FYI - Google is updating mutual fund prices between 12:30-12:40 AM EST. I used a google apps script to check google finance mutual fund quotes every 5 minutes and look for a change. EDIT: it's 1:30-1:40 AM EST, my google time stamps have been off since I changed to the new gmail.

I have been using the etf workaround with importhtml and importxml calls.

Good thread. Thanks for posting.
Last edited by EHEngineer on Thu May 03, 2018 12:15 am, edited 1 time in total.
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Tue May 01, 2018 7:52 pm

munemaker wrote:
Tue May 01, 2018 6:22 pm
The529guy wrote:
Sat Feb 03, 2018 10:31 am
BH13 wrote:
Fri Feb 02, 2018 9:56 pm
Any chance you have a daily change function using the marketwatch quote?
Let's look at the original function for the closing NAV and the source itself:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",B2,"&insttype=Fund"), "table"),3,1)),7))
Image
Image from bigcharts.marketwatch.com results

The "Last" NAV quote (68.81) is located in the 3rd row of the 1st column. ("VTSAX" in the 1st row, "hide quote" in the 2nd row.)

If you scroll over to the end of the code above, you'll see 3,1 to import the value from that table cell.

The daily change values are in the 3rd and 4th rows of the 2nd column. You'd use 3,2 for "Change" (-1.46) and 4,2 for "Percent Change" (-2.08%).

For reference, it's the INDEX function that returns a value based on a cell's row and column.
Scraping data from bigcharts per this post worked for me for quite a while, but lately it has totally stopped working. Anyone else experience this? Anyone have any ideas?


Thanks!
Not sure - it's still working for me. Every once in a while, there's a hiccup, but only for some funds.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Tue May 01, 2018 7:52 pm

EHEngineer wrote:
Tue May 01, 2018 7:37 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.
<snip>
FYI - Google is updating mutual fund prices between 12:30-12:40 AM EST. I used a google apps script to check google finance mutual fund quotes every 5 minutes and look for a change.

I have been using the etf workaround with importhtml and importxml calls.

Good thread. Thanks for posting.
No problem - I'm glad it's helpful! Thanks for the insights into Google.

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Thu May 03, 2018 7:04 pm

The529guy wrote:
Tue May 01, 2018 7:52 pm


Scraping data from bigcharts per this post worked for me for quite a while, but lately it has totally stopped working. Anyone else experience this? Anyone have any ideas?


Thanks!
Not sure - it's still working for me. Every once in a while, there's a hiccup, but only for some funds.
[/quote]

Looking into this further, it appears 3 funds are consistently not updating (that previously updated with no problems): VBTLX, VTSAX, FEMKX. Any ideas?

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

Re: Replacement for Google Finance price quote function

Post by sperry8 » Fri May 04, 2018 10:34 am

munemaker wrote:
Thu May 03, 2018 7:04 pm
The529guy wrote:
Tue May 01, 2018 7:52 pm


Scraping data from bigcharts per this post worked for me for quite a while, but lately it has totally stopped working. Anyone else experience this? Anyone have any ideas?


Thanks!
Not sure - it's still working for me. Every once in a while, there's a hiccup, but only for some funds.
Looking into this further, it appears 3 funds are consistently not updating (that previously updated with no problems): VBTLX, VTSAX, FEMKX. Any ideas?
[/quote]

VPMAX is not working either. But GoogleFinance is working for it, so as long as there is one, I'm good.

You can also use Nasdaq.com and ETF.com if you have ETFs or other stocks listed on those sites. I use both - pull in 1 year returns for ETFs via ETF.com and 30 day Yield info from Nasdaq.com for Muni funds (so I can monitor whether tax free or taxable is better).
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

Indigo_Jon
Posts: 4
Joined: Mon Jan 08, 2018 9:23 pm

Re: Replacement for Google Finance price quote function

Post by Indigo_Jon » Fri Jun 08, 2018 9:37 am

The529guy wrote:
Sat Feb 03, 2018 10:31 am
BH13 wrote:
Fri Feb 02, 2018 9:56 pm
Any chance you have a daily change function using the marketwatch quote?
Let's look at the original function for the closing NAV and the source itself:

Code: Select all

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",B2,"&insttype=Fund"), "table"),3,1)),7))
Image
Image from bigcharts.marketwatch.com results

The "Last" NAV quote (68.81) is located in the 3rd row of the 1st column. ("VTSAX" in the 1st row, "hide quote" in the 2nd row.)

If you scroll over to the end of the code above, you'll see 3,1 to import the value from that table cell.

The daily change values are in the 3rd and 4th rows of the 2nd column. You'd use 3,2 for "Change" (-1.46) and 4,2 for "Percent Change" (-2.08%).

For reference, it's the INDEX function that returns a value based on a cell's row and column.
I would like to pull the date stamp, located at the right end of the first row (I think that's cell 1, 2). I added the "T" function to your formula to display the contents of that cell, but referencing the cell as "1,2" brings up nothing, while "1,1" displays "VTSAX Vanguard Total Stock Market Index Fund;Admiral (FUND) Delayed Quote Data", so I know it's row 1

The formula I used is: =T(Index(ImportHtml(CONCATENATE(<reference to bigcharts URL>,"&insttype=Fund"), "table"),1,1))

How can I get that date cell to display?

Post Reply