Time-Slicing Data

It’s quite common that data consists of a sequence of dated event or transaction records. We’re all familiar with this from bank statements, where a sequence of transactions changes the running balance of our account. For a single object (e.g. account), this is easy enough to do with an extra ‘state’ (e.g. balance) column.

However, what do you do when the sequence contains events for more than one object. The obvious approach is to have a state column for each object:

timesliceExcel1

The state columns filter using an IF function. For example, D3 contains:

=IF(Object=$D$1,D2+Value,D2)

Aficionados of array formulas can strut their stuff too:

timesliceExcel2

G3 contains:

{=SUM((Date <= $A3) * (Object = G$2) * Value)}

This single-cell array formula fills down and right nicely. Note that the results are subtly different. The IF formula is row-based, so Row 4 shows  B=0, because the B record is in Row 5. The array formula is date-based, so Row 4 and Row 5 both show B=3 for 3rd June. I prefer the latter behaviour.

Limitations:

  • With Excel pre-2007, the column limit might be a factor for a large set of objects.
  • You have to know your set of objects up front, even though some objects might not appear until some way through the event sequence.
  • The notion of state, and the way that event parameters change the state, is simple and numerical (i.e. adding up a running total).

One way of tackling the last point would be to write a custom VBA function: to replace SUM in the array formula. Such a function would take an array of values and zeros: so in H13 it would take {0;0;3;7;0;9;0;3;-3;0;4}. We would loop through the array, accumulating changes as we go. We could even have a function that takes more than one array, for different event parameters.

An alternative approach would be to use a ‘time-slicing cursor’ which can be moved forwards and backwards through the event list, calculating the set of extant objects and  their states, for the currently selected ‘time slice’. This cursor could then be the model for a Form that displays the current state in some way. Partly as a change from Excel, and partly because some of the functionality is already there, I’m currently experimenting with doing this using a Recordset in Access. More on this anon.

Advertisements

0 Responses to “Time-Slicing Data”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




June 2009
M T W T F S S
« May   Jul »
1234567
891011121314
15161718192021
22232425262728
2930  

%d bloggers like this: