ncalm

name	three-statement-model-template
description	Creates a polished integrated 3-statement financial model template in Excel with dynamic year labels, Assumptions, Cover, Income Statement, Balance Sheet, Cash Flow, and Checks sheets. Use when asked to build or generate a financial model template.

Three-Statement Financial Model Template

Create a complete Excel financial model with these sheets:

1 Cover

ncalm

REPEAT = LAMBDA(table,row_repeats,[has_header],

    LET(
        // if has_header is zero, FALSE or is omitted, then FALSE, otherwise TRUE
        // long-hand for clarity!
        _has_header,IF(OR(has_header=0,ISOMITTED(has_header),has_header=FALSE),FALSE,TRUE),
        _header,IF(_has_header,TAKE(table,1),NA()), // get the header if it's there
        _table,IF(_has_header,DROP(table,1),table), // redefine the table based on header presence
        _reps,NUMBERVALUE(row_repeats),
        _total,SUM(_reps),

ncalm

/*
Check if one or all values of a list are contained in a text string
txt: the text string to check against
values: a list of substrings, each of which is tested to see if it is found in txt
switch: either List.AnyTrue or List.AllTrue depending on whether you want to check whether at least one of values is contained in txt or if all values are contained in txt
*/
(txt as text, values as list, switch as function) as logical =>
  let
      check_values_non_empty = 
          if List.IsEmpty(values)

ncalm

={"C","L","Q","C","I","Y","O","G","K","P","P","I";"R","H","O","L","L","Y","A","P","H","T","V","F";"Q","P","R","F","U","L","K","P","E","V","J","E";"F","R","Q","I","E","S","X","R","E","M","Q","A";"G","V","X","O","S","J","L","K","T","A","Y","R";"F","T","N","L","H","T","C","O","O","D","C","S";"J","O","L","L","Y","A","M","J","H","C","M","E";"E","N","O","E","R","G","S","A","N","T","A","F";"R","L","A","N","D","I","T","N","S","D","P","Q";"T","P","V","U","K","F","D","G","N","D","H","T";"C","H","M","E","O","T","B","E","L","L","S","P";"W","Q","D","R","S","S","F","L","P","Y","M","X"}

ncalm

df = xl("A2:G31").astype(str)
# set the column names from the first group
df.columns = df.loc[1,:]

# create initial region and quarter columns
fill = lambda s, d: s.where(s.str.contains(d), np.nan).ffill()
df['Region'] = fill(df['Category'], ':')
df['Quarter'] = np.where(fill(df['Jan-Sales'], '-')=='Jan-Sales','Q1','Q2')

# remove unwanted rows

ncalm

# https://owenprice.pyscriptapps.com/excel-challenginator/latest/

import pandas as pd
df['Date'] = pd.to_datetime(df.Data, errors='coerce').ffill()
display(df.head(3), 'After creating Date column (first 3 rows)')

df = df.loc[df.Data!=df.Date]
display(df.head(3), 'After removing date-only rows (first 3 rows)')

df = pd.concat([

ncalm

You are a data cleaning assistant. I will give you a list of messy addresses. Standardize them into a clean table with the following columns: street address, city, state, postal code, country

Rules:
- Use appropriate capitalization.
- Always spell out the full name of a state or province. Do not use abbreviations. 
- Remove non-city locality descriptors (e.g., downtown, midtown, metro area, greater, borough of, city of). Do not place them in the city or street address.
- If a city token includes directional prefixes/suffixes (SE, NW, North, South), discard those and return only the clean city name; do not attach the markers to the street address.
- If the city is missing and a postal code is available, infer the city from the postal code.
- If the state/province is missing but the street address and city are available, infer the state/province.
- If a state/province is present but the country is missing, infer the country from it (e.g., Ontario → Canada; TX/CO/AL → United States).

ncalm

// Simple frequency table using COUNTA
FREQTBL = LAMBDA(dimension, GROUPBY(dimension, dimension, COUNTA,,0));

// Alternate version using EXPAND
FREQTBL_alt = LAMBDA(dimension, GROUPBY(dimension,EXPAND(1,ROWS(dimension),,1),COUNT,,0));

DESCRIBE = LAMBDA(data,
  LET(arepl, REPLICATE(VSTACK,ROWS(data)),
    LAMBDA(statistic, 
      GROUPBY(arepl(statistic),

ncalm

Here's an example of how this pyscript app works in the browser:

https://owenprice.pyscriptapps.com/pq-regex/latest/?txt=abc123def456&pattern=%5Cd%2B

To clone the project and modify, create an account on pyscript.com and clone this project:
https://pyscript.com/@owenprice/pq-regex/

ncalm

def flatten_geography(rich_value):
    props = rich_value.data.get("properties", {})
    flat = {}

    def extract(val):
        if not isinstance(val, dict):
            return str(val) if val is not None else None

        typ = val.get("type")
        if typ in ("String", "FormattedNumber"):