Aggregate

def Aggregate(sourceWorksheet, destinationWorksheet, aggColumn, columns, aggColumnName=None):

The Aggregate function provides a convenient way of summarising data from one worksheet into an aggregated overview in another worksheet. It will usually be used in the Post-Formula User Code section of the coding pane and provides a very powerful and flexible way of summarising data.

sourceWorksheet is the worksheet where data will be aggregated from.

destinationWorksheet must be different from the source worksheet and is where the aggregated data will be put into.

aggColumn is the column to aggregate, which can be either the column index, the column name or the value of the header row, if set. If aggColumn is 2 in the source worksheet, then each distinctly different item in column 2 would be aggregated (combined) with other rows containing the same item in column 2 e.g. all the 'a's would be put together, all the 'b's would be put together etc. In the destination worksheet each item only appears once and each different item is in a different row. For example if the items in column 2 of the source worksheets were a,b,c,a,a,b,a,c,c, in the destination worksheet, they will only be a,b,c

columns says what the columns created in the destination worksheet should be called and which functions the columns should use in the aggregation. columns should be a list [in square brackets] containing tuples (in curved brackets). There should be a tuple for each and every column that is to be filled with an aggregation on the destination worksheet. Each tuple should consist of a title for the aggregated column in the destination worksheet and a name for the function that will generate the values in that titled column. Each function called in a tuple needs to be defined before the Aggregate function. Then, when the function is called, there will be a list of rows for every value in the column that is being aggregated on.

aggColumnName is an optional title for the aggregation column on the destination worksheet. It will only be used if there isn't a HeaderRow set on the source worksheet, otherwise the header title from the source worksheet will be used in preference.

Aggregating allows you to present summaries for tables of data that have multiple entries for different values. The output result for each value is arrived at by calling the function(s) you pass in, which is(are) called with a list of all the rows for that value.

As a concrete example, suppose you have a worksheet of stock transactions. This lists all the transactions that have happened in the last week. Each transaction has an associated customer, which stock was traded, whether it was bought or sold, how many were traded, and the price per share.

You might then want to see the nett transactions per customer or per share. Let's take a very simple example where there are only two different accounts '1' and '2' and two different stocks 'MSFT' and 'GOOG'.

'b' represents a buy and 's' represents a sell. We can use the Aggregate function to tell us what the nett transactions were for each account. In this case we are aggregating on account, which is aggColumn "A" (1).

We need a function, that given a list of rows for each account, will work out the nett transactions. The value of each transaction will be the quantity of shares traded (column 4) multiplied by the price per share (column 5). Where the transaction is a sell ('s' in column 3 of the row) we need to add the result to the total, where it is a buy we need to subtract the total.

The following function will be called for every account, and either adds or subtracts the transaction from a running total. The function receives a list of rows ('Row' objects), where each row is a transaction for that account.

def TotalTransactions(rows):
     result = 0
     for row in rows:
         direction = 1
         if row[3] == 's':
             direction = -1
         result += direction * row[4] * row[5]
     return result

We use this function, by placing the following call to Aggregate in the user code:

Aggregate(workbook["Sheet1"], workbook["Sheet2"], "A", [("Total", TotalTransactions)])

The result shown in Sheet2, will be:

By changing the aggregation column to "B", we would see total transaction aggregated by stock rather than by account.

CopyRange

def CopyRange(sourceRange, destinationCell, copyTraits=False, sortCol=None):

CopyRange takes a cell range and copies the contents of the range to another location. The new cell range will have the same dimensions as the source range. You specify the location of the destination by passing in the cell which is at the top left corner (position 1, 1 of the new range).

The sourceRange is the range of cells you want to copy.

The destinationCell is the home cell in the top left-hand corner of the range where you want to copy the data to. If the destination range would cause existing cell contents to be over-written, a ValueError is raised. There is no need for the destinationCell to be on the same worksheet as the original range.

If copyTraits is True (it defaults to False), then traits are copied as well.

If sortCol is passed, the rows in sourceRange will be sorted by this column before being copied into the destination range. It must be a number from 1 to sourceRange.MaxCol.

CopyRange returns the new cell range.

If the resulting cell range would overlap with original data in the destination range, and therefore partly overwrite it, a ValueError is raised.

This function is useful in complex workbooks where various data sets are loaded from different sources and then manipulated on a variety of worksheets for working data. The results of the manipulated data can be displayed in a single summary worksheet by using the CopyRange function. This means that as the values in live data feeds vary, a simple recalculation can be used to copy the values/findings of the data manipulations to a simple summary sheet.

ImplicitConvert

def ImplicitConvert(value, requiredType):

IronPython does not support .NET implicit conversion operators directly. This function converts a value to requiredType, assuming that requiredType is a .NET type which defines an implicit operator allowing you to pass in an object of value's type.

Stringify

def Stringify(arg):

Stringify is used by CONCATENATE to turn non-text values into text, so that you can build up strings from them. It is not intended that you call Stringify directly, but it is used in the generated code when you add items together using the concatenation operator '&'.