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.