Skip to content

Instantly share code, notes, and snippets.

View ncalm's full-sized avatar
💭
hitting computer with hammer

Owen Price ncalm

💭
hitting computer with hammer
View GitHub Profile
@ncalm
ncalm / excel-geography-properties.py
Created March 29, 2025 18:58
Function to extract properties from Geography RichValue type in Python in Excel
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"):
def portfolio_analysis(data: UDF.Range, has_dates: bool = True, price_type: str = "Close",
annual_factor: int = 252, custom_weights: list = None, risk_free_rate: float = 0.0) -> UDF.Range:
"""
Calculates key portfolio metrics from stock price data.
Args:
data: Daily stock prices. If has_dates=True, the first column should be dates.
has_dates: Whether the first column contains dates (True) or is price data (False).
price_type: String indicating price type used ("Close" or "Adj Close").
annual_factor: Number of trading days per year, defaulting to 252 for daily data.
@ncalm
ncalm / pyexcel_print_globals.py
Created March 3, 2025 14:36
Print the variables in your global scope in Python in Excel
import inspect
begin_print = False
module_print = True
function_print = True
for key, value in list(globals().items()):
if begin_print and not key.endswith("_print"):
# print imported module names if requested
if module_print and inspect.ismodule(value):
@ncalm
ncalm / excel-lambda-DATE.EXP.txt
Created February 24, 2025 14:44
This Excel LAMBDA function allows us to pass 1, 2 and 3 sequences to the DATE function to produce complex lists of dates
DATE.EXP = LAMBDA(y, m, d,
LET(
mdJoin, CROSSJOIN(m, d),
ymdJoin, CROSSJOIN(y, mdJoin),
yearArray, CHOOSECOLS(ymdJoin, 1),
monthArray, CHOOSECOLS(ymdJoin, 2),
dayArray, CHOOSECOLS(ymdJoin, -1),
SORT(MAP(yearArray, monthArray, dayArray, DATE))
)
);
@ncalm
ncalm / excel-lambda-FILTER.2D.txt
Created February 13, 2025 21:18
This Excel LAMBDA functions enables simultaneous row and column filtering as well as 2D include arrays
IFOMITTED = LAMBDA(arg, then, IF(ISOMITTED(arg),then,arg));
// Collapse a boolean array to a boolean vector
COLLAPSE = LAMBDA(
array,
[collapse_with], // AND (default) or OR
[collapse_to], // 0 (default) = column or 1 = row
IF(collapse_to=0,BYROW,BYCOL)(array,IFOMITTED(collapse_with, AND))
);
Option Explicit
Public Sub TogglePythonCells()
Dim cell As Range
For Each cell In Selection
If Left(cell.Formula2, 3) = "=PY" Then
If cell.HasFormula Then
cell.Formula2 = "'" & cell.Formula2
Else
import numpy as np
import bisect
import timeit
import matplotlib.pyplot as plt
# Initialize list to store results
results = []
# Test for different array sizes
test_values = [1000, 10000, 100000, 1000000, 10000000, 100000000]
import math
from sympy import divisor_sigma, isprime
import time
t1 = time.time()
results = []
n = 1
while len(results) < 50:
if n > 2 and isprime(n):
n += 1
@ncalm
ncalm / excel-lambda-TRIMRANGE2.txt
Created December 11, 2024 19:51
These Excel LAMBDA functions support extending TRIMRANGE to remove intermediary vectors from sparse references
CHECKRANGE = LAMBDA(checkfn,
LAMBDA(directionfn,
LAMBDA(v,
directionfn(v,LAMBDA(x,AND(checkfn(x))))
)));
CHECKISBLANK = CHECKRANGE(ISBLANK);
CHECKBLANKROWS = CHECKISBLANK(BYROW);
CHECKBLANKCOLS = CHECKISBLANK(BYCOL);
TRIMRANGE2 =LAMBDA(sparseRange, [compressOrRemove],
@ncalm
ncalm / KPICard-usage.py
Created November 18, 2024 23:54
This gist shows an example of creating a Power BI-style KPI card using Python in Excel
# Create a KPI card
card = KPICard(figsize=(4, 3))
# Add elements to the card
card.add_title("Revenue")
card.add_value(1234567, prefix="$", suffix="")
card.add_change(12.5) # Will show with arrow
card.add_subtitle("vs. Previous Month")
card.add_border()