The Great Spreadsheet Migration: Moving 20 Years of Data
How we escaped Excel hell and lived to tell the tale
The Announcement That Changed Everything
[Read More]
-
-
The One-Liner That Saved Me 10 Hours a Week
groupby().transform() is the Excel killer you've been waiting for
The Weekly Report Nobody Wanted to Do [Read More] -
5-Minute Fix: Stop Manually Counting Trading Days
Calculating investment holding periods in Python (the easy way)
The Task That Shouldn’t Be Hard (But Is) [Read More] -
When Your Data Speaks Two Languages: Text Parsing Adventures
Your legacy system exports gibberish. Here's how to make sense of it.
The Text File From Hell [Read More] -
The Case of the Missing Millions: Tracking Down Rounding Errors
When your balance sheet doesn't balance by $2.47 million (true story)
The 4:37 PM Email That Ruined My Friday [Read More] -
The Fiscal Year Fiasco: Why Excel Dates Hate Accountants
Q1 starts in July? Q4 ends in June? Excel is judging you right now.
The Email That Broke Me [Read More] -
When VLOOKUP Fails You: A Love Letter to Merge Functions
VLOOKUP has been lying to you. Here's what you should be using instead.
We Need to Talk About VLOOKUP [Read More] -
The Copy-Paste Nightmare: How I Automated 500 Monthly Reconciliations
From 40 hours of soul-crushing tedium to 30 seconds of automation glory
It Was 11:47 PM on a Month-End Night [Read More] -
Quick Tip: One-Line Fiscal Quarter Converter
Government fiscal year? Education fiscal year? Convert any date to fiscal quarters instantly.
The Problem [Read More] -
Quick Tip: Payment Frequency Multipliers in Python
Monthly, semi-monthly, bi-weekly? Convert any payment frequency to annual amounts with one elegant function.
The Problem [Read More] -
End-to-End: From Legacy Text Report to Executive Dashboard
Watch a complete real-world workflow transform messy trial balance data into multi-dimensional analysis in 2 minutes. All patterns combined.
The Complete Challenge [Read More] -
Age Grouping & Binning for Actuarial Reports
Create age brackets (5-year increments, <25, 90+) for pension, insurance, and HR reports. Math meets elegance.
The Age Grouping Challenge [Read More] -
Handle Duplicates Like a Data Pro
Duplicate member records breaking your count? Learn to detect, flag, and deduplicate data correctly for accurate analysis.
The Duplicate Disaster [Read More] -
Reshape Your Data: Melt and Pivot Like a Pro
Data in the wrong shape? Transform between wide and long formats effortlessly. Master the most powerful data reshaping techniques.
The Data Shape Problem [Read More] -
Multi-Year Data Consolidation: From Chaos to Clarity
Combine 5+ years of data from different Excel files with inconsistent formats. Python makes it effortless.
The Multi-Year Analysis Problem [Read More] -
Master Data Mapping: End Classification Chaos
Your team classifies the same account three different ways across different reports. Here's how to establish data governance that actually works.
The Classification Nightmare [Read More] -
String Cleaning & Normalization: Tame Your Messy Data
CUSIPs with missing zeros? Account numbers with inconsistent formatting? Python makes data cleaning effortless.
The Data Quality Nightmare [Read More] -
Merge Excel Lists with SQL Databases (The Safe Way)
Got 500 account numbers in Excel? Pull from database WITHOUT SQL injection risks!
The Problem [Read More] -
Groupby + Transform: The Excel Killer Feature
Calculate running totals within groups. In Excel: nightmare. In Python: one line.
The Problem: Running Totals per Group [Read More] -
Government Fiscal Year Calculations Made Easy
Q1 in July? Fiscal year-end in June? Python handles non-calendar periods effortlessly.
The Fiscal Year Headache [Read More] -
Pivot Tables on Steroids: Multi-Level Analysis in One Line
Excel pivot tables break when you refresh data. Here's what professionals use instead.
The Excel Pivot Table Problem [Read More] -
Replace Nested IF Statements with Python Functions
Excel formula hell: =IF(A2=11,'Fund_A',IF(A2=12,'Fund_B',IF(A2=13... ENOUGH!
The Excel Monster That Haunts Us All [Read More] -
The One-Line Solution to Credit/Debit Notation Nightmares
If you've ever seen '1,234.56 CR' and wanted to scream, this post is for you!
The Universal Accounting Pain Point [Read More] -
Stop Fighting With Excel: Parse Legacy Reports Like a Pro
Does your accounting system export unformatted text files? Transform messy reports into clean data in seconds!
The Problem That Haunts Every Accountant [Read More] -
The Case of the Closest Match
The Case [Read More] -
The Case of the Buried Treasure
The Case [Read More] -
The Case of the Vanishing Data
The Case [Read More] -
The Case of the Time-Traveling Addresses
The Case [Read More] -
The Case of the Strange Negative Balances
The Case [Read More] -
The Case of the Disappearing Dollars
The Case [Read More] -
Streamline Data Analysis With Pythons's Merge_as_of Function
Title: Streamline Data Analysis with Python’s “merge_as_of” Function: Empowering Internal Audit, Financial Reporting, and the Finance Industry [Read More] -
Unveiling The Limitations Of Data Sampling
Title: Unveiling the Limitations of Data Sampling: Empowering Auditors and Finance Professionals with Cutting-Edge Data Services [Read More] -
Streamlining Data From Legacy And Web Based Systems
Title: Streamlining Data from Legacy and Web-Based Systems: Unleashing the Power of Data Analytics [Read More] -
Converting Legacy System Unstructured Documents Into Workable Excel Formats
Title: Unlocking Data Analytics: Converting Legacy System Unstructured Documents into Workable Excel Formats [Read More] -
Extracting Monetary Amount From Text
-
Merge/Join tables on the closest date in time
Merge_asof pandas example
In this post, we demonstrate how to use the merge_asof() function in the Pandas library to merge two dataframes, df1 and df2, where df1 contains names and IDs for certain individuals and df2 contains historical addresses for those same individuals. The goal is to match each person in df1 with a corresponding address in df2 that is closest to a specified cutoff date. [Read More] -
Amount in US dollars/cents extracted from a string
A method for extracting US dollar amounts into a separate column of a dataframe.
If you have an unstructured file with messy strings containing amounts, and you want to extract just the amounts, you might consider the following approach: [Read More] -
Formatting legacy system negative balances
Convert old legacy system balances ending with '-' OR 'CR' to negative values
This post is about formatting negative balances in a legacy system so that they can be easily processed in a modern system. The post provides a method using the pandas library in Python to convert old legacy system balances, which may be formatted with a ‘-‘ or ‘CR’ suffix, to negative values. The method involves reading the legacy system values into a pandas dataframe, creating a mask to identify rows with negative balances, and then using the mask to replace the ‘-‘ or ‘CR’ suffix with a negative symbol. The resulting values are then formatted by removing any commas and... [Read More] -
Locating rows with empty spaces (missing data)
Removing rows with empty values (nan) This code is performing the following operations on a Pandas DataFrame called df: [Read More] -
Assign unique ID to a group in a dataframe
Assign an id that is unique to each element in a group
The purpose of this code is to group the elements in the ‘A’ column of a Pandas DataFrame and assign a sequential ID to each group. The ID is assigned starting with one and increasing by one for each group. [Read More] -
The as of merge - python [pandas]
Merging datasets on the nearest or closest mathing value
This post demonstrates how to use the merge_asof() function in the Pandas library to merge two dataframes based on a shared column and the closest date in time. The function takes in two dataframes, data_set_A and data_set_B, and a shared column, ‘key_column’, and returns a new dataframe that includes all rows from data_set_A and only those rows from data_set_B that match the values in data_set_A. If there is no exact match, the function uses the last row in data_set_B whose ‘key_column’ value is less than the ‘key_column’ value in data_set_A. This type of merge is known as a “backward” search.... [Read More] -
Unique list of items contained in a dataframe column
Create a new column in the dataframe that contains a list of unique items from the 'source' column.
import pandas as pd df=pd.DataFrame({'state':['NY','CA','CT','NJ','NY','FL','FL','CA'],\ 'source':['source_a','source_a','source_a','source_b','source_b','source_b',\ 'source_d','source_c']}) df [Read More]