Easily update Excel graphs; AutoHotKey saved me 100’s of repetitive clicks

Easily update Excel graphs

Easily update Excel graphs

In market research I used to spend a lot of time making sure my charts and graphs were beautiful visualizations that conveyed the data in an easy to understandable way.

I always wanted to make complete use of the range of the graph and would have to, manually, update each one.  I finally learned some object oriented programming and wrote an script in AutoHotKey to easily change the axis on scales in and easily update Excel graphs.  The below video demonstrates how I can just hit a key combo and scroll up/down to adjust the scale.

Tweaking Graphs in Excel

Using Hotstrings to help utilized custom SPSS scripts

SPSS ScriptsUsing Hotstrings from AutoHotKey help write your code is a great, easy, way to greatly improve your speed!  This example demonstrates me using hotstrings for writing most of which are actually custom SPSS scripts) which typically affect the look of output.  The newer versions of AutoHotKey now utilized Python for their scripting.  Most of mine were written in SAX Basic.  I’d post the code but it is unlikely they will work for you.

Scripts are easy to use and adaptable to make output much more meaningful and in a format ready to be used. AutoHotKey helps write syntax to utilize the scripts.

SPSS Scripts

Custom SPSS Scripts and AutoHotKey


Sample Scripts Library | Raynald’s SPSS Tools

Visual Basic-like language, embedded into SPSS for Windows. Use scripts samples to solve tasks related to interface and output manipulation.

Free SPSS Scripts

Use this script to rename some or all of the variables in your spss dataset. This script uses search and replace for effective renaming. Rename all your variables  …

Regular Expressions to automate metrics is an investment everyone should do!

regular expressions to automate metrics

Regular Expressions

Regular Expressions to automate metrics

I’ve written a script that logs into our vendor website and exports the recent email campaigns then breaks them out by region, campaign type, Business Unit, etc. and links them back to our SharePoint site for more information.  The script relies on a regular expression to automate metrics on our long string name and parse-out sections by examining patterns in the Naming convention.

My script first logs into our tool (SilverPop) and performs web scraping to capture the data and dumps it to a local HTML file.  I then use Excel to import the HTML file and start parsing the naming convention with the regular expression.   After all the columns are created I replace some of the short abbreviations with longer, easier to understand, words.   Then I insert functions aggregating the metrics by Type of campaign, Region, Language etc.   After that I break out four regions.  All in all it takes about 10 minutes to run but saves me about 40 hours worth of work.  The below video demonstrates the regular expressions to automate metrics in action

Automating email metrics

Debugging Python- Have your best debug code at the tip of your fingers

debugging pythonDebugging Python

I’m learning how to program in Python and have put together some decent gui code which can be very helpful when trouble-shooting your code.

Trying to solve issues by just looking at your code can be done but it isn’t nearly as fast as using some real debugging python code.


Helpful Python Debugging Code

AutoHotKey Debugging Python code

There are other tools for debugging Python but this is a good starting place.