Using Arrays to calculate Formulas, then writing back to Sheet

H

hornirl

New Member
Joined
Oct 21, 2018
Messages
9
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.
Code: 
=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.
Code: 
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

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
Kyle123 said:
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.
Click to expand...
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
 
Upvote 0
How about using Evaluate?

Something like this perhaps.
Code: 
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
 
Upvote 0
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:

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


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

Code: 
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.
 
Upvote 0
This is where the formula(s) are calculated.
Code: 
       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?
 
Upvote 0
Norie said:
This is where the formula(s) are calculated.
Code: 
       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?
Click to expand...

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

Code: 
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?).
 
Upvote 0
What are you trying to do here?
Code: 
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.
 
Upvote 0
Norie said:
What are you trying to do here?
Code: 
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.
Click to expand...
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:
Code: 
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.


 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
2K
Plod518
P
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
494
Aurum1
A
JeffGrant
  • Question Question
Can I use arrays to do write formulas to cells?
Replies
7
Views
409
Peter_SSs
Peter_SSs
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
387
Coyotex3
C
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top