Hi I had a annoying problem with respect to calculating cash index,which as follows:I use REUTERS function to get intra-day data for every constitutent of the index. Prohibited byexcel's format and data arrangement,I just retrieve the first one stock's intra-day data(price and volume)and I write down a macro to change the RIC and get all stock's intra-day data.then I use intra-day data tocompute weighted price for each stock.Unfortunately,REUTERS takes time(2~5 secs) to retrieve those data and it leads the macroget the wrong answer.I have no idea about the question I face. May anyone help me?Thanx in advance!

What I think you need to do is suck the data into another sheet, do the calcs there, then copy them into the sheet that people look at.The delay of 2-5 can be dealt with by the OnTime mechanism which allows you to call another Sub after waiting a period.It's better than the Sleep Win32 API since Excel keeps going whilst it's waiting.

Hi,DCFC Many thanx for your instruction.I will try it ASAP!

Hi,DCFC Again,I got the problem on calling procedure.It seems something wrong with my codebut I don't know where the glitch is. Would you help me?Here's my codeSub abc()Sheets("a").ActivateDim i As Integeri = 1Cells(4, 1).SelectDo While ActiveCell <> "" Application.OnTime Now + TimeValue("00:00:05"), "calcs" Cells(3 + i, 3) = Cells(2, 6) i = i + 1 Cells(3 + i, 1).SelectLoopEnd SubSub calcs()Cells(2, 5) = ActiveCellEnd SubThanx!

You could use the Reuters DEX API to retrieve the TIC data and use the objects update event. You may need to change the service but the default is usually IDN. (see attached).VBA:' Tools, References, to DEX 1.0 Type Library.Private WithEvents DexInstrument As DEXLib.MrvInstrumentPrivate iRIC As Integer'Sub called from buttonPublic Sub Initialise() Me.Range("OutputRange").Clear iRIC = 0 GetIntraDayData (iRIC)End SubPrivate Sub GetIntraDayData(ByVal iRICNumber As Integer) Dim strDate As String Dim strEnd As String Dim strRIC As String Dim strRange As String Dim strStart As String On Error GoTo ErrHandler strRIC = Me.Range("RIC1").Offset(iRICNumber, 0).Value If VBA.Len(strRIC) = 0 Then Exit Sub ' Finished List End If If DexInstrument Is Nothing Then Set DexInstrument = New DEXLib.MrvInstrument strStart = VBA.Format(Me.Range("Start").Value, "HH:MM") strEnd = VBA.Format(Me.Range("End").Value, "HH:MM") strDate = VBA.Format(VBA.Date, "DD/MMM/YYYY") strRange = "START:" & strDate & "-" & strStart & " END:" & strDate & "-" & strEnd With DexInstrument .ErrorMode = EXCEPTION .IDType = "RIC" .InstrumentID = strRIC .Mode = "SOURCEBU HEADER:YES LAY:HOR" .RequestTimeSeries "LAST TRADE", Array("VALUE", "VOLUME", "FILTER_CODE1", "FILTER_CODE2", _ "FILTER_CODE3", "FILTER_CODE4"), _ strRange & " SERVICE:IDN INTERVAL:TAS" End WithFinish: Exit SubErrHandler:Debug.Print "GetIntraDayData: " & DexInstrument.ErrorString & " " & DexInstrument.RunStatus & " " & VBA.NowDebug.Print "GetIntraDayData: " & Err.Description & " " & VBA.NowGoTo FinishEnd SubPrivate Sub DexInstrument_OnUpdate(ByVal DataStatus As DEXLib.DE_DataStatus)Dim dTotVal As DoubleDim dTotVol As DoubleDim dVWAP As DoubleDim i As IntegerDim vResult As VariantOn Error GoTo ErrHandler vResult = DexInstrument.Data 'If DataStatus = 1 Then If IsEmpty(vResult) Then Me.Range("RIC1").Offset(iRIC, 2).Value = "No Data on DBU" Else For i = LBound(vResult, 1) + 1 To UBound(vResult, 1) dTotVal = dTotVal + vResult(i, 1) * vResult(i, 2) dTotVol = dTotVol + vResult(i, 2) Next i If dTotVal = 0 And dTotVol = 0 Then dVWAP = 0 Else dVWAP = dTotVal / dTotVol With Me.Range("RIC1") .Offset(iRIC, 2).Value = VBA.Format(dVWAP, "0.00##") .Offset(iRIC, 3).Value = VBA.Format(dTotVol, "#,###") .Offset(iRIC, 4).Value = VBA.Format(VBA.Time, "HH:MM:SS") End With End IfFinish:Set DexInstrument = NothingiRIC = iRIC + 1GetIntraDayData iRICExit SubErrHandler:Debug.Print "GetIntraDayData_Update: " & DexInstrument.ErrorString & " " & DexInstrument.RunStatus & " " & VBA.NowDebug.Print "GetIntraDayData_Update: " & Err.Description & " " & VBA.NowGoTo FinishEnd Sub

Hi ellertonthanx for your code!!