Using Arrays to calculate Formulas, then writing back to Sheet

[hornirl]

Hi,

I'm using a worksheet to get stock quotes, cols A-C contain Ticker, Name and Price, with col C containing the formula below for row 2 (row 1 is a header) where $A2 contains the ticker whose price I want.

=VALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/" & $A2 & "/quote/delayedPrice"))

I already have a working VBA macro to do this containing the relevant line below, and this works fine, but at 3000+ lines it runs slowly (G3258 4GHz, 8GB RAM, SSD so PC OK), even with Calculation, EnableEvents, ScreenUpdating and StatusBar off.

For i = 2 To lastrow
   ActiveSheet.Range("A" & i & ":" & Split(Cells(1, lastcol).Address, "$")(1) & i).Calculate
Next i

Loading the Range into a Variant Array might improve speed, but how to get the array to execute the formula and go and get the price data? I can use

Vals = Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula

to move the WEBSERVICE Formula into the array, but how to execute this and get the data returned from the WEBSERVICE call into the array before writing back the (hopefully) returned stock quote value to the sheet? It seems Calculate only works with sheet references, and won't work with arrays.

This might all be a moot point, in that even if I can get the returned price in the array and write it back to screen, the real bottleneck is the screen refresh (on 3000+), no matter whether I'm writing the price from the array or calculating it in situ on the sheet using Calculate. The crux of the Q is how to execute a formula stored in an array in such a way as to be able to return the value to the sheet (and keep the formula in the sheet intact if possible!).

Any help appreciated.

 

[hornirl]

Other options:

1) Copy sheet to another sheet, clear contents, then calculate values in array and write out to copied sheet. I'd have 2 sheets- one with formulas, the other values. Tested, of course it works fine but I'd consider a bit of a 'cludge' (as mentioned in Post 8).
2) Create one array to hold values, another formulas, then calculate values array from formulas array and write contents of both arrays- formulas and values- back to sheet (PasteSpecial?).

All of this hinges on processing in arrays- in this case webservice calls- using Evaluate being faster than doing the same processing directly in cells using Calculate, and taking overheads into consideration. Otherwise pursuing this would seem a waste of time.

 

[Norie]

2 Isn't that kind of what the code I posted earlier does?

The code I posted doesn't actually store the formulas but I don't actually see a need for that, unless I'm missing something about the formulas they are pretty straightforward with the only 'variable' being the ticker, which can be read from appropriate column on the sheet and inserted into the formula being evaluated.

 

[hornirl]

2 Isn't that kind of what the code I posted earlier does?

The code I posted doesn't actually store the formulas but I don't actually see a need for that, unless I'm missing something about the formulas they are pretty straightforward with the only 'variable' being the ticker, which can be read from appropriate column on the sheet and inserted into the formula being evaluated.

In the example it's fairly simple (deliberately, just to illustrate the concept), in the real world spreadsheet it's a lot more complex. The formulas don't have to be saved (it's a nice to have), especially since there's option 1 (create another sheet just for values) as a backstop. The following worked, but in doing so it showed up why the code you correctly said wouldn't calculate anything (but actually in some form or fashion did) worked- as soon as you touch a sheet formula it will recalculate it even if your Calculate method is set to manual:

Dim lastrow, lastcol, R, C, Progress, NumbofBars, PctDone As Integer
Dim fors, vals As Variant
Dim SheetName As String


SheetName = ActiveSheet.Name
ActiveSheet.Copy After:=Sheets(SheetName)
ActiveSheet.Name = SheetName & "-Vals"
ActiveSheet.Cells.ClearContents

'This new sheet is now active sheet so return to previous
Sheets(Replace(ActiveSheet.Name, "-Vals", "")).Activate


fors = Range("A1:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula
vals = Range("A1:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value


For R = LBound(vals) + 1 To UBound(vals)
    For C = LBound(vals, 2) + 1 To UBound(vals, 2)
        vals(R, C) = Evaluate(fors(R, C))
    Next C
Next R

'Activate Dupe Sheet for Write
Sheets(ActiveSheet.Name & "-Vals").Activate
Range("A1:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals
Range("A1:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula = fors
Columns("A:" & (Split(Cells(1, lastcol).Address, "$")(1))).AutoFit

So the above all works great, but when you write it back to the sheet, it's just gonna get calculated all over again, even with the sheet set to manual calculate!

That means it's a waste of time trying to load sheets into arrays (in this case) to take advantage of faster array processing and dump the results of formula calculations back to sheet. Seems the best option is to create a cleared copy of the sheet you're working on, load up values and formulas from the original, post formula results to values array and just write the resulting values array back to the sheet copy. You then end up with one sheet containing the calcs, another values, but for saving values like stock prices, that's not so bad since you can create a sheet copy for each day. Or you go back to letting the sheet do the recalculate and refresh keeping your single sheet, which is where I started from...

 

[Norie]

Why are you 'dumping' the formulas back to the sheet?

You've just used the code to calculate them.

 

[hornirl]

You're absolutely right with the duplicate sheet version here, the last section should only be:

'Activate Dupe Sheet for Write
Sheets(ActiveSheet.Name & "-Vals").Activate
Range("A1:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals
Columns("A:" & (Split(Cells(1, lastcol).Address, "$")(1))).AutoFit

So many macros, so many versions... Another reason to let the sheet (and Excel) do the heavy lifting...

In the end I found rationalizing the sheet's calcs to the extent possible (particularly dependencies) was the biggest help shaving time off what seemed to be taking way too long. Useful stuff on that here.

Thanks a mill and lots of kudos for your pointers on this, they really helped.