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 / 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()
@ncalm
ncalm / WorkbookReport.ts
Created November 5, 2024 16:19
This Office Scripts script can be used in Excel to produce a report on workbook structure and objects
function main(
workbook: ExcelScript.Workbook,
outputRange: string = "A1",
newSheetName: string = "Sheet Report"
) {
const sheets = workbook.getWorksheets();
// Create or clear the report sheet
let thisSheet = workbook.getWorksheet(newSheetName);
if (thisSheet) {
@ncalm
ncalm / example-data.txt
Created October 29, 2024 21:13
Get field members
{"OrderDate","Category","SubCategory","Product","SalesAmount","OrderQuantity";40541,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Road Bikes","Road-650 Black, 62",699.0982,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40542,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40542,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40542,"Bikes","Mountain Bikes","Mountain-100 Black, 48",3374.99,1;40542,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-650 Red, 52",699.0982,1;40543,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 44",35
@ncalm
ncalm / excel-lambda-FINITE_DIFF.txt
Created October 25, 2024 15:37
This Excel LAMBDA function computes the finite difference of a vector
/*
FINITE_DIFF function
array is a vector (one column or one row)
1) Check if array is a vector. If not, return #VALUE!
2) Multiply array by similarly sized array of {1, -1, ..., -1}.
3) REDUCE SUM over (2)
@ncalm
ncalm / excel-lambda-resample.txt
Created October 2, 2024 00:31
These Excel LAMBDA functions mimic the DataFrame.resample method from the Pandas library
FIRST = LAMBDA(arr, INDEX(arr, 1, 1));
LAST = LAMBDA(arr, INDEX(arr, 1, 1));
RESAMPLE = LAMBDA(rule,
LAMBDA(datetime_index, values, functions,
LET(
week_day, WEEKDAY(datetime_index, 2),
date_year, YEAR(datetime_index),
group_function, SWITCH(
LOWER(rule),
@ncalm
ncalm / library_list.py
Created September 19, 2024 13:27
Use this short script in Python in Excel to spill the list of available libraries to the grid
import subprocess
result = subprocess.run(['pip', 'list'], capture_output=True, text=True)
lines = result.stdout.splitlines()[2:]
[tuple(line.split()[:2]) for line in lines]
@ncalm
ncalm / excel-lambda-StackEveryNColumns.txt
Last active August 31, 2024 20:15
Excel LAMBDA for taking a range of repeated columns, trimming each set and stacking them on top of each other
/*
For a single data with sets of 'every' columns with differing counts of rows,
trim each set and stack them on top of each other
Dependencies:
STACKER:
https://gist.github.com/ncalm/ef7ed953571eec1475c291948aa2dbc3
EveryXtoN:
https://gist.github.com/ncalm/48b96ac45685a7897fdf0a7336b2e96b
@ncalm
ncalm / excel-lambda-EveryXtoN.txt
Created August 31, 2024 19:58
LAMBDA for generating an arbitrary skipped list of integers
/*
Return every 'x' integers no larger than n, optionally skipping the first 'skip'
integers in such a sequence
e.g.
EveryXtoN(10, 2) = {1; 3; 5; 7; 9}
EveryXtoN(10, 2, 1) = {3; 5; 7; 9}
EveryXtoN(10, 2, 2) = {5; 7; 9}
EveryXtoN(10, 3) = {1; 4; 7; 10}
Sub GetThemeColorsForPythonPalette()
Dim i As Integer
Dim colorHex As String
Dim pythonCode As String
Dim colorsArray() As String
Dim themeColor As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name = "Theme Colors" Then
@ncalm
ncalm / excel-lambda-EMAILVARIANTS.txt
Last active August 20, 2024 17:52
This Excel LAMBDA function builds a regex pattern of variants of the local part of an email address when given a Full Name
/* Takes a name in First Last format and produces a regex string
of variants of that name that might be found before the @ in an email
address.
Optional parameters can be set to FALSE to exclude specific variants
*/
EMAILVARIANTS = LAMBDA(
name, [firstlast], [finitlast], [firstlinit],
[firstdotlast], [finitdotlast], [firstunderlast],
LET(