Extending Excel Functionality with a C# COM Library
Nov 16,
2015
Creating Compiled Code Libraries for Excel Using C# Excel analysts and developers who create useful functions often wish to share their work with coworkers and others. For many applications, creating an Excel macro workbook (xlam file) is sufficient. But for some code, the need for more power requires the development of libraries of compiled code. […]
Excel as a Database: How to Create a Data Model in Excel 2013
Jul 28,
2015
Many blogs, including some originating from our friends in the rainy Pacific Northwest, have referred to the “new” data models in Excel 2013. I’m not sure “new” is quite the right word. “Useful”, absolutely. “Interesting”, most definitely. But what we are really seeing here is not so much new as it is another step in […]
ADO,
Commodities Trade Workbook,
Economic Data,
Excel Data Models,
Excel Power Pivot,
Excel SQL,
Excel VBA,
Linked Tables,
Pivot Tables,
SQL,
VBA
How to Use SQL and the Excel Data Model to Repair Data
Jul 16,
2015
In the previous instalment on querying economic data with SQL we saw a problem not uncommon in the world of worksheets-as-database. Errors and inconsistencies that seem trivial when looking at the data themselves become annoying impediments to creating the pivot table we want. In many cases, the traditional tools of the SQL database are well […]
ADO,
Commodities Trade Workbook,
Economic Data,
Excel Data Models,
Excel Power Pivot,
Excel SQL,
Excel VBA,
Linked Tables,
Pivot Tables,
SQL,
VBA
How to Link Excel to MATLAB to Solve Complex Equations
Apr 28,
2015
Solving Linear System and Linear Optimization Equations Using Microsoft Excel and MATLAB In the previous instalment on linear systems we saw that Excel can easily invert a modest-sized matrix and find solutions to a set of simultaneous linear equations. In the real world, a place I allude to frequently and try to avoid as much as […]
Writing DAX Summary Queries Using PowerPivot and Excel QueryTable Objects
Feb 15,
2015
Don’t get me wrong. Excel PivotTables are one of the best things since pizza and beer. (And considering how much I enjoy that particular combination of snack and beverage, that’s really saying something.) Nevertheless, there are times when you want to take full control, write your own DAX query, and display the results in a […]