Owen Price ncalm
🎯
ncalm
/ pyexcel_print_globals.py
Created
March 3, 2025 14:36
Print the variables in your global scope in Python in Excel
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)) | |
| ); |
ncalm
/ toggle-python-cells.vb
Created
January 27, 2025 16:55
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
ncalm
/ for_vs_binary.py
Created
January 25, 2025 20:01
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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] |
ncalm
/ excelbi-sum-of-sq-divs-perf-sq.py
Created
January 8, 2025 03:04
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ excel-lambda-TRIMRANGE2.txt
Created
December 11, 2024 19:51
These Excel LAMBDA functions support extending TRIMRANGE to remove intermediary vectors from sparse references
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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
/ 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) { |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {"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 |