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: 68
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.

User avatar
Sparky1500
Posts: 20
Joined: Tue May 10, 2016 2:59 pm
Location: Chicago

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.
Retired August 3, 2018 and very grateful.

stocknoob4111
Posts: 278
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: 1573
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: 3393
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: 68
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: 1426
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.
87.5:12.5 — HODL the course!

User avatar
Bylo Selhi
Posts: 1074
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: 3393
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: 1074
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: 68
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: 193
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: 3393
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: 575
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: 68
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: 193
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: 68
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: 3393
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: 710
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: 3393
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: 1573
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?

TOJ
Posts: 364
Joined: Wed Mar 02, 2016 9:19 pm

Re: Replacement for Google Finance price quote function

Post by TOJ » Mon Aug 06, 2018 9:14 am

Just want to say thanks again for this. Google finance has routinely forgotten some mutual fund tickers which blows up my whole spreadsheet. Even now, Marketwatch has the new zero-cost mutual fund whereas google doesn't. FZROX.

User avatar
walletless
Posts: 827
Joined: Fri Aug 15, 2014 4:55 pm

Re: Replacement for Google Finance price quote function

Post by walletless » Mon Aug 06, 2018 11:18 am

You can also refer to my post on another thread in using Morningstar to pull prices: viewtopic.php?p=4043999#p4048310

MotoTrojan
Posts: 2093
Joined: Wed Feb 01, 2017 8:39 pm

Re: Replacement for Google Finance price quote function

Post by MotoTrojan » Tue Aug 07, 2018 9:58 am

Just what I was looking for! Now I can get my daily percent/$ change function to work (after 4:15 ET).

Part of me wants to code it to track ETF pricing, but I really need to try and look less, not more. Being able to quickly peak at the daily change in the afternoon on an interesting day will suffice.

I do not act on this type of evaluation (unless rebalancing is needed but that is rare at 100% equity).

MotoTrojan
Posts: 2093
Joined: Wed Feb 01, 2017 8:39 pm

Re: Replacement for Google Finance price quote function

Post by MotoTrojan » Mon Aug 20, 2018 12:56 pm

Not working today. Anybody else?

User avatar
walletless
Posts: 827
Joined: Fri Aug 15, 2014 4:55 pm

Re: Replacement for Google Finance price quote function

Post by walletless » Mon Aug 20, 2018 1:32 pm

MotoTrojan wrote:
Tue Aug 07, 2018 9:58 am
Part of me wants to code it to track ETF pricing, but I really need to try and look less, not more. Being able to quickly peak at the daily change in the afternoon on an interesting day will suffice.
If you end up doing this, please share here!

MotoTrojan
Posts: 2093
Joined: Wed Feb 01, 2017 8:39 pm

Re: Replacement for Google Finance price quote function

Post by MotoTrojan » Mon Aug 20, 2018 4:09 pm

walletless wrote:
Mon Aug 20, 2018 1:32 pm
MotoTrojan wrote:
Tue Aug 07, 2018 9:58 am
Part of me wants to code it to track ETF pricing, but I really need to try and look less, not more. Being able to quickly peak at the daily change in the afternoon on an interesting day will suffice.
If you end up doing this, please share here!
Would be quite easy.

Easiest way: Multiply closing MF price by ETF percent change.

Possibly more accurate way: Multiply closing MF price by (current ETF price / ETF NAV).

Could even use an if-statement so that this code is only used if the current MF price equals yesterday’s close. Would give you the ETF estimate until the new price had posted that afternoon. Early morning it would revert back to the etf estimate prior to market open.

Maybe I shall...

MotoTrojan
Posts: 2093
Joined: Wed Feb 01, 2017 8:39 pm

Re: Replacement for Google Finance price quote function

Post by MotoTrojan » Mon Aug 20, 2018 9:04 pm

walletless wrote:
Mon Aug 20, 2018 1:32 pm
MotoTrojan wrote:
Tue Aug 07, 2018 9:58 am
Part of me wants to code it to track ETF pricing, but I really need to try and look less, not more. Being able to quickly peak at the daily change in the afternoon on an interesting day will suffice.
If you end up doing this, please share here!
Okay here it is.

This is the code I used for the Price Cell of my VTSAX holding, which I am estimating using VTI.

=if(VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))<>Q25,VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7)),(Q25*(1+(googlefinance("vti","changepct")/100))))


B25 is a cell containing "VTSAX"

Q25 is a cell containing "=googlefinance("vtsax", "closeyest")"

Essentially if the pulled price from MarketWatch does not equal (that is what the <> means) yesterdays close from GoogleFinance, then it multiplies the ETFs current changepct by yesterdays VTSAX close to estimate the current value of the assets. Once the new price updates it will see that there is a difference and show the value from MarketWatch. As noted, in the AM it will revert back to an estimated price using the ETFs closing changepct due to the fact that MarketWatch's value will be equal to the closeyest. As soon as trading starts up, it'll swap back to a live updating estimate.

gostars
Posts: 439
Joined: Mon Oct 09, 2017 7:53 pm

Re: Replacement for Google Finance price quote function

Post by gostars » Tue Aug 21, 2018 12:54 am

For anyone scraping MarketWatch, if you leave off the &insttype=xxx it works for ETFs, mutual funds, and individual stocks.
This is what I use on my Google sheet script:

Code: Select all

var prefix = "http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=";

function getPrice(symbol) {
  var url = prefix + symbol;
  var resp = UrlFetchApp.fetch(url).getContentText();
  var price = resp.substring(resp.indexOf("Last:"));
  price = price.substring(0, price.indexOf("</div>"))
     .substring(price.indexOf("<div>"))
     .replace(/<div>/, "");
  return price;
}

function getName(symbol) {
  var url = prefix + symbol;
  var resp = UrlFetchApp.fetch(url).getContentText();
  var name = resp.substring(0, resp.indexOf(","))
     .substring(resp.indexOf("<title>"))
     .replace(/<title>/, "");
  var decode = new XML('<d>' + name + '</d>');
  return decode.toString();
}
Then getName(B29) for the full fund name, and getPrice(B29) for the price, where B29 has the ticker. I'm way too lazy to use different functions for each line, depending on the type of asset. Thanks to the people up thread I stole the original code from. Since I'm only using this for my rebalancing sheet, all I care about is the name and price; those who need other data may need their own adjustments. For those curious about the XML step, it's mostly there to convert "&amp;" to "&" in S&P funds.

mervinj7
Posts: 550
Joined: Thu Mar 27, 2014 3:10 pm

Re: Replacement for Google Finance price quote function

Post by mervinj7 » Thu Sep 13, 2018 5:22 pm

Does anybody have similar code for Google Sheets that can scrape the Fidelity workplace website for custom tickers used in 401ks? For example, I would like to parse the NAV of the "OHKA" fund below. OHKA can not be priced by Google Finance or by Morningstar. I know very little about coding but I can follow instructions. Thanks in advance!

https://workplaceservices.fidelity.com/ ... rices/OHKA

sarabayo
Posts: 26
Joined: Fri Jun 29, 2018 6:59 pm

Re: Replacement for Google Finance price quote function

Post by sarabayo » Fri Sep 14, 2018 4:18 am

mervinj7 wrote:
Thu Sep 13, 2018 5:22 pm
Does anybody have similar code for Google Sheets that can scrape the Fidelity workplace website for custom tickers used in 401ks? For example, I would like to parse the NAV of the "OHKA" fund below. OHKA can not be priced by Google Finance or by Morningstar. I know very little about coding but I can follow instructions. Thanks in advance!

https://workplaceservices.fidelity.com/ ... rices/OHKA
Here's what I use:

Code: Select all

1*ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/",tickerName),"/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value","noInherit,noTruncate,noHeaders")
Replace "tickerName" with Fidelity's custom ticker name, or an expression that evaluates to it (such as a reference to a spreadsheet cell). This code requires the ImportJSON script to run, which you can get from github and paste into your script editor window.
Last edited by sarabayo on Sat Sep 15, 2018 12:52 am, edited 1 time in total.

mervinj7
Posts: 550
Joined: Thu Mar 27, 2014 3:10 pm

Re: Replacement for Google Finance price quote function

Post by mervinj7 » Fri Sep 14, 2018 11:10 am

sarabayo wrote:
Fri Sep 14, 2018 4:18 am
Here's what I use:

Code: Select all

1*ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/",tickerName,"/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value","noInherit,noTruncate,noHeaders")
Sweet! I'll try it out this weekend. Unfortunately, Google Script editor is blocked for me from work.

bandoba
Posts: 94
Joined: Thu Apr 08, 2010 12:51 am

Re: Replacement for Google Finance price quote function

Post by bandoba » Fri Sep 14, 2018 11:15 pm

sarabayo wrote:
Fri Sep 14, 2018 4:18 am
Here's what I use:

Code: Select all

1*ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/",tickerName,"/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value","noInherit,noTruncate,noHeaders")
Replace "tickerName" with Fidelity's custom ticker name, or an expression that evaluates to it (such as a reference to a spreadsheet cell). This code requires the ImportJSON script to run, which you can get from github and paste into your script editor window.
Thanks sarabayo. I couldn't get your code working as posted. So I fixed it by changing following things:
  • Replaced "1." with "=" before "ImportJSON".
  • Changed CONCATENATE function to concatenate only first 2 values.
Here is the modified code:

Code: Select all

=ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/", tickerName), "/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value", "noInherit,noTruncate,noHeaders")

sarabayo
Posts: 26
Joined: Fri Jun 29, 2018 6:59 pm

Re: Replacement for Google Finance price quote function

Post by sarabayo » Sat Sep 15, 2018 12:53 am

bandoba wrote:
Fri Sep 14, 2018 11:15 pm
Thanks sarabayo. I couldn't get your code working as posted. So I fixed it by changing following things:
  • Replaced "1." with "=" before "ImportJSON".
  • Changed CONCATENATE function to concatenate only first 2 values.
Here is the modified code:

Code: Select all

=ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/", tickerName), "/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value", "noInherit,noTruncate,noHeaders")
Ah, sorry. Indeed, the CONCATENATE thing was a typo on my part, sorry about that - the actual expression I use in my spreadsheet is more complicated and I screwed up trying to boil it down before posting here.

The "1*" part (that's an asterisk, not a period) was to make sure that the value was interpreted as a number. For some reason my spreadsheet refused to format the value as a dollar amount (i.e. right-justified with a $ sign before it) unless I did this. I didn't mention the "=" at the beginning since you always have to write that before any formula.

Thanks for the corrections -- I've edited my post to fix the CONCATENATE parenthesis bug :)

bandoba
Posts: 94
Joined: Thu Apr 08, 2010 12:51 am

Re: Replacement for Google Finance price quote function

Post by bandoba » Sat Sep 15, 2018 2:28 am

sarabayo wrote:
Sat Sep 15, 2018 12:53 am
bandoba wrote:
Fri Sep 14, 2018 11:15 pm
Thanks sarabayo. I couldn't get your code working as posted. So I fixed it by changing following things:
  • Replaced "1." with "=" before "ImportJSON".
  • Changed CONCATENATE function to concatenate only first 2 values.
Here is the modified code:

Code: Select all

=ImportJSON(CONCATENATE("https://fundresearch.fidelity.com/api/workplacefunds/header/", tickerName), "/model/DetailsData/detailDataList/navDataRowData/navDailyAmount/value", "noInherit,noTruncate,noHeaders")
Ah, sorry. Indeed, the CONCATENATE thing was a typo on my part, sorry about that - the actual expression I use in my spreadsheet is more complicated and I screwed up trying to boil it down before posting here.

The "1*" part (that's an asterisk, not a period) was to make sure that the value was interpreted as a number. For some reason my spreadsheet refused to format the value as a dollar amount (i.e. right-justified with a $ sign before it) unless I did this. I didn't mention the "=" at the beginning since you always have to write that before any formula.

Thanks for the corrections -- I've edited my post to fix the CONCATENATE parenthesis bug :)
No worries, thanks for updates. I got to opportunity to learn something new because of your typos :sharebeer

Post Reply