If you have the free xlwings Excel add-in installed, you can query your data using standard
SQL statements, like this:
What is xlwings?
xlwings is a Python package that allows us to write Python functions instead of VBA code. It recently added a full
blown Excel add-in that brought us “xlwings extensions”:
These are Excel functions that are available as soon as we install the add-in without any more setup steps required. The first built-in
extension is the “In-Excel SQL” extension that brings the power of SQL to your Excel spreadsheets.
Typically, you would use the VLOOKUP formula to connect data from two distinct regions in Excel. Or maybe
you would prefer INDEX/MATCH over VLOOKUP if you are an advanced user. However, the functionality of these formulas is limited and if you have
a lot of these functions, Excel can become quite slow. Often, we were banging our heads against the wall saying “this would be so
easy in SQL!”, well…now it is.
The sql formula
The usage of the sql formula is simple:
=sql("SQL Statement", table a, table b, ...)
You simply have to refer to your tables as a, b etc. in your SQL statement in the order you pass them in.
For more complex queries, it is advisable to write the query into a cell instead of directly in the formula which also allows
you to use multiple lines in your query (by using Alt-Enter for a new line), e.g.
Behind the scenes
xlwings uses an in-memory sqlite database to provide the functionality, so refer to their homepage
for further information regarding the sqlite dialect of SQL.
About Zoomer Analytics
We live and breathe Python, Finance and Excel: We created xlwings, the most popular open-source package
to connect Excel with Python. xltrail is our next generation git platform (version control system) that
tracks changes in Excel cells and VBA code. Other than that, we develop web based systems as consultants using Python, Bloomberg
and other systems typically used by financial companies like hedge funds.