Running totals based on single RTD cells updating.

[DrLight]

Hi.

Please forgive my inexperience - first post.

Im using RTD to pull in financial data from a stock simulator. My objective is to calculate Volume Weighted Average Price (VWAP) on a continual basis every time a trade occurs. VWAP = (Cumulative(Last price * Last volume) / cumulative Volume)

Here's what my RTD looks like:

BIDSIZE

BID

ASK

ASKSIZE

Cummulative VOLUME

LAST Price

LAST VOL

Price * QTY

Σ (Price* QTY)

Σ VOL

Current VWAP

T.TO

400

46.74

46.75

100

251066

46.74

<colgroup><col span="2"><col span="2"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Note: Columns Bidsize through Last Price update automatically as the simulator pushes data to RTD. The other columns are for illustration of how I've been approaching this problem.

I figure I need to manipulate the data comming from RTD as follows:

"New volume" - "old volume" = Last Vol
// since the simulator only sends cumulative volume i have to get Last Vol every time there is a trade.

Last Vol * Last Price
//multiply Last vol * last price every trade

Then I need a 1: cummaltive sum of all those Last vol * Last Price calculations. Σ (Price* QTY)

Then I need 2: cumulative sum of All the Last Volumes. Σ VOL

Then I need to divide 1/2 (above). and tada I have my Current VWAP calculation.

Easier said than done, I've tried formulas and 2 macros but cant figure out how to make this work given all the changes are happening in individual cells. I cant even get Last Vol. I think that would be a first step here.

Thanks for any help!!!

-DLight

 

[DrLight]

If I could use a formula to do a running total in a cell that might work: like this =IF(CELL("address")="$F$7",F7+A8,A8) Where my running total goes in cell A8.