Google finance excel query table start date

#Google finance excel query table start date download#

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. 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. 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. 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. 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.

Google's got the inside track here, unless anyone can see ways to speed up the custom function scripts. 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. Normally it is only a second or two.ДЄ 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 for 66 tickers-maybe 5 seconds tops. I'll try to get a screen capture if it happens again. When I've seen the continuous 'Loading.' it was for your MStarNav and MStarPrice functions.

#Google finance excel query table start date download#

It is satisfying to press my Download Prices button and have my Excel spreadsheet updated. Probably not the most efficient way, but it seems to work. When that is complete I clear the area of NAVs imported. If there is a match I use the Cells function to copy the array NAV to a price cell. I then iterate checking the array symbol against the symbols in my portfolio. These data are placed in an empty area of the Excel spreadsheet. Since it returns a comma delimited data I need to use the TextToColumns function to separate the ticker from the price. On the transfer to Excel I use passing the URL of the GS spreadsheet. Today I checked at market close + 3 hours and all the prices are there. I do agree the MStarPrice is populating sooner. Just look at the error in the Google Sheet, and see how it shows up in your Excel sheet. 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)).ДЄt 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. It's as simple as IFERROR(whatever), since the default if value_if_error is not passed is blank. 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. 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. If you download to a csv file, you'll just see #ERROR! in those cells. (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). For example, I see 'Element - found in price position is not a number. 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. Sorry Leif, I don't use Excel, and have no idea what a VBA QueryTables function is. 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?