Google sheets not pulling stock prices
Google sheets not pulling stock prices
I've been using Google sheets to get prices since the Yahoo functions stopped working. Noticed a problem yesterday that it no longer pulls prices for selected symbols (BIV,EDV,VT, etc). Still works ok for VTI, LQD, etc. Its pretty simple - the Google finance functions with "price" parameter. Instead of the price it returns "#N/A"
As an example: =(Googlefinance(B56),"price") where cell B56 contains BIV
Anyone else notice this problem?
As an example: =(Googlefinance(B56),"price") where cell B56 contains BIV
Anyone else notice this problem?
Re: Google sheets not pulling stock prices
Yes, seen the same issue yesterday/today.
Re: Google sheets not pulling stock prices
I'm seeing the same thing this morning with BND
Re: Google sheets not pulling stock prices
It seems to be not working on some subset of tickers. FUSVX and IVV work fine, as do individual stocks (GOOG and AMZN), but BND doesn't. I would say it is only Vanguard stuff that fails, but VTI works fine.
- ivyhedge
- Posts: 258
- Joined: Wed Apr 10, 2013 12:59 pm
- Location: A Town of Beans with only one hill remaining
Re: Google sheets not pulling stock prices
No quotations for Vanguard: VSS, VIOV; none for non Vanguard: FBT, MLPI, RYT, SUB, PUTW.
All others fine, including ETFs and individual equities.
Time: 1005hrs.
All others fine, including ETFs and individual equities.
Time: 1005hrs.
Polymath.
-
- Posts: 61
- Joined: Thu Jan 11, 2018 11:56 am
Re: Google sheets not pulling stock prices
I didn't even know this was a feature on Google Sheets!
Do you mind sharing a blank template here so I can play around with it?
Do you mind sharing a blank template here so I can play around with it?
Re: Google sheets not pulling stock prices
Yep, I see it too. Its very frustrating.
Re: Google sheets not pulling stock prices
Pulled prices this morning. No issues. I pulled yesterday's closing prices. Looks like from the posts the issue is with ETFs. Mine are mutual funds, except for VTIP.
-
- Posts: 637
- Joined: Wed Dec 19, 2012 6:38 am
Re: Google sheets not pulling stock prices
It's not pulling SCHA nor VSS for me.
Sounds like it is a widespread problem, hopefully the delicate geniuses over there will fix it soon.
Sounds like it is a widespread problem, hopefully the delicate geniuses over there will fix it soon.
Re: Google sheets not pulling stock prices
No, it is for various investments, not just ETFs. I have several tickers it is not pulling. Very frustrating, especially since I use my Google Doc almost as my brokerage account.
-
- Posts: 637
- Joined: Wed Dec 19, 2012 6:38 am
Re: Google sheets not pulling stock prices
Here is the function I use to pull quotes and calculate current values in real time, using VTI as an example..
=googlefinance("vti","price")*M9
..where M9 is the cell location with the number of shares
=googlefinance("vti","price")*M9
..where M9 is the cell location with the number of shares
-
- Posts: 12277
- Joined: Wed Jan 11, 2017 7:05 pm
Re: Google sheets not pulling stock prices
The Hedge Fund Contest spreadsheet was broken yesterday. Seems to be fixed now.
Re: Google sheets not pulling stock prices
Having same issues as well.
Not pulling quotes for VSS, SCHE, IEMG, VEU, BND. Hopefully it will correct itself.
Not pulling quotes for VSS, SCHE, IEMG, VEU, BND. Hopefully it will correct itself.
Re: Google sheets not pulling stock prices
I love google sheets but the finance function isn't cutting it these days. Try this solution!
viewtopic.php?t=240332
viewtopic.php?t=240332
Re: Google sheets not pulling stock prices
If you head to the Google issue tracker for this issue, you can click on the star in the upper left to "vote" for this issue.
You will also get updates when there are changes/posts.
https://issuetracker.google.com/u/1/iss ... 3135?pli=1
You will also get updates when there are changes/posts.
https://issuetracker.google.com/u/1/iss ... 3135?pli=1
Re: Google sheets not pulling stock prices
Not working for me either. VOE isn't pulling but many Vanguard ETFs are (VTV and VBR are working). A variety of others aren't working too - and yet some others are. Let's get on the ball Google!
Here is another place to complain: https://productforums.google.com/forum/ ... iK_9xBBAAJ
Here is another place to complain: https://productforums.google.com/forum/ ... iK_9xBBAAJ
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
- GreatOdinsRaven
- Posts: 572
- Joined: Thu Apr 23, 2015 8:47 pm
google finance no longer able to query some tickers in Google Sheets
[Thread merged into here, see below (next page). --admin LadyGeek]
I've been using Google Sheets for some time to monitor my portfolio. Approximately two days ago, it stopped being able to query a few of the tickers that I own (a minority of them). The vast majority of my tickers are still working.
This is what used to work in google sheets:
=(Googlefinance("VOO", "closeYest"))
This is the error I'm receiving in google sheets:
Error
When evaluating GOOGLEFINANCE, the query for the symbol: 'VOO' returned no data.
I'm getting the same errors for VEA, VEU, MTUM, VMMXX, VSS and IEMG.
If you substitute any other ticker such as VTI, brk.b etc it still works.
Any suggestions on what's wrong and how I can contact google to let them know about this problem?
Thank you,
GOR
I've been using Google Sheets for some time to monitor my portfolio. Approximately two days ago, it stopped being able to query a few of the tickers that I own (a minority of them). The vast majority of my tickers are still working.
This is what used to work in google sheets:
=(Googlefinance("VOO", "closeYest"))
This is the error I'm receiving in google sheets:
Error
When evaluating GOOGLEFINANCE, the query for the symbol: 'VOO' returned no data.
I'm getting the same errors for VEA, VEU, MTUM, VMMXX, VSS and IEMG.
If you substitute any other ticker such as VTI, brk.b etc it still works.
Any suggestions on what's wrong and how I can contact google to let them know about this problem?
Thank you,
GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. |
|
"Winter is coming." Lord Eddard Stark.
Re: google finance no longer able to query some tickers in Google Sheets
Already a thread on thisGreatOdinsRaven wrote: ↑Tue Mar 27, 2018 6:29 pm I've been using Google Sheets for some time to monitor my portfolio. Approximately two days ago, it stopped being able to query a few of the tickers that I own (a minority of them). The vast majority of my tickers are still working.
This is what used to work in google sheets:
=(Googlefinance("VOO", "closeYest"))
This is the error I'm receiving in google sheets:
Error
When evaluating GOOGLEFINANCE, the query for the symbol: 'VOO' returned no data.
I'm getting the same errors for VEA, VEU, MTUM, VMMXX, VSS and IEMG.
If you substitute any other ticker such as VTI, brk.b etc it still works.
Any suggestions on what's wrong and how I can contact google to let them know about this problem?
Thank you,
GOR
viewtopic.php?f=2&t=245411&newpost=3850 ... ead#unread
- GreatOdinsRaven
- Posts: 572
- Joined: Thu Apr 23, 2015 8:47 pm
Re: google finance no longer able to query some tickers in Google Sheets
Thanks. I did a google search before posting and unfortunately the pertinent thread didn't appear.
Maybe lady geek will merge my post with the current thread.
Maybe lady geek will merge my post with the current thread.
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. |
|
"Winter is coming." Lord Eddard Stark.
-
- Posts: 849
- Joined: Sun Jun 24, 2012 8:31 pm
Re: Google sheets not pulling stock prices
It started working for me now after being broken for two days.
Re: Google sheets not pulling stock prices
Just started working for me also.
-
- Posts: 12073
- Joined: Fri Sep 18, 2009 1:10 am
Re: Google sheets not pulling stock prices
Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:37 pm, edited 1 time in total.
Re: Google sheets not pulling stock prices
Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Re: Google sheets not pulling stock prices
Using Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))
Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
Code: Select all
=IFERROR(IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']"), GOOGLEFINANCE(A2))
Now if someone can help convert that Morningstar call into appscript code, I'd be very thankful!
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
Re: Google sheets not pulling stock prices
Is there a way to pull in 7 or 30 day SEC Yield into a google spreadsheet? For example, it's shown here for Vanguard Prime MM (VMMXX) as 1.78% (a/o today) https://personal.vanguard.com/us/funds/ ... irect=true
I perused this (https://support.google.com/docs/answer/3093281?hl=en) but only see 12 mo yield "yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
Perhaps via BigCharts or elsewhere?
I'm trying to pull in Yield from Vanguard Prime MM (VMMXX) and Vanguard Municipal MM (VMSXX) so I can monitor them and ensure I'm always in the right one.
I perused this (https://support.google.com/docs/answer/3093281?hl=en) but only see 12 mo yield "yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
Perhaps via BigCharts or elsewhere?
I'm trying to pull in Yield from Vanguard Prime MM (VMMXX) and Vanguard Municipal MM (VMSXX) so I can monitor them and ensure I'm always in the right one.
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
- GreatOdinsRaven
- Posts: 572
- Joined: Thu Apr 23, 2015 8:47 pm
Re: Google sheets not pulling stock prices
I've had similar issues with google sheets not being able to pull quotes for ETFs and mutual funds over the past year and a half or so. Usually, I'll report the issue via the Google Issue Tracker and it will be repaired within a week. Most recently (about two weeks ago) I noticed that Google Sheets/Google Finance was no longer pulling the data for Vanguard Prime Money Market (VMMXX) and Vanguard Federal Money Market (VMFXX). I reported this issue, but they have not repaired it and it's wrecking my spreadsheet. Does anyone know how to escalate this issue or report it in a way that will receive attention? I keep receiving email notifications from Google that issues reported long after I reported my problem have been solved. Here's the formula I'm using:
I'd prefer not to abandon Google Finance for this issue, but in the event that I have to do so, does anyone have an alternative and an explanation of how would I incorporate it into my spreadsheet?
Thank you
GOR
Code: Select all
=Shares!$E$23*(Googlefinance("VMMXX", "closeYest"))
Thank you
GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. |
|
"Winter is coming." Lord Eddard Stark.
- GreatOdinsRaven
- Posts: 572
- Joined: Thu Apr 23, 2015 8:47 pm
Re: Google sheets not pulling stock prices
For example, I use my Google Sheets spreadsheet to scrape expense ratio data from Morningstar for all of my funds and then I calculate my portfolio weighted expense ratio. I can't recall how I ever figured out how to do this...GreatOdinsRaven wrote: ↑Sat Jun 30, 2018 9:04 am I've had similar issues with google sheets not being able to pull quotes for ETFs and mutual funds over the past year and a half or so. Usually, I'll report the issue via the Google Issue Tracker and it will be repaired within a week. Most recently (about two weeks ago) I noticed that Google Sheets/Google Finance was no longer pulling the data for Vanguard Prime Money Market (VMMXX) and Vanguard Federal Money Market (VMFXX). I reported this issue, but they have not repaired it and it's wrecking my spreadsheet. Does anyone know how to escalate this issue or report it in a way that will receive attention? I keep receiving email notifications from Google that issues reported long after I reported my problem have been solved. Here's the formula I'm using:
I'd prefer not to abandon Google Finance for this issue, but in the event that I have to do so, does anyone have an alternative and an explanation of how would I incorporate it into my spreadsheet?Code: Select all
=Shares!$E$23*(Googlefinance("VMMXX", "closeYest"))
Thank you
GOR
I'm using the following formula:
Code: Select all
=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", C23), "table", 1),1,13), " "), 1, 2)*100
Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. |
|
"Winter is coming." Lord Eddard Stark.
Re: Google sheets not pulling stock prices
The URL to the data you need is:GreatOdinsRaven wrote: ↑Sat Jun 30, 2018 9:30 am Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?
Code: Select all
http://quotes.morningstar.com/fund/c-header?t=VMMXX
- GreatOdinsRaven
- Posts: 572
- Joined: Thu Apr 23, 2015 8:47 pm
Re: Google sheets not pulling stock prices
For the benefit of anyone reading this in the future. I found a work-around that uses M* instead of Google Finance:BHUser27 wrote: ↑Sun Jul 01, 2018 8:11 amThe URL to the data you need is:GreatOdinsRaven wrote: ↑Sat Jun 30, 2018 9:30 am Is there a way I can scrape the M* page for the price of VMMXX and use that instead of Google finance? And if so, what would the formula be?However, I haven't figured out how to parse out the Nav Price using index & split functions.Code: Select all
http://quotes.morningstar.com/fund/c-header?t=VMMXX
=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", "VMMXX"), "table", 1),1,1), " "), 1, 2)
The "1" in bold, italics and underlined is the key. This is the NAV price as of the close of the market the day prior. For a money market fund the value should be 1.
My formula pulls the number of shares and multiplies it by the NAV. The actual formula is:
Code: Select all
=Shares!$E$23*(INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", "VMMXX"), "table", 1),1,1), " "), 1, 2))
In a prior post, above, the value I was using was "13". "13" pulls the expense ratio.
GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. |
|
"Winter is coming." Lord Eddard Stark.
Re: Google sheets not pulling stock prices
My portfolio spreadsheet uses googlefinance() to pull stock prices, but for some reason it's now showing BND as Purpose Tactical Inv Grade Bond ETF, which is actually BND-T. Very frustrating. Anyone else seeing this?
- typical.investor
- Posts: 5263
- Joined: Mon Jun 11, 2018 3:17 am
Re: Google sheets not pulling stock prices
Did Total Bond switch over to NASDAQ? There were posts recently saying so.
If BND was part of that, I'd try:
Code: Select all
=GOOGLEFINANCE("NASDAQ:BND","price")
- Bylo Selhi
- Posts: 1310
- Joined: Mon Feb 19, 2007 9:40 pm
- Location: Great White North
- Contact:
Re: Google sheets not pulling stock prices
Have you experimented with Morningstar using the IMPORTXML function as @walletless describes upthread in viewtopic.php?p=3875022#p3875022 ?
Google's quotes source stopped working for me about a month ago with some Canadian securities. I was able to get those quotes from M* instead using that technique.
- typical.investor
- Posts: 5263
- Joined: Mon Jun 11, 2018 3:17 am
Re: Google sheets not pulling stock prices
It's working now.typical.investor wrote: ↑Sat Jul 28, 2018 2:38 amDid Total Bond switch over to NASDAQ? There were posts recently saying so.
If BND was part of that, I'd try:
It's not working now so maybe it's not correct or maybe the listing isn't coming through yet.Code: Select all
=GOOGLEFINANCE("NASDAQ:BND","price")
79.01
Re: Google sheets not pulling stock prices
Can you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?walletless wrote: ↑Wed Apr 11, 2018 2:47 amUsing Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))...Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
Re: Google sheets not pulling stock prices
sure, i am out today and tomm. will try on wed.munemaker wrote: ↑Mon Jul 30, 2018 9:51 amCan you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?walletless wrote: ↑Wed Apr 11, 2018 2:47 amUsing Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))...Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
Re: Google sheets not pulling stock prices
Thanks! Much appreciated.
Re: Google sheets not pulling stock prices
Try this for daily percent change:munemaker wrote: ↑Mon Jul 30, 2018 9:51 amCan you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?walletless wrote: ↑Wed Apr 11, 2018 2:47 amUsing Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))...Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
(AGE minus 23%) Bonds | 5% REITs | Balance 80% US (75/25 TSM/SCV) + 20% International (80/20 Developed/Emerging)
Re: Google sheets not pulling stock prices
I noticed yesterday that google finance no longer shows the s&p 500 or s&p 500 total return indices. Anyone else notice this?
Re: Google sheets not pulling stock prices
Wow! Thank you for doing this! I appreciate it.walletless wrote: ↑Thu Aug 02, 2018 2:44 amTry this for daily percent change:munemaker wrote: ↑Mon Jul 30, 2018 9:51 amCan you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?walletless wrote: ↑Wed Apr 11, 2018 2:47 amUsing Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))...Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
If your ticker symbol (VBTIX in this case) is in cell A2, you can do:Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
Re: Google sheets not pulling stock prices
My Google sheet is back to normal. BND is now being shown correctly as Vanguard Total Bond Market ETF.Bylo Selhi wrote: ↑Sat Jul 28, 2018 9:41 amHave you experimented with Morningstar using the IMPORTXML function as @walletless describes upthread in viewtopic.php?p=3875022#p3875022 ?
Google's quotes source stopped working for me about a month ago with some Canadian securities. I was able to get those quotes from M* instead using that technique.
- Bylo Selhi
- Posts: 1310
- Joined: Mon Feb 19, 2007 9:40 pm
- Location: Great White North
- Contact:
Re: Google sheets not pulling stock prices
Here's Google's new documentation/help for their GOOGLEFINANCE function. This may help explain why some exchanges and security symbols are no longer recognized. It also provides a nice summary of all the security attributes that this function can report.
-
- Posts: 12073
- Joined: Fri Sep 18, 2009 1:10 am
Re: Google sheets not pulling stock prices
Deleted
Last edited by letsgobobby on Tue Apr 23, 2019 8:54 pm, edited 1 time in total.
Re: Google sheets not pulling stock prices
Do you know how to use your morningstar code to pull in 1 year returns for ETFs?walletless wrote: ↑Thu Aug 02, 2018 2:44 amTry this for daily percent change:munemaker wrote: ↑Mon Jul 30, 2018 9:51 amCan you help me with a similar formula to get the either the price change (either $ or % would do) or yesterday's closing price from Morningstar?walletless wrote: ↑Wed Apr 11, 2018 2:47 amUsing Morningstar.com:codmaxkor wrote: ↑Tue Apr 10, 2018 8:59 pm Has anyone found an alternative to:
=VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advcha ... ttype=Fund"), "table"),3,1)),7))...Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
If your ticker symbol (VBTIX in this case) is in cell A2, you can do:Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=VBTIX", "//div[@vkey='DayChange']/span[1]")
Code: Select all
=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//div[@vkey='DayChange']/span[1]")
This code works for mutual funds (B10 represents a cell with the fund name inside it), but doesn't work for ETFs.
Code: Select all
=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:",B10,"&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&alig n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[6]")
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
- changingtimes
- Posts: 483
- Joined: Mon Jul 24, 2017 9:28 am
Re: Google sheets not pulling stock prices
Just wanted to say thanks for this thread. Google stopping the 6 pm NAV updates for mutual funds has driven me nuts.
I went with this for the prices:
And I used this for the last reported date:
I went with this for the prices:
Code: Select all
=VALUE(Right((Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&B3&"", "table"),3,1)),7))
Code: Select all
=Index(ImportHtml("http://bigcharts.marketwatch.com/quickchart/quickchart.asp?symb="&B3&"", "table"),1,6)
-
- Posts: 77
- Joined: Sat Nov 28, 2015 10:50 am
Re: Google sheets not pulling stock prices
I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?
Thanks in advance for any suggestions.
Thanks in advance for any suggestions.
Re: Google sheets not pulling stock prices
I am scraping the fund prices from Marketwatch and today most of the Fidelity fund prices I import did not load. I am using Google Finance for individual stock prices and they all loaded correctly. Overall, the Marketwatch imports have been working well until today (maybe a small glitch yesterday), so I am not overly concerned YET.highdesert wrote: ↑Mon Dec 24, 2018 11:36 am I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?
Thanks in advance for any suggestions.
Sorry I do not have any suggestions. I am not going to put any time into a fix for a couple days, to see if it rights itself.
UPDATE: At 8:15 pm, I noticed that the Fidelity Funds that I actually own had updated. I track others that I don't own, and many of those had still not updated. At least the ones I need to calculate my totals updated and I am ok with that.
Re: Google sheets not pulling stock prices
Everything on mine is working fine today. No problems.munemaker wrote: ↑Mon Dec 24, 2018 6:38 pmI am scraping the fund prices from Marketwatch and today most of the Fidelity fund prices I import did not load. I am using Google Finance for individual stock prices and they all loaded correctly. Overall, the Marketwatch imports have been working well until today (maybe a small glitch yesterday), so I am not overly concerned YET.highdesert wrote: ↑Mon Dec 24, 2018 11:36 am I'm having some trouble with this, but only for certain Fidelity funds. For Vanguard ETFs and funds, both the googlefinance function and the Morningstar and Marketwatch links still work. The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX). The http://performance.morningstar.com/ form will auto-fill the names of the Fidelity funds, but only returns prices for certain ones. This seems to be a recent change, it was working for me a couple of months ago (October?). Even the Google Finance web page doesn't return price info for some of these funds. Is anyone else seeing this, or is there another workaround?
Thanks in advance for any suggestions.
Sorry I do not have any suggestions. I am not going to put any time into a fix for a couple days, to see if it rights itself.
UPDATE: At 8:15 pm, I noticed that the Fidelity Funds that I actually own had updated. I track others that I don't own, and many of those had still not updated. At least the ones I need to calculate my totals updated and I am ok with that.
Re: Google sheets not pulling stock prices
FUSVX doesn't trade any more. The new ticker symbol is FXAIX. You can find out the new ticker symbol for the ones that consolidated into others by entering the old symbol to the search box on the top right at fidelity.com.highdesert wrote: ↑Mon Dec 24, 2018 11:36 am The Morningstar and Marketwatch links also work for some Fidelity funds (e.g., FCNTX) but not others (e.g., FUSVX).
Harry Sit has left the forums.
-
- Posts: 77
- Joined: Sat Nov 28, 2015 10:50 am
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: Google sheets not pulling stock prices
Update to this older thread. I couldn't get my new Vanguard International Small Cap Admiral ticker VFSAX to be recognized by GoogleFinance or BigCharts.Marketwatch because the fund is so new, so I figured out another way to get realtime prices with the ImportXML function from the Bloomberg stock page:
You'll probably be referencing a ticker symbol in another cell, so suppose that cell is A2, then your formula is:
I tried to do this from Morningstar but I couldn't get any XPATH to work, but I could with Bloomberg.
I must say that I'm wary of depending on finding the NAV from a class with such a strange name (priceText__1853e8a5) (at first I thought it was dynamically generated to deter webscrapers) but it has been working for 3+ days now, both for mutual funds and ETFs.
An even more advanced tip is that my "Price" cell in my spreadsheet tries 3 different sites: first BigCharts.MarketWatch, then Bloomberg, and then GoogleFinance. If BigCharts.Marketwatch works, take it; if that fails, try Bloomberg; if that fails, try GoogleFinance. The syntax to do this is with nested IFERROR functions. Assuming the Ticker is in cell A2:
Code: Select all
=importxml("https://www.bloomberg.com/quote/VFSAX:US", "//span[@class='priceText__1853e8a5']")
Code: Select all
=importxml("https://www.bloomberg.com/quote/" & A2 & ":US", "//span[@class='priceText__1853e8a5']")
I must say that I'm wary of depending on finding the NAV from a class with such a strange name (priceText__1853e8a5) (at first I thought it was dynamically generated to deter webscrapers) but it has been working for 3+ days now, both for mutual funds and ETFs.
An even more advanced tip is that my "Price" cell in my spreadsheet tries 3 different sites: first BigCharts.MarketWatch, then Bloomberg, and then GoogleFinance. If BigCharts.Marketwatch works, take it; if that fails, try Bloomberg; if that fails, try GoogleFinance. The syntax to do this is with nested IFERROR functions. Assuming the Ticker is in cell A2:
Code: Select all
=IFERROR(VALUE(Right((Index(ImportHtml(CONCATENATE("http://bigcharts.marketwatch.com/advchart/frames/frames.asp?symb=",A2,"&insttype=Fund"), "table"),3,1)),7)), iferror(importxml("https://www.bloomberg.com/quote/"&A2&":US", "//span[@class='priceText__1853e8a5']"), GoogleFinance(A2, "price")))