Introduction

In Formatted Reports, formulas allow for dynamic calculations directly within the report. Using IronPython, you can set formulas in cells either by referencing relative cell positions or by using named cell references. This flexibility makes it possible to automate calculations across large datasets and create summary calculations that adapt to changes in data.

Example Script for Setting Formulas

The following IronPython script demonstrates two ways to set formulas within a formatted report: using relative references and named cell references.

# IronPython

# Fund level data
data = Qx.ToMatrix([
  ["Fund Code", "Instrument", "Sector", "Holding", "Price", "Market Value", "Weight"],

  # Fund 1 - "AlphaFund"
  ["AlphaFund", "AAPL", "Technology", 50, 150, "", 0.04424],
  ["AlphaFund", "GOOGL", "Technology", 30, 2800, "", 0.4955],
  ["AlphaFund", "AMZN", "Consumer Discretionary", 20, 3300, "", 0.3893],
  ["AlphaFund", "MSFT", "Technology", 40, 300, "", 0.0707],

  # Fund 2 - "BetaFund"
  ["BetaFund", "TSLA", "Automotive", 10, 900, "", 0.2346],
  ["BetaFund", "NFLX", "Communication Services", 15, 540, "", 0.2112],
  ["BetaFund", "NVDA", "Technology", 12, 500, "", 0.1564],
  ["BetaFund", "ADBE", "Technology", 25, 610, "", 0.3976],
  ["", "", "", "", "", "", ""]
])
funds = Qx.MakeStyleSheet(data)

# Naming cells to use in dynamic formulas
funds.Cell(1,5).Name = "FirstMarketValue"
funds.Cell(8,5).Name = "LastMarketValue"

Setting Formulas Using Relative Cell References

In this example, each cell in the “Market Value” column calculates its value based on the holding and price of the respective instrument. Relative references use the Excel RC (Row-Column) syntax to refer to cells to the left.

# Use Excel RC syntax to make formulas that refer to relative cells
# The formula for Market Value is calculated as Holding * Price (two and one cells to the left, respectively)

for i in range(1,9):
    funds.Cell(i,5).Formula = "=RC[-2]*RC[-1]"  # This formula will be converted to standard A1 syntax in Excel

In this script:

• RC[-2] refers to the “Holding” column (two columns to the left).
• RC[-1] refers to the “Price” column (one column to the left).
• The formula calculates each instrument’s Market Value by multiplying Holding by Price.

Setting Formulas Using Named Cell References

Named cell references provide a dynamic way to create formulas that automatically adapt based on the position of named cells in the report. In this example, a summary formula calculates the total Market Value across all instruments.

# Add a dynamic formula to calculate the sum of Market Values
# The double curly brackets {{ }} indicate a substitution with the Excel address of the named cell
funds.Cell(9,5).Formula = "=Sum({{FirstMarketValue}}:{{LastMarketValue}})"

In this example:

• {{FirstMarketValue}} and {{LastMarketValue}} are placeholders that will be replaced by the actual cell addresses in Excel.
• The formula calculates the sum of all Market Value entries from the first to the last instrument listed.

Summary

Using relative cell references and named cell references for formulas in Formatted Reports allows for dynamic, adaptable calculations. Relative references are ideal for row-by-row calculations, while named references are useful for aggregating data across a range. Together, these approaches streamline reporting and ensure accurate calculations that update with changes in the report data.