Tired of Excel's VLOOKUP? Use SQL queries on Excel ranges!

TL;DR

If you have the free xlwings Excel add-in installed, you can query your data using standard SQL statements, like this:

READ MORE >

Unit Tests for Microsoft Excel

Many (business) users use Excel to create full blown applications. However, unlike applications developed by professional software developers, Excel tools fall short of most software development practices that are considered minimal standards. This introduces risks that can lead to reputational damages and substantial financial losses (remember the «London Whale»).

This blog post introduces the concept of test-driven development before it shows you how to write an automated unit test for a VBA function.

READ MORE >

How to fetch Market Data in Excel like a Pro

Tell a financial analyst to download market data into Excel, and the odds are that they will start typing the following formula in an Excel spreadsheet: =BDH("TICKER", "PX_LAST",...). This formula stands for “Bloomberg Data History” and will download the end-of-period prices of the indicated ticker directly into your spreadsheet - given that you have Bloomberg’s Excel addin connected to a Bloomberg terminal.
This blog post post is about creating such a formula with xlwings that you can attach to any data source like Quandl, Yahoo! Finance or Google Finance.

READ MORE >

"pip -t": A simple and transparent alternative to virtualenv

Often, virtualenv is overkill for the basic task of installing project dependencies and keeping them isolated. We present a simple alternative consisting of:

  1. adding ./.pip to your PYTHONPATH
  2. using pip install -t .pip to install modules locally
  3. executing python from your project’s root directory
READ MORE >

Build your own Google Analytics Dashboard in Excel for free!

This post is about building a Google Analytics Dashboard in Excel (the place where the data is ending up sooner or later anyway). To do this, we’ll be using Python with the Pandas and xlwings packages.

READ MORE >