How to accumulate a running total for each cell in a column

Hi,

I'm a newbie to the board. Also a newbie to Excel, although I made my living for about 10 or so years coding integrations using Access VBA, and before that, as a professional coder in a big, awkward database/OS called PICK. Thats just so folks know I'm not an inexperienced coder. But I'm a newbie to Excel.

So, I've wrestled for days to figure something out. I'm sure I'll be embarassed when its explained to me, but whats a little pride? LOL

I've a datafeed coming in over DDE. Column B is entitled LAST, column C is entitled TRADE_VOL, column D is entitled TRADE_TIME, & column H is entitled ASK.

Here's what I want but can't figure out: I want a running total column of the amount of volume that happens at ASK and a running total column of volume that happens not at ASK. I'll call these two columns ASKSIDE & BIDSIDE, respectively.

So, every time a trade happens, TRADE_TIME changes. When an event happens on TRADE_TIME, I want to look at the current value of LAST, ASK & TRADE_VOL. If LAST < ASK, then I want to add TRADE_VOL to my BIDSIDE column else, I add it to ASKSIDE.

My problem is that I keep running into the fact that I can't seem to add TRADE_VOL to the new columns. In the database languages I code in, it would be something like:
IF LAST < ASK then BIDSIDE = BIDSIDE + TRADE_VOL else ASKSIDE = ASKSIDE + TRADE_VOL.

It seems that, in Excel, to can't refer to the variable BIDSIDE (or ASKSIDE) on both sides of the equals sign. How does Excel handle this kind of issue? I can't figure it out.

TIA

Imperial Whazoo (Excel Newbie)

I think the answer is straightforward -- can you post an example of the data?

And to restate what I said earlier, I am likewise a newcomer, so if vectoring you to a new thread was inappropriate, I apologize.

I'm not quite clear on whether the incoming data replaces the old data, i.e. is displayed in a row that is already displaying data, or whether it's added to the old data, i.e. is displayed in a new row.
It it's the former, I'm pretty sure what you want can't be done using cell formulas, so you'll have to use VBA. If it's the latter, cell formulas will be fine.

The reason that cell formulas can't refer to the cells themselves is that a worksheet will, whenever a cell changes, update all cells that depend on it immediately - so a circular reference would cause an infinite update-loop and the value would be indeterminate. Value-type variables are different in that respect, as they are only updated when an explicit instruction to do so is encountered.

Well, I don't know how to post a spreadsheet, but here's a stab at doing it in text LOL

Assume the following contents of my cells: B3 = 18.81, C3 = 1000, D3 = 12:45:09 and H3 = 18.86. Undisplayed are the two target columns, BIDSIDE & ASKSIDE. J3 is BIDSIDE and K3 ASKSIDE. For the purposes of my question, the values in J3 & K3 can be any number equal or greater than 0. So, lets assume both J3 & K3 = 5000.

EXAMPLE 1
B C D H
LAST TRADE_VOL TRADE_TIME ASK
Row 3 -->> 18.81 1000 12:45:09 18.86

So, what is being shown is the values immediately after a trade happens. The trade happened at 12:45:09 (D3) and the instant it happened, D3 became 12:45:09, B3 became 18.81, C3 became 1000, and H3 became 18.86.

So, in this case, LAST < ASK, so new BIDSIDE = old BIDSIDE + TRADE_VOL. In actual numbers then, 18.81 < 18.86, so 6000 = 5000 + 1000.

OK, now there is more trading, of course, during the day. Lets say the next trade comes in and it looks like this:

EXAMPLE 2
B C D H
LAST TRADE_VOL TRADE_TIME ASK
Row 3 -->> 18.85 17000 12:45:12 18.85

This trade is showing that someone bought 17000 shares at the ask, at 18.85. So, in the math of this event, LAST < ASK is NOT true, so the math is: ASKSIDE = old ASKSIDE + TRADE_VOL. In actual numbers then, since18.85 = 18.85, then 22000 = 5000 + 17000.

To summarize, my to examples cover two trades: one where someone bought 1000 shares below the ask and another where someone bought 17000 shares at the ask. In EXAMPLE 1, the event resulted in BIDSIDE having TRADE_VOL added to it and EXAMPLE 2 resulted in ASKSIDE having TRADE_VOL added to it. BIDSIDE startted at 5000 and became 6000 while ASKSIDE started at 5000 and became 22000.

What I'm unfamiliar with in Excel is how you use an event happening in a cell to add the value from another cell to the existing value in a third cell. In this case, the event is the change in the value in the TRADE_TIME cell which triggers the evaluation of whether the value in the LAST cell is < the value in the ASK cell,and based on that, either adds the value in TRADE_VOL to the value in the BIDSIDE cell or to the value in the ASKSIDE cell.

Hope thats not confusing.

TIA

Imperial Whazoo

Thanks for the example, that makes it pretty clear. As I said above, you can't use cell formulas for this type of update process, so it'll have to be an all-VBA solution (someone correct me if I'm wrong).

But the VBA solution should be straightforward, especially for someone with your background. :)
The Worksheet object provides a Change event which tells you in which cell(s) the change occured. So, all you should need to do is check if this cell is one that triggers an update and, if so, assign a new value to "running total" cell. To read and write cell information, you use the Worksheet and Range objects and their Cells property (see VBA Help).
The rest is simple programming logic.

Does that help, or am I being too vague?

I've never coded to Excel. Actually, never even used Excel till now LOL. I'm an old database guy.

So, basically, does the Change even attach itself to the cell that changes or to the whole worksheet? The reason I ask this is that in a DDE datastream that represents real time stock info, the speed of the price activity events is like a blizzard. It would seem that, if two change events on a worksheet level happen either simultaneously or all-but-simultaneously, you would miss the event on the latter or both. So, can the change event be affixed to the cell you are watching for changes?

Imperial Whazoo

EDITED: oh.... and no, the answer is helpful. Thanx.

To post a spreadsheet, select the "go advanced" posting option, then look below the text entry box for "manage attachments". Attach the spreadsheet as a file.

If you're doing this in code and not trying to get a formula to do it directly in the spreadsheet, add a colon after "else"

If trying to use a formula directly in the spreadsheet (although I can't see how that would work), you may find this link about reiteration in Excel helpful:

http://office.microsoft.com/client/helppreview.aspx?AssetID=HP100541491033&ns=EXCEL&lcid=1033&CTT=3&Origin=HP100662431033
(I'm not sure whether it applies to all versions or just 2007)

IF LAST < ASK then BIDSIDE = BIDSIDE + TRADE_VOL else: ASKSIDE = ASKSIDE + TRADE_VOL

I usually structure if/then/else statements in VBA like so:

IF LAST < ASK THEN
BIDSIDE = BIDSIDE + TRADE_VOL
ELSE: ASKSIDE = ASKSIDE + TRADE_VOL
END IF

or

IF LAST < ASK THEN
BIDSIDE = BIDSIDE + TRADE_VOL
ELSEIF: (another test) THEN
(do something)
ELSEIF: (another test) THEN
(do something else)
ELSE:
ASKSIDE = ASKSIDE + TRADE_VOL
END IF

You could also look at Select Case statements (see the VBA helpfile, then ask if you need further info).
Select Case last
Case Is < ask: bidside = bidside + trade_vol
Case Else: askside = askside + trade_vol
End Select

Good luck!

-geodekl

...
looks like I focused on the wrong part of the question.. glad you found a useful answer!

So, basically, does the Change even attach itself to the cell that changes or to the whole worksheet? [...] So, can the change event be affixed to the cell you are watching for changes?
No, I don't think there is a way to attach events to Ranges, or anything else below the level of Worksheets and Charts. To see a list of object types that can raise events, just type "Dim WithEvents x As " to get intellisense.
I'm not sure that'd help, even if it could be done, though. I don't know enough about Excel's event model to be able to tell you if events can be missed as you describe or if they're queued. But either way you'd have precisely the same problem, no matter whether you trap it early or let it bubble up and trap it late, I'd think.
The only other approach to this is to use a gameloop which continuously checks cell values, but unless I'm much mistaken this will make it more likely to miss updates, not less. Plus it's uglyyy. ;)

I suggest you try out the Change event and testrun it. If it turns out you're indeed missing events, the best thing would be to intercept the incoming information before it's input into the Worksheet, if that's an option?

Thanx.

Let me ask you this then: Since my spreadsheet will watch a lot of stocks, I can't use LAST and BIDSIDE and so on.

So, is this a valid modification to your code snippet?

IF B3 < ASK H3
J3 = J3 + C3
ELSE: K3 = K3 + C3
END IF

or, to reference the values in particular cells, do I have to declare them as variables?

Imperial Whazoo

I can think of using old fashioned automation because, if I recall (its been a long time since I used OLE), you can connect the cells in a spreadsheet to the fields in an Access database and I know that, in Access, I can interpret a change happening at the field level. So, does anyone remember how to connect the cells of an Excel spreadsheet to the fields of an Access recordset?

I don't have Access 2007 but I have Access 97 developer's edition on one of my boxes and I could dig out my old Access programming manuals. All I'd need to do is pipe all of the changes in the Excel cells to connected fields in the Access recorset. Gosh.... what an archaic kluge.

Imperial Whazoo

to reference the values in particular cells, do I have to declare them as variables?
Referring to cells in VBA works differently from the way it is done in cell formulas. I suggest you look at the DOM as described in the Help, e.g. by typing "Worksheet" in the VBE and pressing F1, that'll produce the article on Worksheets, from where the subordinate entities (Ranges and Cells) are linked.

If you have a strong preference for learning-by-doing, you can also use the Macro recorder (Tools->Macro->Record), mess around a bit and then look at the produced code. But that way you tend to pick up a bunch of bad coding practices, like excessive use of the Selection object instead of directly referencing cells. And since this will also yield significantly less efficient code, you probably really don't want to take this route... ;)

referring to cells in code:

open a worksheet in excel

start recording a macro (choose "this workbook" when it prompts for a location)
enter things in some cells, go to a different sheet, enter a few more things, go back to the first sheet. open and the close another workbook.
stop the macro.

press ALT+F11 to call up the VB editor so you can look at the code

cells values can be addressed in several different ways

the ones I most commonly use are
cells(1,1).value = variable 'the first digit is the row number, the second is the column number
or
variable = cells(1,1).value

cells("A1").value = whatever 'or .text or .formula etc

you can also use the range object
range("A1").value = 5 ' puts "5" in one cell
range ("A1:B4") 'describes a 2 col x 4 cell area

As Cas said, you do need to be selective about what you take from the macro approach to code, but I find it to be useful for figuring out how Excel "thinks" about things.

-geodekl

Thanx, but earlier in the thread, I raised the issue of concurrency and I'd bet my eye teath that trying to manage simultaneous changes would result in gaps in my calculated results. So, I'm pretty much going to have to work on some kind of alternate idea. I'd like to use VBA since writing Access VBA is something I did a lot of back 4 years ago so I'm pretty sure I'd muddle through but I bet that simultaneous change events would either crash Excel or generate unreliable data.

Imperial Whazoo

Off topic, but as a newbie here, how do I do that code sub-window thingy?

Imperial Whazoo

I have an idea that may work and would like a comment or two

First though, do I have to know how many cells there are going to end up being in a column?

I could modify the question I'm asking to:

If LAST < ASK then concatenate a new cell to the bottom of a column.

That way I could use SUM or SUMIF to do the math on the column and get around the circular reference thingy.

So, does anyone know if I can build a column out of my question? For example. Lets say I have a column M and I call it MSFT_BIDSIDE and another column N I'll call MSFT_ASKSIDE.

My "if statement" could then be somthing like this:
If LAST < ASK for MSFT stock, the add a new cell containing TRADE_VOL to the bottom of MSFT_BIDSIDE else add a new cell containing TRADE_VOL to the bottom of MSFT_ASKSIDE. Then I could SUM the entire colum and place that result in the appropriate cell of column J or column K.

So, can I build a column on the fly?

Imperial Whazoo

This would be ever so much easier if you could post a sample workbook ...

Oh, the last notion I posted was just brainstorming.

Basically, in summary, I've learned I can not do it in formulas because of circular references and I can not do it in VBA because of likely data loss due to two events firing simultaneously. So, the last Idea was an effort to brainstorm and stumble upon a creative idea or two.

Basically, I noticed that a column can be added for its total using SUM. So, I was trying to think of a way to build my columns as I went along. Every time a stock trades, it either happens at the ask or not at the ask. So, all I needed to be able to do is build my columns as I went along.

For each stock, every trade results in the volume traded being added to one of two columns. I start out the day with no trades so there are no cells in either column A or column B. A trade happens. The volume gets placed in the top cell of either column A or column B. Each time this happens, I sum the column and the SUM function returns the running total of the column. So, as the day goes by and trades happen, columns A & B get longer and longer, and each time I add a cell to the bottom of a column, I calculate the SUM and thus know the running total of columns A & B (BIDSIDE and ASKSIDE) every time a trade happens.

So, the only question is whether I can programmatically append new cells as needed. If I can, then theoretically, I should be able to do the above. If not, then another dead end.

So, I have no spreadsheet to show you. I was just theorizing and asking those more knowledgable if the thing I was speculating on was even possible.

Imperial Whazoo

I bet that simultaneous change events would either crash Excel or generate unreliable data.
I experimented a little with timers and events, and found that for a simple operation like a running total involving a few cells Excel/VBA can deal with on the order of 1,000 updates per second. Beyond that, things start to pile up on the input side, but you never miss a single Change event. Of course, that's all in-process, and I don't have a good enough understanding of DDE to tell you if that last part is applicable.
Off topic, but as a newbie here, how do I do that code sub-window thingy?
vB Code (http://www.xtremevbtalk.com/misc.php?do=bbcode)
That way I could use SUM or SUMIF to do the math on the column and get around the circular reference thingy. [...] So, can I build a column on the fly?
Right, that was the thought behind the question I asked you in #3 (http://www.xtremevbtalk.com/showthread.php?p=1286298#post1286298). It's certainly possible to build a column, all that's needed is keeping track of the previous row used. The question is whether it's feasible to store the amount of data you're dealing with - instead of a single sheet with a row per stock you would need a sheet per stock and a row per trade. A sheet has 2^16 rows...