Skip to content

database_formulas

shortfx.fxExcel.database_formulas

Excel Database Functions Module.

This module provides Excel-compatible database functions that operate on structured data (list of dictionaries). Functions include: - DCOUNT, DCOUNTA: Count functions - DAVERAGE, DSUM: Aggregate functions - DMAX, DMIN: Statistical functions - DSTDEV, DSTDEVP, DVAR, DVARP: Variance and standard deviation - DPRODUCT: Product calculation - DGET: Single record extraction

All functions follow Excel naming conventions and behavior.

Functions

DAVERAGE(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float

Calculate average of database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to average.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
float float

Average value, 0 if no values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DAVERAGE(db, 'Value', None) 20.0

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
def DAVERAGE(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float:
    """
    Calculate average of database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to average.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        float: Average value, 0 if no values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DAVERAGE(db, 'Value', None)
        20.0

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return sum(values) / len(values) if values else 0

DCOUNT(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> int

Count cells containing numbers in a database field that match criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to count.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
int int

Count of numeric values matching criteria.
Example

db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}] DCOUNT(db, 'Age', None) 2

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
def DCOUNT(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> int:
    """
    Count cells containing numbers in a database field that match criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to count.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        int: Count of numeric values matching criteria.

    Example:
        >>> db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}]
        >>> DCOUNT(db, 'Age', None)
        2

    Cost: O(n) where n is the number of records.
    """
    return len(_get_db_values(database, field, criteria))

DCOUNTA(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> int

Count non-empty cells in a database field that match criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to count.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
int int

Count of non-empty values matching criteria.
Example

db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': None}] DCOUNTA(db, 'Age', None) 1

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
def DCOUNTA(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> int:
    """
    Count non-empty cells in a database field that match criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to count.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        int: Count of non-empty values matching criteria.

    Example:
        >>> db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': None}]
        >>> DCOUNTA(db, 'Age', None)
        1

    Cost: O(n) where n is the number of records.
    """
    count = 0
    for record in database:
        if criteria is None or _evaluate_criteria(record, criteria):
            if field in record and record[field] is not None:
                count += 1
    return count

DGET(database: List[Dict[str, Any]], field: str, criteria: Dict[str, Any]) -> Any

Extract a single value from a database field that matches criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to extract.

 required
criteria Dict[str, Any]

Dictionary with filtering criteria.

 required

Returns:

Name Type Description
Any Any

Field value from the matching record, None if not found.

Raises:

Type Description
ValueError

If multiple records match criteria (Excel behavior).
Example

db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}] DGET(db, 'Age', {'Name': 'Alice'}) 30

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
def DGET(database: List[Dict[str, Any]], field: str, criteria: Dict[str, Any]) -> Any:
    """
    Extract a single value from a database field that matches criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to extract.
        criteria: Dictionary with filtering criteria.

    Returns:
        Any: Field value from the matching record, None if not found.

    Raises:
        ValueError: If multiple records match criteria (Excel behavior).

    Example:
        >>> db = [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}]
        >>> DGET(db, 'Age', {'Name': 'Alice'})
        30

    Cost: O(n) where n is the number of records.
    """
    for record in database:
        if _evaluate_criteria(record, criteria):
            return record[field] if field in record else None
    return None

DMAX(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Optional[Union[int, float]]

Return maximum value from database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Type Description
Optional[Union[int, float]]

Union[int, float, None]: Maximum value, None if no values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DMAX(db, 'Value', None) 30

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
def DMAX(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Optional[Union[int, float]]:
    """
    Return maximum value from database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        Union[int, float, None]: Maximum value, None if no values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DMAX(db, 'Value', None)
        30

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return max(values) if values else None

DMIN(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Optional[Union[int, float]]

Return minimum value from database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Type Description
Optional[Union[int, float]]

Union[int, float, None]: Minimum value, None if no values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DMIN(db, 'Value', None) 10

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
def DMIN(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Optional[Union[int, float]]:
    """
    Return minimum value from database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        Union[int, float, None]: Minimum value, None if no values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DMIN(db, 'Value', None)
        10

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return min(values) if values else None

DPRODUCT(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Union[int, float]

Multiply values from database field that match criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to multiply.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Type Description
Union[int, float]

Union[int, float]: Product of values, 0 if no values.
Example

db = [{'Value': 2}, {'Value': 3}, {'Value': 4}] DPRODUCT(db, 'Value', None) 24

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
def DPRODUCT(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Union[int, float]:
    """
    Multiply values from database field that match criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to multiply.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        Union[int, float]: Product of values, 0 if no values.

    Example:
        >>> db = [{'Value': 2}, {'Value': 3}, {'Value': 4}]
        >>> DPRODUCT(db, 'Value', None)
        24

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    product = 1
    for v in values:
        product *= v
    return product if values else 0

DSTDEV(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float

Calculate sample standard deviation of database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
float float

Sample standard deviation, 0 if less than 2 values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DSTDEV(db, 'Value', None) 10.0

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
def DSTDEV(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float:
    """
    Calculate sample standard deviation of database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        float: Sample standard deviation, 0 if less than 2 values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DSTDEV(db, 'Value', None)
        10.0

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return statistics.stdev(values) if len(values) > 1 else 0

DSTDEVP(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float

Calculate population standard deviation of database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
float float

Population standard deviation, 0 if no values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] round(DSTDEVP(db, 'Value', None), 2) 8.16

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
def DSTDEVP(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float:
    """
    Calculate population standard deviation of database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        float: Population standard deviation, 0 if no values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> round(DSTDEVP(db, 'Value', None), 2)
        8.16

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return statistics.pstdev(values) if values else 0

DSUM(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Union[int, float]

Sum values from database field that match criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to sum.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Type Description
Union[int, float]

Union[int, float]: Sum of values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DSUM(db, 'Value', None) 60

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
def DSUM(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> Union[int, float]:
    """
    Sum values from database field that match criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to sum.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        Union[int, float]: Sum of values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DSUM(db, 'Value', None)
        60

    Cost: O(n) where n is the number of records.
    """
    return sum(_get_db_values(database, field, criteria))

DVAR(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float

Calculate sample variance of database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
float float

Sample variance, 0 if less than 2 values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] DVAR(db, 'Value', None) 100.0

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
def DVAR(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float:
    """
    Calculate sample variance of database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        float: Sample variance, 0 if less than 2 values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> DVAR(db, 'Value', None)
        100.0

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return statistics.variance(values) if len(values) > 1 else 0

DVARP(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float

Calculate population variance of database field values matching criteria.

Parameters:

Name Type Description Default
database List[Dict[str, Any]]

List of dictionaries representing database records.

 required
field str

Field name to analyze.

 required
criteria Optional[Dict[str, Any]]

Optional dictionary with filtering criteria.

 None

Returns:

Name Type Description
float float

Population variance, 0 if no values.
Example

db = [{'Value': 10}, {'Value': 20}, {'Value': 30}] round(DVARP(db, 'Value', None), 2) 66.67

Cost: O(n) where n is the number of records.

Source code in shortfx/fxExcel/database_formulas.py 
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
def DVARP(database: List[Dict[str, Any]], field: str, criteria: Optional[Dict[str, Any]] = None) -> float:
    """
    Calculate population variance of database field values matching criteria.

    Args:
        database: List of dictionaries representing database records.
        field: Field name to analyze.
        criteria: Optional dictionary with filtering criteria.

    Returns:
        float: Population variance, 0 if no values.

    Example:
        >>> db = [{'Value': 10}, {'Value': 20}, {'Value': 30}]
        >>> round(DVARP(db, 'Value', None), 2)
        66.67

    Cost: O(n) where n is the number of records.
    """
    values = _get_db_values(database, field, criteria)
    return statistics.pvariance(values) if values else 0