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
User avatar
The529guy
Posts: 577
Joined: Fri May 23, 2014 1:08 am

Replacement for Google Finance price quote function

Post by The529guy » 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.
Last edited by The529guy on Fri Feb 02, 2018 7:25 pm, edited 1 time in total.

tesuzuki2002
Posts: 263
Joined: Fri Dec 11, 2015 12:40 pm

Re: Replacing Google Finance price quote function

Post by tesuzuki2002 » Fri Feb 02, 2018 7:12 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.

Is the update much faster and real time with Market watch?

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

Re: Replacing Google Finance price quote function

Post by The529guy » Fri Feb 02, 2018 7:15 pm

Yes, real-time in the sense that -- right now at 7:15pm ET -- marketwatch.com returns today's closing NAV and finance.google.com still returns yesterday's closing NAV.

And, even though finance.google.com's quote tool returns the NAV from yesterday (Feb 1), its timestamp for the quote says "Jan 31, 7:00PM EST".

venkman
Posts: 495
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman » Fri Feb 02, 2018 9:46 pm

The529guy wrote:
Fri Feb 02, 2018 7:15 pm
Yes, real-time in the sense that -- right now at 7:15pm ET -- marketwatch.com returns today's closing NAV and finance.google.com still returns yesterday's closing NAV.

And, even though finance.google.com's quote tool returns the NAV from yesterday (Feb 1), its timestamp for the quote says "Jan 31, 7:00PM EST".
This is useful. Thanks.

I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.

BH13
Posts: 46
Joined: Thu Oct 20, 2011 2:38 pm

Re: Replacement for Google Finance price quote function

Post by BH13 » Fri Feb 02, 2018 9:56 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.
Thank you! This is much better than the Vanguard only function I have been using:

Code: Select all

=importXML("https://personal.vanguard.com/us/JSP/Funds/VGITab/VGIFundPricePerfTabContent.jsf?FundIntExt=INT&FundId=0585","//*[@id='currentQuoteForm:priceTabletbody0']/tr[1]/td[2]")
Above function required knowing the Vanguard FundId ie 0585 is VTSAX. Of course it doesn't work for non-Vanguard funds.

Any chance you have a daily change function using the marketwatch quote?

cheesepep
Posts: 743
Joined: Wed Feb 17, 2010 10:58 pm

Re: Replacement for Google Finance price quote function

Post by cheesepep » Sat Feb 03, 2018 12:12 am

Nice. I use Apple numbers for my stocks and quotes but have been very disappointed lately. Getting stock quotes originally debuted a while ago and I got real time quotes or close to it. An “update” later and now I get closenof market quotes from YESTERDAY! Really?! Can I at least get close if market quotes from today? Sigh...

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

Re: Replacing Google Finance price quote function

Post by The529guy » Sat Feb 03, 2018 9:46 am

venkman wrote:
Fri Feb 02, 2018 9:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.

Edit: If you want to automate pulling the 2017 year-end NAV, you could use the methodology below to import from MarketWatch's Historical Quotes tool. For VTSAX (Dec 29, 2017):

http://bigcharts.marketwatch.com/histor ... %2F29%2F17
Last edited by The529guy on Sat Feb 03, 2018 10:59 am, edited 2 times in total.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » 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.
Last edited by The529guy on Sat Feb 03, 2018 11:23 am, edited 1 time in total.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Sat Feb 03, 2018 10:37 am

BH13 wrote:
Fri Feb 02, 2018 9:56 pm
Any chance you have a daily change function using the marketwatch quote?
Admittedly, I still just use Google Finance with the corresponding Vanguard ETF ticker, since those update throughout the day:

Code: Select all

=googlefinance("VTI", "changepct")
Last edited by The529guy on Sat Feb 03, 2018 11:22 am, edited 1 time in total.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Sat Feb 03, 2018 10:50 am

p.s. MarketWatch provides a quote for TIAA Real Estate Account using symbol QREARX.

moshe
Posts: 438
Joined: Thu Dec 12, 2013 1:18 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by moshe » Sat Feb 03, 2018 11:09 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.
+1 Works much better than having to wait until the next day for MF updates from Google.

Thank you very much for sharing!
~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams

User avatar
Dale_G
Posts: 3000
Joined: Tue Feb 20, 2007 5:43 pm
Location: Central Florida - on the grown up side of 80

Re: Replacement for Google Finance price quote function

Post by Dale_G » Sat Feb 03, 2018 5:14 pm

Thank you for the heads up 529. I would have had no prayer of figuring this out by myself.

For some reason I get an error if the price of the fund is less than $10.00, so in that case I use BH13's technique for Vanguard funds.

Dale
Volatility is my friend

afatcat
Posts: 35
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat » Sat Feb 03, 2018 7:08 pm

Thanks for this. I was missing getting the nightly updates after they went away.

venkman
Posts: 495
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman » Sat Feb 03, 2018 7:59 pm

The529guy wrote:
Sat Feb 03, 2018 9:46 am
venkman wrote:
Fri Feb 02, 2018 9:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
The problem with that is the NAV will drop when the fund makes a distribution, and the dividends won't get counted toward the YTD total return.

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

Re: Replacing Google Finance price quote function

Post by The529guy » Sat Feb 03, 2018 11:30 pm

venkman wrote:
Sat Feb 03, 2018 7:59 pm
The529guy wrote:
Sat Feb 03, 2018 9:46 am
venkman wrote:
Fri Feb 02, 2018 9:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
The problem with that is the NAV will drop when the fund makes a distribution, and the dividends won't get counted toward the YTD total return.
Good point. I'm so used to updating my share numbers after dividend payouts, but I guess that might not make sense for mock portfolios.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Sat Feb 03, 2018 11:59 pm

Dale_G wrote:
Sat Feb 03, 2018 5:14 pm
Thank you for the heads up 529. I would have had no prayer of figuring this out by myself.

For some reason I get an error if the price of the fund is less than $10.00, so in that case I use BH13's technique for Vanguard funds.

Dale
Dale, great catch! Try adjusting the final number in the function from 7 to 6. I was able to get sub-$10 prices to work with this change.

To see why, keep the 7 and delete "VALUE". With an X.XX price, the last 7 characters in the cell include the colon after "Last" (see RIGHT function), which the VALUE function doesn't like.

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

Re: Replacing Google Finance price quote function

Post by The529guy » Sun Feb 04, 2018 12:43 am

The529guy wrote:
Sat Feb 03, 2018 9:46 am
venkman wrote:
Fri Feb 02, 2018 9:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.

Edit: If you want to automate pulling the 2017 year-end NAV, you could use the methodology below to import from MarketWatch's Historical Quotes tool. For VTSAX (Dec 29, 2017):

http://bigcharts.marketwatch.com/histor ... %2F29%2F17
Update: Try the WSJ's quote tool. Here's the result for VTSAX. I'm able to access the Total Returns table like this:

Code: Select all

=IMPORTHTML("http://quotes.wsj.com/mutualfund/VTSAX?mod=searchresults_companyquotes", "table", 3)
Using the INDEX function described in this thread, you can extract the YTD, 1YR, 3YR, 5YR, and 10YR total returns for the fund.

Emilyjane
Posts: 108
Joined: Sat Jul 27, 2013 6:39 am

Re: Replacement for Google Finance price quote function

Post by Emilyjane » Sun Feb 04, 2018 1:40 pm

Thanks, that is a wonderful replacement!
"Real knowledge is to know the extent of one's ignorance", Confucius

Grasshopper
Posts: 863
Joined: Sat Oct 09, 2010 3:52 pm

Re: Replacement for Google Finance price quote function

Post by Grasshopper » Sun Feb 04, 2018 2:07 pm

Thanks, the529guy.

venkman
Posts: 495
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman » Mon Feb 05, 2018 12:27 am

The529guy wrote:
Sun Feb 04, 2018 12:43 am
Update: Try the WSJ's quote tool. Here's the result for VTSAX. I'm able to access the Total Returns table like this:

Code: Select all

=IMPORTHTML("http://quotes.wsj.com/mutualfund/VTSAX?mod=searchresults_companyquotes", "table", 3)
Using the INDEX function described in this thread, you can extract the YTD, 1YR, 3YR, 5YR, and 10YR total returns for the fund.
It worked! Thanks. :happy

User avatar
One Ping
Posts: 334
Joined: Thu Sep 24, 2015 4:53 pm

Re: Replacement for Google Finance price quote function

Post by One Ping » Wed Feb 07, 2018 12:31 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.
Anyone have an approach that works with EXCEL?
"Re-verify our range to target ... one ping only."

User avatar
oneleaf
Posts: 2336
Joined: Mon Feb 19, 2007 5:48 pm

Re: Replacement for Google Finance price quote function

Post by oneleaf » Wed Feb 07, 2018 5:40 pm

This is great. I have had issues with the slow GoogleFinance update.

jasg
Posts: 82
Joined: Wed May 08, 2013 7:10 pm

Re: Replacement for Google Finance price quote function

Post by jasg » Wed Feb 07, 2018 8:32 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.
Thanks for this! I too was unhappy with the 2AM EST updates for mutual funds. This seems to update at 6PM EST as GoogleFinance() used to do.

Two changes I found useful:

1) Using 'quickchart' is shorter and you can eliminate the CONCATENATE() with "&A1&" as shown below.

Code: Select all

=VALUE(Right((Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&A1&"", "table"),3,1)),7)) 
2) You can get the reporting date from index 1,6 (then use an appropriate cell format to display date or time, or both)

Code: Select all

=Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&A1&"", "table"), 1, 6)   

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Thu Feb 08, 2018 8:00 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.
I really appreciate all the tips in this thread.

After reading this, I put together a Google Sheets and ran into this problem today with the daily change values, with both $ and %. Today VBTLX had zero price change, and the change value and percent came back as #value!. I am guessing this is because of the zero change. Any way to fix this?

User avatar
bostondan
Posts: 496
Joined: Sun Aug 18, 2013 12:21 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by bostondan » Fri Feb 09, 2018 12:03 pm

Not sure why, but this suddenly started giving me completely incorrect values for VTI only. I noticed my US allocation was suddenly 10% below target and thought that something horrible must have happened in the markets. I then calculated it myself and realized it was returning an invalid result. I couldn't figure out why, but it was doing it across all my spreadsheets. I only tested it with a few ETFs, but it worked for all but VTI.

While I liked that this method was updated faster, I have reverted to the googlefinance function given that it was very reliable even if delayed.
“There may be times when we are powerless to prevent injustice, but there must never be a time when we fail to protest.” - Elie Wiesel

maineminder
Posts: 21
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacement for Google Finance price quote function

Post by maineminder » Fri Feb 09, 2018 12:05 pm

Thanks, this is extremely useful and I'll eventually move my spread sheet to use this.

BTW the right function will break down for a few cases depending on the number of digits that are returned. I found that in some cases the right 7 were not enough (and too many as pointed out earlier).

VTI seems to be returning 8 characters today. 4 digits past the decimal point on the right. I'll have to ponder a solution for this.

132.6197

When set to 7 you get this:

32.6197

As a side, how often do you find yourself tweaking this when marketwatch changes the table since row/column are hard-coded?

User avatar
bostondan
Posts: 496
Joined: Sun Aug 18, 2013 12:21 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by bostondan » Fri Feb 09, 2018 12:08 pm

maineminder wrote:
Fri Feb 09, 2018 12:05 pm
VTI seems to be returning 8 characters today. 4 digits past the decimal point on the right. I'll have to ponder a solution for this.
That makes sense. I didn't spend too much time debugging it.

I am amazed there is not a simple API available that returns plain text ETF/stock/mutual fund prices!
“There may be times when we are powerless to prevent injustice, but there must never be a time when we fail to protest.” - Elie Wiesel

maineminder
Posts: 21
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacement for Google Finance price quote function

Post by maineminder » Fri Feb 09, 2018 1:35 pm

This seems to work for the limited testing I did for quotes with 1,2,3 digits to the left of the decimal point.

B55 is set to VTI. This splits the returned value based on " " and displays the 2nd half.

=Index(SPLIT(Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/quickc ... .asp?symb=", B55), "table"),3,1)," "),0,2)

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Sat Feb 10, 2018 11:54 am

munemaker wrote:
Thu Feb 08, 2018 8:00 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.
I really appreciate all the tips in this thread.

After reading this, I put together a Google Sheets and ran into this problem today with the daily change values, with both $ and %. Today VBTLX had zero price change, and the change value and percent came back as #value!. I am guessing this is because of the zero change. Any way to fix this?
No one responded to my question, but I figured it out myself. If you are importing price change and it is zero, you get an error. You can use the Google Sheets IFERROR formula to have the value changed to $0.00 when you receive an error for price change. Works great.

User avatar
AtlasShrugged?
Posts: 511
Joined: Wed Jul 15, 2015 6:08 pm

Re: Replacement for Google Finance price quote function

Post by AtlasShrugged? » Sun Feb 11, 2018 6:42 am

Bogleheads....How would this work in an *ods file, if at all?

=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
“If you don't know, the thing to do is not to get scared, but to learn.”

4fitness
Posts: 17
Joined: Fri Feb 17, 2017 9:29 pm

Re: Replacement for Google Finance price quote function

Post by 4fitness » Sun Feb 11, 2018 7:39 am

[/quote]
Anyone have an approach that works with EXCEL?
[/quote]

I’m still in Excel as well and have just been doing manual entry. A bit of a pain, but I find myself checking my portfolio less often.

If someone is aware of a way for auto updates in Excel that would speed up the process. Perhaps it’s time to move to Google Sheets for me.

User avatar
Epsilon Delta
Posts: 7266
Joined: Thu Apr 28, 2011 7:00 pm

Re: Replacement for Google Finance price quote function

Post by Epsilon Delta » Sun Feb 11, 2018 1:46 pm

From my experience programming and in quality control I would say these are the type of things that really should be encapsulated in a visual basic function (or google app srcript).

That way the main spreadsheet contains many lines like MYGETQUOTE( "VTI" ) and you can more easily change the web scraping as needed by editing the MYGETQUOTE() function. That allows you to avoid excessive one liners and add some fine comments.

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

Re: Replacing Google Finance price quote function

Post by munemaker » Mon Feb 12, 2018 12:25 am

The529guy wrote:
Sun Feb 04, 2018 12:43 am

Update: Try the WSJ's quote tool. Here's the result for VTSAX. I'm able to access the Total Returns table like this:

Code: Select all

=IMPORTHTML("http://quotes.wsj.com/mutualfund/VTSAX?mod=searchresults_companyquotes", "table", 3)
Using the INDEX function described in this thread, you can extract the YTD, 1YR, 3YR, 5YR, and 10YR total returns for the fund.
I would like to use this but instead of embedding the ticker symbol in the formula, I would like to have it pass the ticker from a cell, say A3. Anyone know how to do that?

maineminder
Posts: 21
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacing Google Finance price quote function

Post by maineminder » Mon Feb 12, 2018 9:41 am

munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.

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

Re: Replacement for Google Finance price quote function

Post by The529guy » Mon Feb 12, 2018 10:02 am

Epsilon Delta wrote:
Sun Feb 11, 2018 1:46 pm
From my experience programming and in quality control I would say these are the type of things that really should be encapsulated in a visual basic function (or google app srcript).
Sounds great. Can you show us how?

afatcat
Posts: 35
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat » Mon Feb 12, 2018 10:24 am

munemaker you could also use the method jasg mentioned above using &A1&.

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Tue Feb 13, 2018 1:07 am

afatcat wrote:
Mon Feb 12, 2018 10:24 am
munemaker you could also use the method jasg mentioned above using &A1&.
Could not get this to work, but I did get the CONCATENATE method to work; maybe I just had a syntax error somewhere.

Thanks

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

Re: Replacing Google Finance price quote function

Post by munemaker » Tue Feb 13, 2018 1:14 am

maineminder wrote:
Mon Feb 12, 2018 9:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.

Thanks

NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST

User avatar
in_reality
Posts: 4529
Joined: Fri Jul 12, 2013 6:13 am

Re: Replacing Google Finance price quote function

Post by in_reality » Tue Feb 13, 2018 4:30 am

munemaker wrote:
Tue Feb 13, 2018 1:14 am
maineminder wrote:
Mon Feb 12, 2018 9:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.

Thanks

NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
I don’t think its internet or processor speed.

It seems related to google’s whim or ability. Sheets with many imports drag from time to time.

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Tue Feb 13, 2018 9:10 am

deleted
Last edited by munemaker on Wed Feb 14, 2018 12:51 am, edited 1 time in total.

User avatar
alpenglow
Posts: 585
Joined: Tue May 31, 2011 12:02 pm

Re: Replacement for Google Finance price quote function

Post by alpenglow » Tue Feb 13, 2018 4:28 pm

Thanks 529guy - you're the man. I was getting very annoying with the delayed update!

Is there any way to scrape data from this site?

https://www.nysdcp.com/iApp/rsc/netAssetValue.x

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

Re: Replacing Google Finance price quote function

Post by libralibra » Wed Feb 14, 2018 1:58 am

munemaker wrote:
Tue Feb 13, 2018 1:14 am
maineminder wrote:
Mon Feb 12, 2018 9:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.

Thanks

NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
You are pulling in 60 webpages to render your one sheet. For each page you pull, you are just using a single number and tossing the rest. The slowness is from all this downloading and is a really inefficient use of resources, including the website. They don't like this and often have (legally binding) Terms of Service clauses that prohibit bulk crawling/scraping of their services.

Maybe you can just use the scraping for some of your fields and use GOOGLEFINANCE() calls for the rest, i.e. the ones that aren't as time sensitive (like past performance), or update quickly (like regular stocks). API help is here https://support.google.com/docs/answer/3093281?hl=en

Halicar
Posts: 69
Joined: Tue Oct 31, 2017 8:41 am
Location: Midwest

Re: Replacement for Google Finance price quote function

Post by Halicar » Wed Feb 14, 2018 7:15 am

One Ping wrote:
Wed Feb 07, 2018 12:31 pm
Anyone have an approach that works with EXCEL?

This function is apparently being added to Excel soon.

viewtopic.php?f=11&t=240763#p3767113

Edit: fixed quote and user ping.

afatcat
Posts: 35
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat » Wed Feb 14, 2018 9:58 am

If you use multiple values from the same imported table, one way to reduce the web calls might be to import the table to a separate page and pick the values you want from the individual table cells on that page.

To import table to separate page:

Code: Select all

=IMPORTHTML("http://quotes.wsj.com/mutualfund/"&A1&"?mod=searchresults_companyquotes", "table", 3)

User avatar
One Ping
Posts: 334
Joined: Thu Sep 24, 2015 4:53 pm

Re: Replacement for Google Finance price quote function

Post by One Ping » Wed Feb 14, 2018 12:22 pm

Halicar wrote:
Wed Feb 14, 2018 7:15 am
One Ping wrote:
Wed Feb 07, 2018 12:31 pm
Anyone have an approach that works with EXCEL?
This function is apparently being added to Excel soon.
Yes. Looking forward to it, I'm just not too sure how soon "soon" is ... :?
"Re-verify our range to target ... one ping only."

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

Re: Replacing Google Finance price quote function

Post by munemaker » Sat Feb 17, 2018 7:14 pm

libralibra wrote:
Wed Feb 14, 2018 1:58 am
munemaker wrote:
Tue Feb 13, 2018 1:14 am
maineminder wrote:
Mon Feb 12, 2018 9:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.

Thanks

NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
You are pulling in 60 webpages to render your one sheet. For each page you pull, you are just using a single number and tossing the rest. The slowness is from all this downloading and is a really inefficient use of resources, including the website. They don't like this and often have (legally binding) Terms of Service clauses that prohibit bulk crawling/scraping of their services.

Maybe you can just use the scraping for some of your fields and use GOOGLEFINANCE() calls for the rest, i.e. the ones that aren't as time sensitive (like past performance), or update quickly (like regular stocks). API help is here https://support.google.com/docs/answer/3093281?hl=en
Thanks for your comments. I am new to this and didn't even know what "scraping" was two weeks ago. I have my SS working now, and next I will work to minimize the data loads on the servers. Interestingly, there are multiple descriptions online of how the "importhtml" instruction updates. One place referred to "Spreadsheet Settings/Calculations" for update settings, which turns out not to be true. Another place said "importhtml" does not update automatically and you need to write a script and set a trigger, which I did. Then I found out "importhtml" was automatically updating every hour, and that's how it works by design, so I can delete the script. Not only that, but the SS updates even if the file is closed which is pretty cool. Now that my SS is complete, the update time does not seem so long or inconvenient. I don't really need an hourly update...just once a day after the mutual fund prices are reported would be fine. Doesn't seem like a way to turn off the hourly updates though.

I am learning a lot with this. I used to think Sheets was a lightweight program but am finding it has a lot of capability. Going to look for some online training for the more advanced features.

Bimmer
Posts: 125
Joined: Tue Mar 06, 2007 10:33 pm

Re: Replacement for Google Finance price quote function

Post by Bimmer » Tue Feb 20, 2018 12:53 am

Thank you The529Guy - the delayed quotes were a huge frustration for me, and your formula works great. I was even able to play with the numbers at the end of the string to get the day's percent change into another column so I can track the daily fluctuation of my portfolio. Finally may leave Morningstar!
____________________________ | Bimmer | ____________________________

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

Re: Replacement for Google Finance price quote function

Post by Hiwatter » Mon Feb 26, 2018 3:09 pm

Hmmm, my Bigcharts formulas (in google sheets) are not working today... anyone else having issues?

Edit... nevermind, looks to be back up now.

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

Re: Replacement for Google Finance price quote function

Post by munemaker » Mon Feb 26, 2018 9:28 pm

Hiwatter wrote:
Mon Feb 26, 2018 3:09 pm
Hmmm, my Bigcharts formulas (in google sheets) are not working today... anyone else having issues?

Edit... nevermind, looks to be back up now.
Mine had similar issues but eventually cam through.

User avatar
sperry8
Posts: 1529
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 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!
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

Post Reply