🔐 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]
Recent Posts
Explore practical guides, tutorials, and insights on data analytics and automation in finance and accounting. Each post tackles real challenges with actionable solutions.
Topics We Cover
Data Analytics - Transform raw data into actionable insights
Process Automation - Eliminate repetitive tasks and reduce errors
Tools and Techniques - Practical implementations using Python, SQL, and modern platforms
Industry Trends - What’s actually changing in the profession
Career Development - Skills and strategies for staying relevant
Browse the latest posts below:
-
-
🔄 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]