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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Replacement for Google Finance price quote function

Post by The529guy »

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 6:25 pm, edited 1 time in total.
tesuzuki2002
Posts: 1433
Joined: Fri Dec 11, 2015 11:40 am

Re: Replacing Google Finance price quote function

Post by tesuzuki2002 »

The529guy wrote: Fri Feb 02, 2018 6: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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacing Google Finance price quote function

Post by The529guy »

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: 1338
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman »

The529guy wrote: Fri Feb 02, 2018 6: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: 103
Joined: Thu Oct 20, 2011 2:38 pm

Re: Replacement for Google Finance price quote function

Post by BH13 »

The529guy wrote: Fri Feb 02, 2018 6: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: 1295
Joined: Wed Feb 17, 2010 9:58 pm

Re: Replacement for Google Finance price quote function

Post by cheesepep »

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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacing Google Finance price quote function

Post by The529guy »

venkman wrote: Fri Feb 02, 2018 8: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 9:59 am, edited 2 times in total.
User avatar
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy »

BH13 wrote: Fri Feb 02, 2018 8: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 10:23 am, edited 1 time in total.
User avatar
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy »

BH13 wrote: Fri Feb 02, 2018 8: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 10:22 am, edited 1 time in total.
User avatar
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy »

p.s. MarketWatch provides a quote for TIAA Real Estate Account using symbol QREARX.
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by moshe »

The529guy wrote: Fri Feb 02, 2018 6: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: 3466
Joined: Tue Feb 20, 2007 4:43 pm
Location: Central Florida - on the grown up side of 85

Re: Replacement for Google Finance price quote function

Post by Dale_G »

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: 68
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat »

Thanks for this. I was missing getting the nightly updates after they went away.
venkman
Posts: 1338
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman »

The529guy wrote: Sat Feb 03, 2018 8:46 am
venkman wrote: Fri Feb 02, 2018 8: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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacing Google Finance price quote function

Post by The529guy »

venkman wrote: Sat Feb 03, 2018 6:59 pm
The529guy wrote: Sat Feb 03, 2018 8:46 am
venkman wrote: Fri Feb 02, 2018 8: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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy »

Dale_G wrote: Sat Feb 03, 2018 4: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
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacing Google Finance price quote function

Post by The529guy »

The529guy wrote: Sat Feb 03, 2018 8:46 am
venkman wrote: Fri Feb 02, 2018 8: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: 191
Joined: Sat Jul 27, 2013 6:39 am

Re: Replacement for Google Finance price quote function

Post by Emilyjane »

Thanks, that is a wonderful replacement!
"Real knowledge is to know the extent of one's ignorance", Confucius
Grasshopper
Posts: 1209
Joined: Sat Oct 09, 2010 3:52 pm

Re: Replacement for Google Finance price quote function

Post by Grasshopper »

Thanks, the529guy.
venkman
Posts: 1338
Joined: Tue Mar 14, 2017 10:33 pm

Re: Replacing Google Finance price quote function

Post by venkman »

The529guy wrote: Sat Feb 03, 2018 11:43 pm 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: 1087
Joined: Thu Sep 24, 2015 4:53 pm

Re: Replacement for Google Finance price quote function

Post by One Ping »

The529guy wrote: Fri Feb 02, 2018 6: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: 2562
Joined: Mon Feb 19, 2007 4:48 pm

Re: Replacement for Google Finance price quote function

Post by oneleaf »

This is great. I have had issues with the slow GoogleFinance update.
jasg
Posts: 96
Joined: Wed May 08, 2013 7:10 pm

Re: Replacement for Google Finance price quote function

Post by jasg »

The529guy wrote: Sat Feb 03, 2018 9:31 am
BH13 wrote: Fri Feb 02, 2018 8: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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker »

The529guy wrote: Sat Feb 03, 2018 9:31 am
BH13 wrote: Fri Feb 02, 2018 8: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: 717
Joined: Sun Aug 18, 2013 12:21 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by bostondan »

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: 148
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacement for Google Finance price quote function

Post by maineminder »

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: 717
Joined: Sun Aug 18, 2013 12:21 pm
Location: Boston, MA

Re: Replacement for Google Finance price quote function

Post by bostondan »

maineminder wrote: Fri Feb 09, 2018 11:05 am 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: 148
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacement for Google Finance price quote function

Post by maineminder »

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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker »

munemaker wrote: Thu Feb 08, 2018 7:00 pm
The529guy wrote: Sat Feb 03, 2018 9:31 am
BH13 wrote: Fri Feb 02, 2018 8: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: 699
Joined: Wed Jul 15, 2015 6:08 pm

Re: Replacement for Google Finance price quote function

Post by AtlasShrugged? »

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: 27
Joined: Fri Feb 17, 2017 8:29 pm

Re: Replacement for Google Finance price quote function

Post by 4fitness »

[/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: 8090
Joined: Thu Apr 28, 2011 7:00 pm

Re: Replacement for Google Finance price quote function

Post by Epsilon Delta »

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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacing Google Finance price quote function

Post by munemaker »

The529guy wrote: Sat Feb 03, 2018 11:43 pm
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: 148
Joined: Sat Sep 24, 2011 9:48 am

Re: Replacing Google Finance price quote function

Post by maineminder »

munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
User avatar
Topic Author
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: Replacement for Google Finance price quote function

Post by The529guy »

Epsilon Delta wrote: Sun Feb 11, 2018 12: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: 68
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat »

munemaker you could also use the method jasg mentioned above using &A1&.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker »

afatcat wrote: Mon Feb 12, 2018 9: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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacing Google Finance price quote function

Post by munemaker »

maineminder wrote: Mon Feb 12, 2018 8: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 »

munemaker wrote: Tue Feb 13, 2018 12:14 am
maineminder wrote: Mon Feb 12, 2018 8: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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker »

deleted
Last edited by munemaker on Tue Feb 13, 2018 11:51 pm, edited 1 time in total.
User avatar
alpenglow
Posts: 1806
Joined: Tue May 31, 2011 12:02 pm

Re: Replacement for Google Finance price quote function

Post by alpenglow »

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: 345
Joined: Sat Jul 30, 2011 2:01 pm

Re: Replacing Google Finance price quote function

Post by libralibra »

post removed
Last edited by libralibra on Sun May 26, 2019 9:50 pm, edited 1 time in total.
User avatar
Halicar
Posts: 501
Joined: Tue Oct 31, 2017 8:41 am
Location: Midwest

Re: Replacement for Google Finance price quote function

Post by Halicar »

One Ping wrote: Wed Feb 07, 2018 11:31 am 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: 68
Joined: Sat Mar 28, 2015 9:17 am

Re: Replacement for Google Finance price quote function

Post by afatcat »

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: 1087
Joined: Thu Sep 24, 2015 4:53 pm

Re: Replacement for Google Finance price quote function

Post by One Ping »

Halicar wrote: Wed Feb 14, 2018 6:15 am
One Ping wrote: Wed Feb 07, 2018 11:31 am 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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacing Google Finance price quote function

Post by munemaker »

libralibra wrote: Wed Feb 14, 2018 12:58 am
munemaker wrote: Tue Feb 13, 2018 12:14 am
maineminder wrote: Mon Feb 12, 2018 8: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: 215
Joined: Tue Mar 06, 2007 9:33 pm

Re: Replacement for Google Finance price quote function

Post by Bimmer »

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 | ____________________________
User avatar
Hiwatter
Posts: 120
Joined: Tue Aug 16, 2016 3:43 pm

Re: Replacement for Google Finance price quote function

Post by Hiwatter »

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: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Replacement for Google Finance price quote function

Post by munemaker »

Hiwatter wrote: Mon Feb 26, 2018 2: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: 3065
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

Re: Replacement for Google Finance price quote function

Post by sperry8 »

The529guy wrote: Fri Feb 02, 2018 6: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!
BH Contests: 23 #89 of 607 | 22 #512 of 674 | 21 #66 of 636 |20 #253/664 |19 #233/645 |18 #150/493 |17 #516/647 |16 #121/610 |15 #18/552 |14 #225/503 |13 #383/433 |12 #366/410 |11 #113/369 |10 #53/282
Post Reply