Updating spreadsheet values using functions vba
It’s more stable than this VBA function and offers lifetime support and updates.
The VBA function use the Yahoo Finance API, and the code is given below (a spreadsheet also implements the function and gives a few examples of its use).
A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. When someone tells you that formulas aren’t calculating, it’s probably because the Calculation setting has been changed to Manual, instead of Automatic.
Even more mysterious, the calculations worked fine on some machines, but not others. That can happen if the first workbook that you open in an Excel session was saved as Manual calculation.
In most cases though, the problem is the Automatic Calculation setting.
To see the effects of opening Excel workbooks with different calculation option settings, watch this short Excel tutorial video.
Even if the Calculation option is set for Manual, you can use a Ribbon command or keyboard shortcut to force a calculation.
Question): In column A there are some values stored & I want them to automatically refresh every 1 second.
The function takes the form The Yahoo Finance API offers over programmatic access to over 80 types of financial data.
The spreadsheet contains a full list of the types of financial data, and the corresponding value of the second argument.
They were SUMIF formulas that referred to a named range on another worksheet.
=IF(B7=””,0, SUMIF(, E7,$D:$D)) There was no obvious reason why that wouldn’t work in Excel 2010, but I was getting desperate.
So, I kept tweaking and testing, going through the file one worksheet at a time.