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
65 followers · 20 following
View GitHub Profile
@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}
@ncalm
ncalm / GetThemeColorsForPythonPalette.vb
Last active August 23, 2024 23:59
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(
@ncalm
ncalm / excel-lambda-partition.txt
Created August 15, 2024 14:09
These Excel LAMBDA functions partition an array into two thunked parts
/*
axis is (default) 0 for rows 1 for columns
array is a 2D array
at is a position at which to partition the array on the axis
So this returns two thunks, stacked vertically, the first
of which contains the first 5 rows of the array. The second contains the second
5 rows of the array.
PARTITION(0)(SEQUENCE(10,10), 5)
@ncalm
ncalm / excel-lambda-thunk-illustrations.txt
Created August 8, 2024 14:37
This gist contains examples of why thunking can be useful
// This takes several seconds to evaluate
huge_array = MAKEARRAY(10000,5000,PRODUCT);
// By putting the array in a thunk, we can choose not to evaluate it
thunked_huge_array = LET(my_thunk, LAMBDA(MAKEARRAY(10000,5000,PRODUCT)),my_thunk);
// We can evaluate it by putting () at the end of the LET function's return value
evaluated_on_LET_return = LET(my_thunk, LAMBDA(MAKEARRAY(10000,5000,PRODUCT)),my_thunk());
// Or just putting () outside the LET function
@ncalm
ncalm / excel-lambda-module-knn.txt
Last active July 22, 2024 15:24
Imputer = LAMBDA(training_data, k, [distance_function],
LAMBDA(observation,
LET(
// Identify where the missing value is on the observation
_missing, IFERROR(observation="",TRUE),
IF(
/*If there's more than one blank/error in the observation or
if the training data and observation have difference column counts,
then return an error
*/
@ncalm
ncalm / azure-a-translation-codes.m
Created July 20, 2024 20:10
List of language codes for use with Azure AI Language Services and the Excel TRANSLATE function
let
Source = Web.BrowserContents("https://learn.microsoft.com/en-us/azure/ai-services/Translator/language-support"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(1)"}, {"Column2", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(2)"}, {"Column3", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(3)"}, {"Column4", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(4)"}, {"Column5", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(5)"}, {"Column6", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(6)"}, {"Column7", "DIV.table-wrapper.has-inner-focus > TABLE.table.table-sm.margin-top-none > * > TR > :nth-child(7)"}}, [RowSelector="DIV.table-wrapper.has-inner-focus >
@ncalm
ncalm / frequent_users.sql
Created May 29, 2024 20:34
T-SQL solution for a question of "Find users for whom each booking is within 7 days of their most recent booking"
-- https://medium.com/@mail2asimmanna/another-beautiful-sql-question-from-my-business-analyst-interview-1d9fa00c0381
DROP TABLE IF EXISTS #bookings;
SELECT
CAST(u AS smallint) AS userid,
CAST(d AS date) AS booking_date
INTO #bookings
FROM
(VALUES (1,'2024-01-01')