Excel

Pro Excel secrets and magic. The kind of industry knowledge to put junior finance guys out of business.
Excel
06 Jun 2019

PowerPivot 3: Managing the Data Model

Analyzing ginormous files with Microsoft PowerPivot.
PowerPivot 3: Managing the Data Model

How's it going readers? If you've been paying attention and/or have the basic ability to count, you'll notice that this is the third post in a series about using Excel's secret Weapon of Math Destruction: PowerPivot. I highly suggest that if you haven't read the previous two posts, that you go ahead and do that. If you had, you would already:

  1. Have enabled Powerpivot through the COM add-ins.
  2. Know what Powerpivot is.
  3. Understand why powerpivot can make your life as an analyst a great deal easier.
  4. Be ready to power-up some ginormous flat files.

I'm going to assume that

Continue Reading
Excel
02 Jun 2019

PowerPivot 2: What's the Deal with Delimiters?

Working with large flat files in PowerPivot.
PowerPivot 2: What's the Deal with Delimiters?

Hey there budding Excel wizards, this post picks up RIGHT where my previous post left off, so if you haven't enabled Powerpivot yet, I highly recommend that you read the previous post and enable the add-in before moving forward. Don't worry, we'll wait...

Good to see you again! If you've gotten this far, you've already won half the battle by enabling Powerpivot. To recap, technically PowerPivot is the desktop version of one of the cornerstones of Microsoft's Business Intelligence cloud platform: Power BI. Functionally however, PowerPivot is the answer to dealing with enormous files in a way that they're still

Continue Reading
Excel
25 May 2019

Power to the Pivot Redux: Enter PowerPivot

Dipping into Microsoft's PowerPivot add-on for Excel.
Power to the Pivot Redux: Enter PowerPivot

Well, hello again dear reader, it's been a while. I've been hard at work moving around and summarizing ever expanding datasets using nothing but the wonderful tools offered to me in the Microsoft office suite (and occasionally using SAS Enterprise guide for a very unintended purpose, but that's for a later post...and hopefully, a never post).  I know I promised a post about hardware, and maybe I'll include something about that in my next post. However, there are much more pertinent and useful things at foot, and the truth is, you're probably working on a laptop anyways.

Those of

Continue Reading
Excel
13 Sep 2018

Roll On Through: Create an Iterative List in Excel Without Any Real Coding

This isn't standard programming...THIS IS EXCEL!
Roll On Through: Create an Iterative List in Excel Without Any Real Coding

Hi there dear reader, so, I know that in the previous article that I penned, I stated that I'd be showing you how to create buttons that you can interact with (and I know you know that because you're reading these in order...right?) but I recently had to create something that was so cool, I had to write about it (once you've drank enough data Kool-Aid, you too will find solutions to data manipulation problems cool).

Here's the scene: we've got a list of people, each with a particular issue, however, due to why I needed to put the

Continue Reading
Excel
31 Aug 2018

Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel

Dynamically load data in smart pivot tables.
Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel

First things first, 10 points to anyone who understands what the title is referring to (that's either Charles Atlas' workout philosophy, or one of core tenants of Bokononism in Kurt Vonnegut's standout Cat's Cradle).

Now, with my obscure reference quota filled for the day, let's assume that you've been working as an analyst for some time now. As stated in a previous post, generally you're likely to get promoted into a management position, where you no longer need your formidable skills of analysis, and they too, like all good things, will atrophy to nothingness. However, for those of you who

Continue Reading
Pandas
20 Aug 2018

Importing Excel Datetimes Into Pandas, Part II

Import dates & times from Excel .xlsx files into Pandas!
Importing Excel Datetimes Into Pandas, Part II

What if, like during my data import task a few months back, the dates & times are in separate columns?  This gives us a few new issues.  Let's import that Excel file!

import pandas as pd
import xlrd
import datetime

df = pd.read_excel("hasDatesAndTimes.xlsx", sheet_name="Sheet1")

book = xlrd.open_workbook("hasDatesAndTimes.xlsx")
datemode = book.datemode

And let's see that time variable!

df["Time"]
Index Time
0 0.909907
1 0.909919
2 0.909931
3 0.909942
4 0.909954
df["Time"].map(lambda x: xlrd.xldate_
Continue Reading
Excel
14 Aug 2018

I Owe My Job to Mr. Robot

Entering the Dataverse.
I Owe My Job to Mr. Robot

What's up data gang? If you've been reading along throughout this journey, you'll realize that all of my posts have something in common (other than Excel...smartass), they all assume that you're already working with data...even if it's just cursory exposure. Well, every journey of a thousand worksheets begins with a single lookup, and hopefully this story will help you position yourself to inner-join the fraternity of functions.

For the two of you who went out of the way to read our bios, you may have seen something interesting: I started out as a therapist...a job that does

Continue Reading
Pandas
13 Aug 2018

Importing Excel Datetimes Into Pandas, Part I

Pandas & Excel, Part 1.
Importing Excel Datetimes Into Pandas, Part I

Different file formats are different!  For all kinds of reasons!

A few months back, I had to import some Excel files into a database. In this process I learned so much about the delightfully unique way Excel stores dates & times!  

The basic datetime will be a decimal number, like 43324.909907407404.  The number before the decimal is the day, the number afterwards is the time.  So far, so good - this is pretty common for computers.  The date is often the number of days past a certain date, and the time is the number of seconds.  

So, let's load

Continue Reading
Excel
10 Aug 2018

Active-X Gonna Give it to Ya

Excel Named Ranges and the Active Xs who Love Them.
Active-X Gonna Give it to Ya

Hi there dear reader, we've now reached a crossroads in your ability to use Excel as a means of data analysis. If you've been following along, you've learned how to use basically every tool that an Excel analyst would need to have in the front of their minds. For those just joining us, these tools are as follows:

  • Pivot Tables (both the report layout and the tabular layout)
  • Lookup Functions
  • IF + AND + OR statements
  • And the combination of these three tools (and if you're particularly efficient, you may have dabbled in some VBA here and there)

However, if you have

Continue Reading
Excel
21 Jul 2018

Your Invitation to the Excel Pivot Party

Becoming well-versed in pivot tables to the point where it’s a problem.
Your Invitation to the Excel Pivot Party

I know its been a while, but you'd be surprised how little time an energy you have when you're building a plane while you're flying it...or perhaps, if you've been reading along with this series, you won't.

Before jumping right into the deep end and showing you some lesser known (but supremely useful) tricks to save even more time (which you'll no doubt use to implement new tricks to save more time, it's a vicious cycle), allow me to introduce the downside of dabbling in the mystic arts:

One of the issues that nearly any overly competent technical analyst

Continue Reading
Excel
05 Jul 2018

Slicing Excel: The Way of The Knife

Slicing your Excel data to be presentable for the less tech-savvy.
Slicing Excel: The Way of The Knife

If you've been following along with the posts in this series, and you've been putting some of the scenarios into practice, you've now crossed a crucial juncture in your never ending quest to master the bloated beast that is Excel. To put things in perspective, if you've conquered all the quests that I've set you upon, you should now be able to:

* Open up and add macro code into an Excel Worksheet
* Utilize lookup functions (particularly Index(Match)) to compare and validate data sets
* Build a complex formula
* Utilize IF/AND/and OR statements in order to manipulate data and

Continue Reading
Excel
20 Jun 2018

Yes...If(And)): Excel’s Mighty “IF” Statement

A deep dive into Excel’s implementation of IF statements and their significance.
Yes...If(And)): Excel’s Mighty “IF” Statement

If you've been following along on our proverbial Hogwarts for budding Excel wizardry, you would know that we recently crossed an important Rubicon upon which all Excel, and indeed all computer programming languages are built: The mighty IF statement. Remember, the IF statement allows the wielder to fork reality at their whim, to bend the code on a lark, to be the Franklin Richards of the spreadsheet just by asking the computer a simple true/false question. Considering the gravity of this momentous revelation, I implore you to read the previous statement in order to join us in enlightenment.

As

Continue Reading
Excel
10 Jun 2018

Getting Iffy With it: Conditional Statements in Excel

Effectively utilize conditionals such as IF statements in your Excel workflow.
Getting Iffy With it: Conditional Statements in Excel

If you've been following along, we discussed in the last several posts of this series how, if you're not working in a very "tech forward" organization (like my two compatriots on his site), but you have the same title, you're probably obtaining your data from another department (or it might be a sentient sponge, or a gang of squirrels with dreams of world domination, you'll actually have no idea) who you will have no contact with. As a side effect of this...rather strange situation (ya know, like being a child and being abandoned briefly by your mother in a

Continue Reading
Excel
05 Jun 2018

Taking Out the Trash: Dirty Data in Excel

Dealing With Dirty Data in Excel (continued).
Taking Out the Trash: Dirty Data in Excel

In my last post, we explored the organizational structure of many large companies and how this pertains to one's duties as a fledgling data analyst. I highly recommend you go back and read the first post on "dirty" data, but just in case you're one of those rebels who thinks that they're too cool to read part 1, here's a quick refresher to put you back in the analytical mindset (which is the perfect combination tactical laziness, ADHD, and a complete disregard for downside risk).

Essentially, the reason why most data analysts exist within a company (remember, NOT a tech

Continue Reading