How to Auto Refresh AlphaVantage Technical Indicator?

[einstein1]

hi

im trying out alphavantage excel addin, to retrieve and refresh technical indicator every 5 seconds.

example formula is cell D1

alphavantage sample.xlsm
	D
1	EMA
Sheet1

Cell Formulas
Range	Formula
D1:D627	D1	=INDEX(AlphaVantage.TechnicalIndicator("AAPL","EMA","60min",A6:B7),,2)
Dynamic array formulas.

i have tried running vba code such as

Sub Calculate_Range()  
   Range("G3").Calculate  
   Application.OnTime DateAdd("s", 5, Now), "Calculate_Range"  
 End Sub

and also

Public RefreshTime As Double
Public Const Seconds = 30 'Input Refresh Interval in seconds


Sub StartRefreshLoop()

'User Message indicating loop is beginning
  MsgBox "Refreshes will begin to occur at " & _
    "the designated interval of " & Seconds & " seconds"

'Call the first Refresh
  Call StartRefreshes

End Sub


Sub StartRefreshes()

'Calculate Next Refresh Time
  RefreshTime = Now + TimeSerial(0, 0, Seconds)

'Trigger a Refresh with OnTime function
  Application.OnTime _
    EarliestTime:=RefreshTime, _
    Procedure:="RefreshConnections", _
    Schedule:=True
 
End Sub


Sub RefreshConnections()

'Refresh Data Connections
  ThisWorkbook.RefreshAll

'Start Timer Over Again
  Call StartRefreshes

End Sub


Sub EndRefreshLoop()

'On Error Resume Next
  Application.OnTime _
    EarliestTime:=RefreshTime, _
    Procedure:="RefreshConnections", _
    Schedule:=False

'User Message indicating loop has ended
  MsgBox "Refreshes are no longer occurring"

End Sub

but it doesnt seem to refresh.
it works on cells thats uses formula rand()

i have installed the xl2bb addon, but it's greyout in my windows. not sure why as well.

i have contacted support, but they say vba should work to refresh the technical indicator data.

any help please?

 

[einstein1]

the first vba code should be referencing cell D1 instead of G3, but it still doesnt work