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 of the same dimensions. You specify the destination by passing in the cell which is at the top left corner (position 1, 1 of the new range). The function returns the new cell range and can be assigned to a variable.

sourceRange

The range of cells you want to copy.

destinationCell

The home cell in the top left-hand corner of the destination range. This can be in a different worksheet. If the setting would overwrite existing data, a ValueError is raised.

copyTraits

If True, then traits are copied as well.

sortCol

Sort on this column based on the values in the corresponding column in the source. This must be an integer between 1 and the number of columns in the range.

CopyRange(sourceRange, destinationCell)

CopyRange(sourceRange, destinationCell, True)

CopyRange(sourceRange, destinationCell, sortCol=3)

CopyRange(sourceRange, destinationCell, False, 3)

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 '&'.