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.

 

[Kyle123]

The issue is the amount of time it takes to return the data from the webservice 3000 times, not your machine. You're on the wrong track here.

 

[hornirl]

The issue is the amount of time it takes to return the data from the webservice 3000 times, not your machine. You're on the wrong track here.

Thanks, sometimes you're so far down a (wrong) road, you forgot why you started down it and someone reminds you. Also this got me thinking that if the webservice calls are taking their sweet time, why bother turning off screenupdating- it might as well work away too as it can while waiting for the returned data?

The 3 column example I gave for the Q is actually a little simplistic but for the Q seemed OK- the data is actually 27 items per row (i.e. 27 webservice calls)- so although there are several 1000 rows there's a lot more data (and associated webservice calls and screen refreshes, say c50,000).

Using a timer for the VBA, copying to an array and processing took 1 min pulling formulas into the array and writing values back out to sheet, running using Calculate directly within the sheet took 1 min 20 secs (for both it's presumably issuing instrux to a queue). Both then took 9 minutes to (eventually) write the resulting data to the screen with ScreenUpdating and EnableEvents set to False.

So if webservice is slow, screen updating would seem at least equally so (and seemingly much more so) in my particular case whether it's left on during execution or not. If anyone has any other ideas on how to improve speed of either webservice or screen updating, I'd love to hear them, that is aside from using the following to improve speed (source, among many others):

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

 

[Norie]

How about using Evaluate?

Something like this perhaps.

Dim rng As Range
Dim Res As Variant
Dim arrData As Variant
Dim I As Long

    Set rng = Range("A2:C100")
    
    arrData = rng.Value

    For I = LBound(arrData) To UBound(arrData)
        Res = Evaluate("VALUE(WEBSERVICE(""https://api.iextrading.com/1.0/stock/" & arrData(I, 1) & "/quote/delayedPrice""))")
        arrData(I, 3) = Res
    Next I

    rng.Value = arrData

 

[hornirl]

Yes, this works and thanks. I'm using the Dow Jones 30 (DJIA) components to test (below)- sheet (formulas should be set to manual calculation) called DJIA30 (run in this sheet so it's the active sheet) with another sheet called Instrux to hold timing values. To duplicate, the formula to go in Col 3 (Price) is =VALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/" & $A2 & "/quote/delayedPrice")) for e.g. Col 2- copy down to other Col 3 rows (I leave the price value the same in each cell prior to running the macro so I can easily see when the formula is executed).

The remaining issue is that writing back the values from the array loses the formulas which I want to keep in the cell. Just reading in the values and writing them back out again works also, so where/when they get calculated I don't know (though I'd be interested)- perhaps because Excel sees the cell as changed? That means just these 2 lines will do the trick AND keep the formula intact:

vals = Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula' Calculate?
' Write the new values back to the worksheet
Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals

Full code below also. I've left your Evaluate expression commented in though vals(I, 3) = Evaluate(vals(I, 3)) works using the formula dropped into the array.

Tick	Name	Price
AAPL	Apple Inc	56.07
AXP	American Express Co	56.07
BA	Boeing Co	56.07
CAT	Caterpillar Inc	56.07
CSCO	Cisco Systems Inc	56.07
CVX	Chevron Corp	56.07
DIS	Walt Disney Co	56.07
DWDP	DowDuPont Inc	56.07
GS	Goldman Sachs Group Inc	56.07
HD	Home Depot Inc	56.07
IBM	International Business Machines Corp	56.07
INTC	Intel Corp	56.07
JNJ	Johnson & Johnson	56.07
JPM	JPMorgan Chase & Co	56.07
KO	Coca-Cola Co	56.07
MCD	McDonald's Corp	56.07
MMM	3M Co	56.07
MRK	Merck & Co Inc	56.07
MSFT	Microsoft Corp	56.07
NKE	Nike Inc	56.07
PFE	Pfizer Inc	56.07
PG	Procter & Gamble Co	56.07
TRV	Travelers Companies Inc	56.07
UNH	UnitedHealth Group Inc	56.07
UTX	United Technologies Corp	56.07
V	Visa Inc	56.07
VZ	Verizon Communications Inc	56.07
WBA	Walgreens Boots Alliance Inc	56.07
WMT	Walmart Inc	56.07
XOM	Exxon Mobil Corp	56.07

<tbody>
</tbody>

Sub RefreshViaEvaluate()

Dim lastrow, lastcol, I, Progress, NumbofBars, PctDone As Integer
Dim vals As Variant
Dim t As Date

t = Now
' Code to be timed

lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lastcol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

Worksheets("Instrux").Range("C1") = "Execution Data/Times"
Worksheets("Instrux").Range("C2") = "Sheet: " & ActiveSheet.Name
Worksheets("Instrux").Range("C3") = "Last C/R: " & lastcol & "/" & lastrow
Worksheets("Instrux").Range("C4") = "Split Col: " & Split(Cells(1, lastcol).Address, "$")(1)
Worksheets("Instrux").Range("C5") = "Range: A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow

Application.Calculation = xlCalculationManual
Application.Run ("MORNIconOff")
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.StatusBar = "Processing..."

'Read in data from sheet to variant array
vals = Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula

'Display Status Bar
NumbofBars = 40
Application.StatusBar = "[" & Space(NumbofBars) & "]"


For I = LBound(vals) To UBound(vals)
    'vals(I, 3) = Evaluate("VALUE(WEBSERVICE(""https://api.iextrading.com/1.0/stock/" & vals(I, 1) & "/quote/delayedPrice""))")
    vals(I, 3) = Evaluate(vals(I, 3))
    If I Mod (lastrow * 0.1) = 0 Then
        DoEvents
        Progress = Int((I / lastrow) * NumbofBars)
        PctDone = Round(Progress / NumbofBars * 100, 0)
        Application.StatusBar = "[" & String(Progress, "|") & _
                                Space(NumbofBars - Progress) & "]" & _
                                " " & PctDone & "% Complete"
    End If
Next I

' Reset All Defaults
Application.Run ("MORNIconIsOn")
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.UsedRange.Font.Size = 8
Rem Recalc Chained Performance #s
Rem ActiveSheet.UsedRange.Columns("U:X").Calculate

'Completion, Write Array Values to Sheet, Data Stats, Housekeeping
Application.StatusBar = "Completed!"
MsgBox "Time to Complete: " & Format(Now - t, "hh:mm:ss"), vbOKOnly, "Code Timer"
Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals
ActiveSheet.UsedRange.Columns("A:" & (Split(Cells(1, lastcol + 4).Address, "$")(1))).AutoFit
Worksheets("Instrux").Range("C6") = "Time to Execute (H:M:S): " & Format(Now - t, "hh:mm:ss")
Worksheets("Instrux").Range("C7") = "Time Now (H:M:S): " & Format(Now, "hh:mm:ss")
Worksheets("Instrux").Range("C8") = "Time SU Finished (H:M:S):"
Worksheets("Instrux").Columns("C").AutoFit
Application.StatusBar = ""


End Sub

Thanks for any help. At this point I've got something useable on larger data sets (S&P500, Russell 3000 index components) so I'm not complaining, it's more of an 'Í'd like to know' issue now.

 

[hornirl]

Sorry "for e.g. Col 2" s/b Row 2 (can't edit post (even within 10 min timeout))...

 

[Norie]

This is where the formula(s) are calculated.

       Res = Evaluate("VALUE(WEBSERVICE(""https://api.iextrading.com/1.0/stock/" & arrData(I, 1) & "/quote/delayedPrice""))")

The result from the formula is then put back into the array and once all the formulas have been calculated the array is written back to the sheet.

If you leave the formulas in the cells they will be constantly getting recalculated.

Do you really need the formulas in the cells?

 

[hornirl]

This is where the formula(s) are calculated.

       Res = Evaluate("VALUE(WEBSERVICE(""https://api.iextrading.com/1.0/stock/" & arrData(I, 1) & "/quote/delayedPrice""))")

The result from the formula is then put back into the array and once all the formulas have been calculated the array is written back to the sheet.

If you leave the formulas in the cells they will be constantly getting recalculated.

Do you really need the formulas in the cells?

On calc, yes I know and vals(I, 3) = Evaluate(vals(I, 3)) does the same. With Formulas->Calculation Options->Manual the sheet won't autocalc (Application.Calculation = xlCalculationManual does this in VBA), so I can leave formulas in with this and just recalc when I need.

Needing the formulas in the cells? Would be nice, since some data sets are small and F9 works to recalc small sheets, but some are much larger and I'm looking for heavier duty improvements in performance if possible.

I could always read the formulas into an array from one sheet and write the results out to another sheet, but that seems a bit 'cludgy'. Keeping the formula in with it's newly calculated value is the goal- the 2 lines

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

do this as mentioned in my last post but I don't understand how or why (again, speculating perhaps because Excel sees the cell as changed so recalculates?).

 

[Norie]

What are you trying to do here?

vals = Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula' Calculate?
' Write the new values back to the worksheet
Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals

That's not going to calculate anything, if you did want to calculate formulas that were in the array vals you would need to loop and use Evaluate.

 

[hornirl]

What are you trying to do here?

vals = Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Formula
' Calculate?
' Write the new values back to the worksheet
Range("A2:" & Split(Cells(1, lastcol).Address, "$")(1) & lastrow).Value = vals

That's not going to calculate anything, if you did want to calculate formulas that were in the array vals you would need to loop and use Evaluate.

This just happens to work, but as mentioned I don't understand how or why (again, speculating perhaps because Excel sees the cell as changed so recalculates?).

The following from the prior post does the loop and Evaluate:

For I = LBound(vals) To UBound(vals)
    'vals(I, 3) = Evaluate("VALUE(WEBSERVICE(""https://api.iextrading.com/1.0/stock/" & vals(I, 1) & "/quote/delayedPrice""))")
    vals(I, 3) = Evaluate(vals(I, 3))
    If I Mod (lastrow * 0.1) = 0 Then
        DoEvents
        Progress = Int((I / lastrow) * NumbofBars)
        PctDone = Round(Progress / NumbofBars * 100, 0)
        Application.StatusBar = "[" & String(Progress, "|") & _
                                Space(NumbofBars - Progress) & "]" & _
                                " " & PctDone & "% Complete"
    End If
Next I

but it just writes values ONLY back from array to sheet (formulas are now gone) and ideally I'd like to update values and keep formulas.