Google sheets not pulling stock prices

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Tue Apr 30, 2019 9:17 pm

Leif wrote:
Tue Apr 30, 2019 8:05 pm
Kevin, since I'm pulling the NAVs using Excel's VBA QueryTables function do you know what happens if an error in thrown in GS? Perhaps I need to do my error checking in VBA after import and remove the throws?
Sorry Leif, I don't use Excel, and have no idea what a VBA QueryTables function is.

In Google Sheets, throwing an error from a custom function displays #ERROR! in the cell, and if you hover over it, you see the error message thrown by the custom function. For example, I see "Element — found in price position is not a number. (line 36)." when I try to retrieve price for a Vanguard MM fund, since &mdash is the HTML element where the price should be (that's due to the extra error trapping I added for this function).

If you download to a csv file, you'll just see #ERROR! in those cells. If Google Sheets itself generates an error, you'll see one of the GS error displays, such as #N/A when no data is returned by GoogleFinance.

You can trap for any error directly in Google Sheets with IFERROR, and generate whatever you want in the cell; I often just generate a blank. It's as simple as IFERROR(whatever), since the default if value_if_error is not passed is blank. For example, I use something like this if I just want a blank if GoogleFinance doesn't return a value for a particular attribute: =IFERROR(GOOGLEFINANCE($A5,J$2)).

At any rate, I would think it would be straightforward to see how whatever you're doing in Excel handles any particular error thrown by a custom function or Google built-in function. Just look at the error in the Google Sheet, and see how it shows up in your Excel sheet.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Leif
Posts: 2915
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif » Tue Apr 30, 2019 10:46 pm

Kevin,

I added a question to one of my posts, but I think I added it after you already responded, so perhaps you did not see it.
Sometimes when I pull up my GS it continues to say "Loading...", for all the tickers, with a comment that an error occurred. If I do a page refresh I get the NAVs. Have you seen that? I wonder if that will still happen on Excel import?
In other words, it seems to just freeze while trying to update. I've seen that about 2 or 3 times.

User avatar
vitaflo
Posts: 1272
Joined: Sat Sep 03, 2011 3:02 pm

Re: Google sheets not pulling stock prices

Post by vitaflo » Tue Apr 30, 2019 11:39 pm

Leif wrote:
Tue Apr 30, 2019 10:46 pm
Kevin,

I added a question to one of my posts, but I think I added it after you already responded, so perhaps you did not see it.
Sometimes when I pull up my GS it continues to say "Loading...", for all the tickers, with a comment that an error occurred. If I do a page refresh I get the NAVs. Have you seen that? I wonder if that will still happen on Excel import?
In other words, it seems to just freeze while trying to update. I've seen that about 2 or 3 times.
You're probably trying to do too many calls at once. Google limits the number of calls you can do, so if you have a lot of tickers you're trying to pull at the same time it will barf on you. You could try using a lock to get around it.

Code: Select all

  var lock = LockService.getScriptLock();
  lock.waitLock(20000);
  Utilities.sleep(500);
  
  // Do something
  
  lock.releaseLock();
Of course you'll have to wait a lot longer for info to populate (it will say "Loading..." for a lot longer) but you'll be less likely to get errors popping up.

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Wed May 01, 2019 3:31 pm

Leif wrote:
Tue Apr 30, 2019 10:46 pm
Kevin,

I added a question to one of my posts, but I think I added it after you already responded, so perhaps you did not see it.
Sometimes when I pull up my GS it continues to say "Loading...", for all the tickers, with a comment that an error occurred. If I do a page refresh I get the NAVs. Have you seen that? I wonder if that will still happen on Excel import?
In other words, it seems to just freeze while trying to update. I've seen that about 2 or 3 times.
Is this for my custom function(s), GoogleFinance, or both?

I am pulling quotes and update date(/time) for 66 tickers (a few of them invalid) using both custom functions, GoogleFinance, and VLOOKUPs on a sheet that pulls the Vanguard mutual fund prices with the other custom function, and I haven't seen the sheet get stuck Loading ... for more than a few seconds.

Would you mind outlining the process you're using to get the data from Google Sheets to Excel? Your questions indicate that you have this automated somehow, and if so, I'm curious how.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Leif
Posts: 2915
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif » Wed May 01, 2019 6:06 pm

When I've seen the continuous "Loading..." it was for your MStarNav and MStarPrice functions. Google Finance has no problem. I'm pulling 18 quotes. I'll try to get a screen capture if it happens again. Maybe it just needs a bit more time. Normally it is only a second or two. I do agree the MStarPrice is populating sooner. Today I checked at market close + 3 hours and all the prices are there.

On the transfer to Excel I use ActiveSheet.QueryTables.Add passing the URL of the GS spreadsheet. Since it returns a comma delimited data I need to use the TextToColumns function to separate the ticker from the price. These data are placed in an empty area of the Excel spreadsheet. I then load the data into an array. I then iterate checking the array symbol against the symbols in my portfolio. If there is a match I use the Cells function to copy the array NAV to a price cell. When that is complete I clear the area of NAVs imported.

Probably not the most efficient way, but it seems to work. It is satisfying to press my Download Prices button and have my Excel spreadsheet updated.

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Wed May 01, 2019 8:00 pm

Leif wrote:
Wed May 01, 2019 6:06 pm
When I've seen the continuous "Loading..." it was for your MStarNav and MStarPrice functions. Google Finance has no problem. I'm pulling 18 quotes. I'll try to get a screen capture if it happens again. Maybe it just needs a bit more time. Normally it is only a second or two.
A screenshot won't help--I know what it looks like, I just haven't had the problem where it takes more than 3-4 seconds for both of the Mstar custom functions to pull (or attempt to pull) price and as-of date[time] for 66 tickers--maybe 5 seconds tops. I do see Loading ... with GoogleFinance with this many tickers, but the data comes back a 2-3 seconds faster than for the custom functions with this many tickers. Google's got the inside track here, unless anyone can see ways to speed up the custom function scripts.

Since I'm pulling a lot more data than you are, I don't know why you'd be seeing this problem when I don't. However, I do see some Google Sheets loading issues sometimes, and usually just deleting the formula(s) and then hitting ctrl-z or clicking the undo icon forces a reload, and that usually resolves it.

ImportRange (used to import data from one spreadsheet to another) is particularly flaky, and I use it a lot, so I've developed some scripting to delete the ImportRange formulas, then copy them back (can't find an undo) to get ImportRange to fire. Sometimes have to do this more than once, and including SpreadsheetApp.flush() after the delete and after the setFormula seems to help, along with a little sleep time between deleting and setting. But this isn't relevant to custom functions, and I don't see how any of this is relevant to the way you're doing things, since you're not actually "in" the sheet with your Excel functionality.

Incidentally, to ensure I'm seeing current values in my Prices sheet, I click the top-left "select all cells" square (above row 1 and to the left of column a), press delete, then ctrl-z, which forces all formulas to execute.
I do agree the MStarPrice is populating sooner. Today I checked at market close + 3 hours and all the prices are there.
Yeah, no doubt about it from what I've seen so far.
On the transfer to Excel I use ActiveSheet.QueryTables.Add passing the URL of the GS spreadsheet.
OK, this is the part I was mostly curious about--the Excel functionality to pull data from a spreadsheet available via a URL. I think LibreOffice Calc has something similar, so maybe I'll play around with that if it actually works with a Google spreadsheet URL.

This actually seems similar to Google Sheets ImportRange, which as I mentioned above, is pretty flaky. When ImportRange doesn't seem to be working, I just see #N/A in the ImportRange formula cell, which is one of the Google Sheets error types. I usually don't see Loading ...

The weird thing is that when I use a script to get the contents of the value of the ImportRange formula cell or any other where data should be, the script retrieves the value that's supposed to be there! I don't think I've ever logged it retrieving #N/A in the formula cell. I've checked both value and displayValue, and they always show the same thing. It's like the data is there internally, but for some reason it's not being displayed. So I can't check the contents of that cell with a script to determined whether something needs to be done to refresh the ImportRange.

However, I do usually (but not always) retrieve #N/A from a cell that's referencing one of the cells that ImportRange is supposed to populate, for example with a VLOOKUP or FILTER with the ticker as the search key. So, I am able to use this to determine if I need to run some script to try and get the ImportRange to refresh the data. I run this when the spreadsheet is opened with the onOpen event, and provide a custom menu to check for #N/A or just run the appropriate refresh script.

Having said all of this, I haven't really run into problems using ImportRange for importing prices from another spreadsheet, but rather for importing downloads from Vanguard and Fidelity in other spreadsheets. Not sure why those cause a problem, since the data in those is pretty much static, while the data in the Prices spreadsheet is being populated by custom functions.

Not sure how relevant any of this is to you, but who knows. Let us know if you run into problems, and what you see if you do.
Since it returns a comma delimited data I need to use the TextToColumns function to separate the ticker from the price. These data are placed in an empty area of the Excel spreadsheet. I then load the data into an array. I then iterate checking the array symbol against the symbols in my portfolio. If there is a match I use the Cells function to copy the array NAV to a price cell. When that is complete I clear the area of NAVs imported.
However you're doing it, it seems like you are in a better position than I am to tell us what gets pulled into Excel if the data isn't available in the Google Sheet, for whatever reason. You might need to eliminate the last step where you clear the imported NAVs, at least temporarily, unless whatever is there gets copied exactly to your price cells, in which case you'll get the answer.

Since "Loading ..." is what's displayed in the cell, maybe that's what would get pulled into your spreadsheet.
Probably not the most efficient way, but it seems to work. It is satisfying to press my Download Prices button and have my Excel spreadsheet updated.
Yeah, it seems like a decent workaround for not having the all of the cool Google Sheets built-in internet functionality in Excel.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Thu May 02, 2019 12:37 pm

Kevin M wrote:
Thu Apr 25, 2019 5:59 pm
I currently am getting prices for Vanguard funds using a script that scrapes the Vanguard funds summary page--it pulls the prices for over 200 funds in about a second, and the prices are updated much sooner than GoogleFinance. This is in a dedicated prices and yields spreadsheet, which serves as a source for Vanguard prices and yields in other spreadsheets.
Kevin M wrote:
Sat Apr 27, 2019 1:36 pm
Here is a view-only link to a spreadsheet that has several sheets that call the custom function with different parameters: https://docs.google.com/spreadsheets/d/ ... sp=sharing.
Last night I discovered something very cool. With one small change to the URL used by this custom function, the same script will retrieve the same data and attributes for all 80 Vanguard ETFs!

The only thing that's missing is share class, since the mutual fund script only checks for investor, admiral and institutional shares. Adding another check for the isETF attribute fixes that. The JSON structure is exactly the same for ETFs as it is for mutual funds (at least for all the data and attributes I'm retrieving), so everything else gets retrieved with no other changes to the script. Nice of Vanguard to be consistent in this respect.

This retrieves the most recent available ETF closing price; e.g., as of now (market open), you see prices for yesterday. There's another URL that can be used to get the most recent price available from Vanguard for an individual ETF, so a custom function for that would be similar to the one to get market price from M*, but if all you're interested in is closing prices, getting them all with one URL fetch is more efficient.

Since all the code except the URL is the same, it doesn't make sense to duplicate it in two custom functions. I'm thinking about how to combine them. My current thought is to add a flag that can have three values:

0 - mutual funds only (default if missing, so would work the same as the current function).
1 - mutual funds and ETFs.
2 - ETFs only.

I would break the function up a bit. I'm thinking that the custom function (called from the spreadsheet) would populate the header row, then pass the data array and the parameter flags to a function that populates the data and attributes. This function would have most of the existing code in it, and would push the data for the mutual funds or ETFs into the array.

For mutual funds and ETFs (MF/ETF flag = 1), the second call to the data population function would pass the array with the mutual funds already populated, so the ETFs would be appended to this array. For mutual funds only (flag = 0 or missing) or ETFs only (flag = 2), the array passed in would contain only the header row, and then the data for either mutual funds or ETFs would be appended to the array.

Any thoughts?

Kevin
Last edited by Kevin M on Thu May 02, 2019 2:02 pm, edited 1 time in total.
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Leif
Posts: 2915
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif » Thu May 02, 2019 1:32 pm

Just got the "Loading..." message again. I was patient and I got the prices after 3 minutes. Perhaps it is some local issue with my internet provider? I guess I need to stay with my GoogleFinance quotes for now.

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Thu May 02, 2019 2:13 pm

Leif wrote:
Thu May 02, 2019 1:32 pm
Just got the "Loading..." message again. I was patient and I got the prices after 3 minutes. Perhaps it is some local issue with my internet provider? I guess I need to stay with my GoogleFinance quotes for now.
Weird.

What happens if you run your Excel macro without opening the Google Sheet?

Does it make a difference if you execute the custom function for only one ticker? Just trying to determine if it's an issue with multiple calls to the function, or if it happens with even one call to the function.

Are you using primarily Vanguard funds, and if so, have you tried the custom function to pull prices for Vanguard mutual funds? That might be quicker, since it's only one URL fetch. You could then do a VLOOKUP to get just the prices for your tickers in another sheet or in your Excel spreadsheet.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Thu May 02, 2019 2:27 pm

And, have you just tried using the ImportXml version that the custom function is based on?
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Thu May 02, 2019 2:38 pm

Kevin M wrote:
Sun Apr 28, 2019 6:19 pm
walletless wrote:
Wed Apr 11, 2018 2:47 am
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))
Using Morningstar.com:

Code: Select all

=IMPORTXML("http://quotes.morningstar.com/fund/c-header?t=" & A2, "//span[@vkey='NAV']")
<snip>
Now if someone can help convert that Morningstar call into appscript code, I'd be very thankful!
I didn't see that anyone responded to your request, so I created an apps script custom function that does it:
<snip>
I'm sure something much more elegant and concise could be written by importing and parsing XML, but working with the HTML as a string is very straightforward. Maybe I'll try an XML version later, unless someone else beats me to it, but this works fine.
Turns out that an XML version of the custom function is not so straightforward. XmlService.parse(xml) fails to parse the content text returned by the URL fetch; a not-well formed XML error is returned. I was able to strip off HTML before and after the <div> ... </div> that includes the price, and this can be parsed as XML, but having to do this is as much work as just working with the HTML to get the price directly. Then you have to do some XML navigation on top of that to get the price.

Apparently ImportXml is more lenient about the XML structure than is XmlService.parse().

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
wander
Posts: 2992
Joined: Sat Oct 04, 2008 9:10 am

Re: Google sheets not pulling stock prices

Post by wander » Thu May 02, 2019 2:47 pm

It happened to me quite often. Mostly, the problem can be fixed by close and open the browser again. Lately, I use Firefox instead of Chrome. Chrome constantly gives me error.

User avatar
Leif
Posts: 2915
Joined: Wed Sep 19, 2007 4:15 pm

Re: Google sheets not pulling stock prices

Post by Leif » Thu May 02, 2019 3:14 pm

Kevin M wrote:
Thu May 02, 2019 2:13 pm
Weird.

What happens if you run your Excel macro without opening the Google Sheet?
I've not tried that yet. I just open the spreadsheet now as a test. I don't want to make modifications to my Excel if I'm going to have issues with the custom functions.
Kevin M wrote:
Thu May 02, 2019 2:13 pm
Does it make a difference if you execute the custom function for only one ticker? Just trying to determine if it's an issue with multiple calls to the function, or if it happens with even one call to the function.
I can try that, but the issue seems to be random (or I've not figured out the pattern). Maybe only once in 10 or so times have I seen the issue.
Kevin M wrote:
Thu May 02, 2019 2:13 pm
Are you using primarily Vanguard funds, and if so, have you tried the custom function to pull prices for Vanguard mutual funds? That might be quicker, since it's only one URL fetch. You could then do a VLOOKUP to get just the prices for your tickers in another sheet or in your Excel spreadsheet.
Primarily Fidelity funds, but some Vanguard and DFA. It does seem to wait to update the spreadsheet until it has all the prices, not one at a time.

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

Re: Google sheets not pulling stock prices

Post by sperry8 » Tue Sep 17, 2019 5:44 pm

Argh. Nasdaq changed their website and my workaround stopped working. I was using this:

Code: Select all

=INDEX(IMPORTHTML("https://www.nasdaq.com/symbol/"&"VMFXX","table",3),2,5)
to pull in yields (For clarity I'm trying to pull in the 7 day yield.). Anyone else know where can I pull them that still has tables or a source that can be pulled into Excel?
BH contest results: 2019: #233 of 645 | 18: #150 of 493 | 17: #516 of 647 | 16: #121 of 610 | 15: #18 of 552 | 14: #225 of 503 | 13: #383 of 433 | 12: #366 of 410 | 11: #113 of 369 | 10: #53 of 282

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Thu Sep 19, 2019 3:01 pm

sperry8 wrote:
Tue Sep 17, 2019 5:44 pm
Argh. Nasdaq changed their website and my workaround stopped working. I was using this:

Code: Select all

=INDEX(IMPORTHTML("https://www.nasdaq.com/symbol/"&"VMFXX","table",3),2,5)
to pull in yields (For clarity I'm trying to pull in the 7 day yield.). Anyone else know where can I pull them that still has tables or a source that can be pulled into Excel?
I'm still using a solution based on my Google Sheets custom function getVanguardPriceYieldAndAttributes for Vanguard fund yields, discussed in this post above, and it has continued to work with 100% reliability for me. I check the yields almost daily.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

retiringwhen
Posts: 1743
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen » Sun Sep 22, 2019 5:29 pm

Kevin M wrote:
Thu May 02, 2019 12:37 pm
Last night I discovered something very cool. With one small change to the URL used by this custom function, the same script will retrieve the same data and attributes for all 80 Vanguard ETFs!

The only thing that's missing is share class, since the mutual fund script only checks for investor, admiral and institutional shares. Adding another check for the isETF attribute fixes that. The JSON structure is exactly the same for ETFs as it is for mutual funds (at least for all the data and attributes I'm retrieving), so everything else gets retrieved with no other changes to the script. Nice of Vanguard to be consistent in this respect.

This retrieves the most recent available ETF closing price; e.g., as of now (market open), you see prices for yesterday. There's another URL that can be used to get the most recent price available from Vanguard for an individual ETF, so a custom function for that would be similar to the one to get market price from M*, but if all you're interested in is closing prices, getting them all with one URL fetch is more efficient.

Since all the code except the URL is the same, it doesn't make sense to duplicate it in two custom functions. I'm thinking about how to combine them. My current thought is to add a flag that can have three values:

0 - mutual funds only (default if missing, so would work the same as the current function).
1 - mutual funds and ETFs.
2 - ETFs only.

I would break the function up a bit. I'm thinking that the custom function (called from the spreadsheet) would populate the header row, then pass the data array and the parameter flags to a function that populates the data and attributes. This function would have most of the existing code in it, and would push the data for the mutual funds or ETFs into the array.

For mutual funds and ETFs (MF/ETF flag = 1), the second call to the data population function would pass the array with the mutual funds already populated, so the ETFs would be appended to this array. For mutual funds only (flag = 0 or missing) or ETFs only (flag = 2), the array passed in would contain only the header row, and then the data for either mutual funds or ETFs would be appended to the array.

Any thoughts?

Kevin
Kevin, I just found this thread this weekend (I am playing with your April 27th sheet and loving it!) I am doing essentially a NJ fund analysis similar to what you have done previously. I will share the resulting sheet with you.

As to the question posted above, I support the design you propose with a consistent single entry point with the final parameter deciding the classes to return. I guess it could be possible to override the institutional parameter to have 4 values, but that may be backwards compatibility issues for folks who try to re-use the code. Have you completed this effort or published it elsewhere

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Mon Sep 23, 2019 7:43 pm

retiringwhen wrote:
Sun Sep 22, 2019 5:29 pm
Kevin M wrote:
Thu May 02, 2019 12:37 pm
Last night I discovered something very cool. With one small change to the URL used by this custom function, the same script will retrieve the same data and attributes for all 80 Vanguard ETFs!

The only thing that's missing is share class, since the mutual fund script only checks for investor, admiral and institutional shares. Adding another check for the isETF attribute fixes that. The JSON structure is exactly the same for ETFs as it is for mutual funds (at least for all the data and attributes I'm retrieving), so everything else gets retrieved with no other changes to the script. Nice of Vanguard to be consistent in this respect.

This retrieves the most recent available ETF closing price; e.g., as of now (market open), you see prices for yesterday. There's another URL that can be used to get the most recent price available from Vanguard for an individual ETF, so a custom function for that would be similar to the one to get market price from M*, but if all you're interested in is closing prices, getting them all with one URL fetch is more efficient.

Since all the code except the URL is the same, it doesn't make sense to duplicate it in two custom functions. I'm thinking about how to combine them. My current thought is to add a flag that can have three values:

0 - mutual funds only (default if missing, so would work the same as the current function).
1 - mutual funds and ETFs.
2 - ETFs only.

I would break the function up a bit. I'm thinking that the custom function (called from the spreadsheet) would populate the header row, then pass the data array and the parameter flags to a function that populates the data and attributes. This function would have most of the existing code in it, and would push the data for the mutual funds or ETFs into the array.

For mutual funds and ETFs (MF/ETF flag = 1), the second call to the data population function would pass the array with the mutual funds already populated, so the ETFs would be appended to this array. For mutual funds only (flag = 0 or missing) or ETFs only (flag = 2), the array passed in would contain only the header row, and then the data for either mutual funds or ETFs would be appended to the array.

Any thoughts?

Kevin
Kevin, I just found this thread this weekend (I am playing with your April 27th sheet and loving it!) I am doing essentially a NJ fund analysis similar to what you have done previously. I will share the resulting sheet with you.

As to the question posted above, I support the design you propose with a consistent single entry point with the final parameter deciding the classes to return. I guess it could be possible to override the institutional parameter to have 4 values, but that may be backwards compatibility issues for folks who try to re-use the code. Have you completed this effort or published it elsewhere
Thanks for the feedback!

No, I lost interest in this as I moved onto other things, and what I have now has been working for me.

GoogleFinance does a good job for ETFs (pretty timely updates), so I'm still using that in my spreadsheets for price. I haven't had a need yet to get attributes and other things I pull for the mutual funds.

I actually haven't done any scripting in a couple of months--I seem to get intensely involved in it for a few weeks or months, then lose interest and get involved in other things until another pressing need arises.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

retiringwhen
Posts: 1743
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: Google sheets not pulling stock prices

Post by retiringwhen » Mon Sep 23, 2019 8:06 pm

Kevin M wrote:
Mon Sep 23, 2019 7:43 pm
Thanks for the feedback!

No, I lost interest in this as I moved onto other things, and what I have now has been working for me.

GoogleFinance does a good job for ETFs (pretty timely updates), so I'm still using that in my spreadsheets for price. I haven't had a need yet to get attributes and other things I pull for the mutual funds.

I actually haven't done any scripting in a couple of months--I seem to get intensely involved in it for a few weeks or months, then lose interest and get involved in other things until another pressing need arises.

Kevin
I can understand the scripting intensity, I still do a bit on my own as needed (used to be my job, now it just supports my finance hobby!) , but don't normally have the spare time to throw at it (that is why I use other people's code when possible!)

BTW, the tricks for the money markets and bond funds did automate a few things for me. I created one for just NJ money markets that I am going to share with our local BH chapter as the last meeting we had discussions about how to pick money market funds. I have a more general one for looking at all bond funds, but it gets a bit involved. Thanks for the work you share to make it happen.

eightytwo77
Posts: 1
Joined: Sun May 24, 2020 8:25 pm

Re: Google sheets not pulling stock prices

Post by eightytwo77 » Sun May 24, 2020 8:32 pm

This thread has been very helpful! I'm trying to scrape Morningstar data into Google Sheets from http://performance.morningstar.com/perf ... hmarktype=.

The function below works for all rows other than the S&P500 data. Changing "/tr[1]" to "/tr[2]" doesn't work like it does for the other rows. Any ideas?
"=IMPORTXML(concatenate("http://performance.morningstar.com/perf ... hmarktype="), "//table/tbody[1]/tr[1]/td[1]")/100"

User avatar
Kevin M
Posts: 11530
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Google sheets not pulling stock prices

Post by Kevin M » Mon May 25, 2020 5:27 pm

eightytwo77 wrote:
Sun May 24, 2020 8:32 pm
This thread has been very helpful! I'm trying to scrape Morningstar data into Google Sheets from http://performance.morningstar.com/perf ... hmarktype=.

The function below works for all rows other than the S&P500 data. Changing "/tr[1]" to "/tr[2]" doesn't work like it does for the other rows. Any ideas?
"=IMPORTXML(concatenate("http://performance.morningstar.com/perf ... hmarktype="), "//table/tbody[1]/tr[1]/td[1]")/100"
You can import the entire table simply with this:

=IMPORTHTML($B$1,"table", 1)

Where the URL is in cell B1. You can then use other spreadsheet functions to extract the cells of interest.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

GrowthSeeker
Posts: 860
Joined: Tue May 15, 2018 10:14 pm

Re: Google sheets not pulling stock prices

Post by GrowthSeeker » Mon Jun 01, 2020 8:45 am

My primary method for pulling prices is just:

Code: Select all

=GOOGLEFINANCE(A3,"price")
where A3 is the ticker symbol. I have a ticker list in one column of a google sheet with price in the next column. But I use LibreOffice Calc to contain all my personal data, so I just copy the column of prices from the google sheet, which is in the cloud, to my private LibreOffice sheet. Any of my working sheets that need price data then "look at" that master list of prices.

But sometimes that isn't working, or slow. And many of the Vanguard fund tickers are quite delayed.
So here is a hack to get multiple ticker prices, it's not automatic, but only took me 25 seconds just now, including the time waiting for the page to load. This hack can be done from any spreadsheet, not just google sheets.
In one cell of my LO sheet I have a list of all the tickers I want to download separated by spaces and in the same order as my column of tickers. This happens to be in cell A28.
Just below that in A29, I have this URL: " https://bigcharts.marketwatch.com/quote ... asp?view=Q"
Below that is (initially) a lot of blank space. At least as many rows as you have tickers. And at least 10 columns wide.
Procedure: click cell A28, type Cntl-C to copy
Then over cell A29 (with the URL) type Cntl-click, i.e. hold down the control key while left clicking the mouse. This opens up your default browser to the bigcharts page in quote mode.
On the bigcharts page is a textbox for entering ticker symbols. But you can enter more than one.
Next: click in that textbox, type Cntl-V to paste. The box now fills with your list of tickers.
Next: click the "show" button on the web page.
A table appears with all your stocks and funds.
Next: click and drag to highlight the entire table, and once selected, type Cntl-C to copy.
Go back to your spreadsheet and click at the top left of a large clear space, for me, I just click in cell A30.
Next: type Cntl-V to paste. I just leave those values there and overwrite them the next time I collect data. Then I just have to copy and paste any new price date into the cells I normally paste the price data into.
Just because you're paranoid doesn't mean they're NOT out to get you.

mroe800
Posts: 165
Joined: Mon Jan 27, 2020 1:37 am

Re: Google sheets not pulling stock prices

Post by mroe800 » Mon Jun 01, 2020 9:26 am

I have a similar implementation of the BigCharts url, but I use a textjoin function across a range to dynamically add tickers as I input them inside that range. I then feed that value into a concat function to assemble the full URL that is being used in the importhtml call.

You can cleanup the imported table by using the index function appropriately. I make extensive use of indexing, google query, importhtml/xml functions in my sheets to fulfill minor automation.

Post Reply