Excel is a very powerful (and much underestimated) analytical tool that
is as capable (or sometimes more capable) then many more expensive packages.
People view Excel as a spreadsheet program for mass users. It fills this
role quite well but in its capabilities is not a regular consumer program,
it is a powerful professional analytical tool disguised as a commodity spreadsheet.
There are very few good books about Excel programming (and generally
advanced Excel usage). I really hate
John Walkenbach books, the author who actually almost monopolized
the field. He has zero talent as a writer and his books are a mess.
Of course much depends on what are do actually doing with Excel. My experience
is limited to simple financial modeling (although I do use complex recurrent
formulas).
This is an eclectic collection of various recipes for Excel. This is
a strange "lemmings" effect that the book has such a high rating on
Amazon.
The book is essentially a badly written cookbook as it does not provide
underling mechanics and key ideas behind the Excel formulas. Chapters
are more or less disconnected and most of them can be read in any order.
At the end the reader is left with very new good findings that probably
are not worth the price of the book.
Pagecount is very deceptive -- considerable part of the book is fluff
-- brainless reproduction of basic things that one can find in help
and that is not worth even one dollar. Many examples are very trivial
and not worth reading.
Tricks like Appendix B are simply disgusting -- the author just copied
the listing of functions that has no practical value whatsoever to inflate
the pagecount.
Explanations mostly are extremely fuzzy. The author has real talent
to make simple things complex and complex things impossible. Also this
is just "do like I said" type of cookbook: the author never tries to
explain concepts that are used (use of absolute adressing vs relative,
the syntax intricacies of the second argument of countif and similar
functions, etc)
Also the book suffers from frequent references to previous versions
of Excel, which only distract the reader. One can assume that if the
reader really wants to use one of the previous versions of Excel he
can buy prev. edition of the book and save money.
In few places were things became more interesting they are also incomplete
and/or incorrect (creation of your own VBA functions and collection
of functions, usage of array functions like frequency, etc).
My impression is the author is just a book writer and does not have
rich real world experience with Excel, the experience that is necessary
to distinguish between important and trivial things.
As a result he cannot provide the reader any help in getting the grasp
of underling architectural ideas, that are often very non-trivial (Excel
is extremely powerful analytical tool disguised as a commodity spreadsheet).
Announced at the Microsoft Ignite 2021 conference, the initiative is part of an effort to
extend the reach of Power Fx that is already employed within Office 365 to other offerings such
as Microsoft Dataverse, Microsoft Power Automate, and Microsoft Power Virtual Agents.
Power Fx traces its lineage back to a pair of Tangram and Siena projects that ultimately
gave birth to a programming language that was first widely employed by users of Microsoft Excel
spreadsheets.
As much as Excel is a blessing, it is also a curse. When it comes to smallish enough data and simple enough operations As much as
Excel is a blessing, it is also a curse. When it comes to smallish enough data and simple enough operations Excel is king
. Once you find yourself endeavoring outside of those zones however, it becomes a pain. Sure enough, you can use Excel VBA to get
around such issues, but in the year 2020, you can thank your lucky stars because you don't have to! If only there was a way to integrate
Excel with Python to give Excel wings! Well, now there is. A python library called If only there was a way to integrate Excel with
Python to give Excel wings! Well, now there is. A python library called If only there was a way to integrate Excel with Python to
give Excel wings! Well, now there is. A python library called xlwings allows you to call Python scripts through VBA and
pass data between the two .
Why integrate Python with Excel VBA? The truth of the matter is, you can pretty much do anything in VBA. So, if that is
the case, why would you want to use Python? Well, there are a number of reasons.
You can create a custom function in Excel without learning VBA (if you don't know it already)
Your users are comfortable in Excel
You can speed up your data operations significantly by using Python
There are libraries for just about anything in Python (Machine Learning, Data Science, etc)
Because you can!!!
Getting Set Up to Use xlwings The first thing we need to do, as with any new library we want to use, is to install it.
It's super easy to do; with two commands we'll be set up in no time. So, go ahead and type in your terminal: The first thing we need
to do, as with any new library we want to use, is to install it. It's super easy to do; with two commands we'll be set up in no time.
So, go ahead and type in your terminal:
pip install xlwings
Once the library has been downloaded and installed, we need to install the Excel integration part. Ensure you've closed down all
your Excel instances and in any terminal type: Once the library has been downloaded and installed, we need to install the Excel integration
part. Ensure you've closed down all your Excel instances and in any terminal type:
xlwings addin install
Assuming you experience no errors, you should be able to proceed. However, oftentimes on Win10 with Excel 2016, people will see the
following error: Assuming you experience no errors, you should be able to proceed. However, oftentimes on Win10 with Excel 2016,
people will see the following error:
xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'
If you are one of the lucky ones to experience the above error, all you need to do is create the missing directory. You can do that
easily by using the mkdir command. In my case, I did: If you are one of the lucky ones to experience the above error, all you need
to do is create the missing directory. You can do that easily by using the mkdir command. In my case, I did:
Assuming the successful installation of the excel integration with the python library, the main difference you will immediately notice
is in Excel: Assuming the successful installation of the excel integration with the python library, the main difference you will
immediately notice is in Excel:
Enabling User Defined Functions for xlwings First up, we need to load the Excel Add-in. You can hit Alt, L, H and then
navigate to the directory above to load the plugin. Once you're done, you should be able to see the following: First up, we need
to load the Excel Add-in. You can hit Alt, L, H and then navigate to the directory above to load the plugin. Once you're done, you
should be able to see the following:
Finally, you need to Enable Trust access to the VBA project object model. You can do that by navigating to File > Options > Trust
Center > Trust Center Settings > Macro Settings: Finally, you need to Enable Trust access to the VBA project object model. You can
do that by navigating to File > Options > Trust Center > Trust Center Settings > Macro Settings:
Photo by Pakata Goh
on Unsplash Getting Started with xlwings There are two main ways you can go from Excel to Python (and back). The first one is to call
a Python script directly from VBA, while the other one is through a User Defined Function. Let us have a quick look at both. In order
to avoid any confusion and to have the correct set up every time, xlwings offers to create your Excel spreadsheet, ready to go. Let
us then use this functionality. Using the terminal, we navigate to the directory we like and type: There are two main ways you can
go from Excel to Python (and back). The first one is to call a Python script directly from VBA, while the other one is through a
User Defined Function. Let us have a quick look at both. In order to avoid any confusion and to have the correct set up every time,
xlwings offers to create your Excel spreadsheet, ready to go. Let us then use this functionality. Using the terminal, we navigate
to the directory we like and type: In order to avoid any confusion and to have the correct set up every time, xlwings offers to create
your Excel spreadsheet, ready to go. Let us then use this functionality. Using the terminal, we navigate to the directory we like
and type: In order to avoid any confusion and to have the correct set up every time, xlwings offers to create your Excel spreadsheet,
ready to go. Let us then use this functionality. Using the terminal, we navigate to the directory we like and type:
xlwings quickstart ProjectName
I am calling this MyFirstPythonXL. The above command will create a new folder in your pre-navigated directory with an Excel worksheet
and a python file. I am calling this MyFirstPythonXL. The above command will create a new folder in your pre-navigated directory
with an Excel worksheet and a python file.
Opening the .xlsm file, you immediately notice a new Excel sheet called Opening the .xlsm file, you immediately notice a new Excel
sheet called _xlwings.conf . Should you wish to override the default settings
of xlwings, all you have to do is rename this sheet and remove the starting underscore. And with that, we are all set up and ready
to begin using xlwings.
VBA to Python Before we jump into the coding, let us first ensure we are all on the same page. To bring up our Excel VBA
editor, hit Before we jump into the coding, let us first ensure we are all on the same page. To bring up our Excel VBA editor, hit
Alt + F11 . This should return the following screen:
VBA editor with xlwings
The key things to note here is that this code will do the following: The key things to note here is that this code will do
the following:
Look for a Python Script in the same location as the spreadsheet
Look for a Python Script with the same name as the spreadsheet (but with a .py extension)
From the Python Script, call the function "main()"
Without any further ado, let us look at a few examples of how this can be used. Without any further ado, let us look at a few examples
of how this can be used. Example 1: Operate Outside of Excel, and return the Output In this example, we will see how you carry
operations outside of Excel, but then return your results in the spreadsheet. This can have an infinite amount of use cases. We will
source data from a CSV file, do a modification on said data, and then pass the output to Excel. Let's review how easy it is: First
up, the VBA code : I have left this completely unchanged from the default. Then, the Python code : In this example, we will see how
you carry operations outside of Excel, but then return your results in the spreadsheet. This can have an infinite amount of use cases.
We will source data from a CSV file, do a modification on said data, and then pass the output to Excel. Let's review how easy it
is: First up, the VBA code : I have left this completely unchanged from the default. Then, the Python code : We will source data
from a CSV file, do a modification on said data, and then pass the output to Excel. Let's review how easy it is: First up, the VBA
code : I have left this completely unchanged from the default. Then, the Python code : We will source data from a CSV file, do a
modification on said data, and then pass the output to Excel. Let's review how easy it is: First up, the VBA code : I have left this
completely unchanged from the default. Then, the Python code : First up, the VBA code : I have left this completely unchanged from
the default. Then, the Python code : First up, the VBA code : I have left this completely unchanged from the default. Then, the Python
code : I have left this completely unchanged from the default. Then, the Python code : I have left this completely unchanged from
the default. Then, the Python code : Then, the Python code : Then, the Python code :
Which results in the following: Which results in the following:
xlwings in action
Example 2: Use Excel Inputs to Drive the Operations In this example, we will read inputs from Excel, do something with it
in Python, and then pass the result back to Excel. More specifically, we are going to read a Greeting, a Name and a file location
of where we can find jokes. Our Python script will then take a random line from the file, and return us a joke. First up, the VBA
code : I have left this completely unchanged from the default. Then, the Python code : In this example, we will read inputs from
Excel, do something with it in Python, and then pass the result back to Excel. More specifically, we are going to read a Greeting,
a Name and a file location of where we can find jokes. Our Python script will then take a random line from the file, and return us
a joke. First up, the VBA code : I have left this completely unchanged from the default. Then, the Python code : More specifically,
we are going to read a Greeting, a Name and a file location of where we can find jokes. Our Python script will then take a random
line from the file, and return us a joke. First up, the VBA code : I have left this completely unchanged from the default. Then,
the Python code : More specifically, we are going to read a Greeting, a Name and a file location of where we can find jokes. Our
Python script will then take a random line from the file, and return us a joke. First up, the VBA code : I have left this completely
unchanged from the default. Then, the Python code : First up, the VBA code : I have left this completely unchanged from the default.
Then, the Python code : First up, the VBA code : I have left this completely unchanged from the default. Then, the Python code :
I have left this completely unchanged from the default. Then, the Python code : I have left this completely unchanged from the default.
Then, the Python code : Then, the Python code : Then, the Python code :
import xlwings as xw
import randomdef random_line(afile):
line = next(afile)
for num, aline in enumerate(afile, 2):
if random.randrange(num): continue
line = aline
return line
'Function from: stackoverflowdef main():
wb = xw.Book.caller()
listloc = str(wb.sheets[0].range('B3').value)
fhandle = open(listloc, encoding = 'utf-8')wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a joke for you'
wb.sheets[0].range('A6').value = random_line(fhandle)
Which gives us: Which gives us:
User-Defined Functions with xlwings In pretty much the same fashion as before, we will be changing the code in the
python file. In order to turn something into an Excel User Defined Function, all we need to do is include '@xw.func' before the line
the function is on: The Python code: In pretty much the same fashion as before, we will be changing the code in the python file.
In order to turn something into an Excel User Defined Function, all we need to do is include '@xw.func' before the line the function
is on: The Python code: The Python code: The Python code:
import xlwings as [email protected]
def joke(x):
wb = xw.Book.caller()
fhandle = open(r'C:\Temp\list.csv')
for i, line in enumerate(fhandle):
if i == x:
return(line)
The result: The result:
Conclusion I think you would agree that this is a nifty little library. If you are like me and you much prefer to work
in Python rather than VBA but need to work in spreadsheets, then this can be an exceptional tool. Want to stay up to date with my
blogs? Don't forget to I think you would agree that this is a nifty little library. If you are like me and you much prefer to work
in Python rather than VBA but need to work in spreadsheets, then this can be an exceptional tool. Want to stay up to date with my
blogs? Don't forget to Want to stay up to date with my blogs? Don't forget to Want to stay up to date with my blogs? Don't forget
to follow me !
Microsoft Excel is widely used in almost every industry. Its intuitive interface and ease of
use for organising data, performing calculations, and analysis of data sets has led to it being
commonly used in countless different fields globally.
Whether you're a fan of Excel or not, at some point you will have to deal with it! For many
applications you won't want to do complex calculations or manage large data sets in Excel
itself, but you may need to take values from Excel as inputs, produce reports in an Excel
format, or provide tools to Excel users. Python can be a better choice for complex tasks and
fortunately there are many tools for the Python developer to work with so Excel and Python can
be used together.
This post gives an overview of some of the most popular and useful tools out there to help
you choose which is the right one for your specific application.
Below there's a feature
matrix outlining the different features of the packages for calling Python from
Excel.
Building Interactive Python Tools with Excel as a Front-End
Excel is a well known and really good user interface for many tasks. When you get into more
complex tasks and processing larger datasets however you can soon reach the limits of what can
sensibly be achieved in Excel. Python is a popular choice for data science and other
disciplines as it can handle these complex cases far better than Excel alone. By using both
together and recognising the strengths of each, it's possible for you to build really powerful
interactive tools using Excel as a user-friendly front end, with all the heavy lifting done in
Python.
Python is an extremely powerful language with an extensive ecosystem of 3rd party libraries.
Leveraging Python in Excel spreadsheets can be a fantastic way to enhance your productivity and
remove the need for importing and exporting data into and out of Excel. Interactive worksheets
can be developed using Python code in the same way as you might use VBA, but with all of the
advantages of Python.
There are a few tools available that can be used to bring Python to Excel and it can be
difficult to know which one is right for different situations. Below is an overview of each,
which I hope will highlight the differences between them and help you decide which ones are
right for what you need to achieve.
PyXLL is currently the only package that enables developers to write fully featured Excel
addins in Python. It embeds the Python interpreter into Excel so that it can be used as a
complete VBA replacement. You can think of it conceptually as being similar to something like
Excel-DNA for C#, except that it is dynamic and imports your Python code while Excel is running
– so there's no add-in to build and no need to restart Excel when modifying your Python
code.
PyXLL has a config file (pyxll.cfg) which contains a list of all the modules that will be
imported when Excel starts. By adding the module above to the list in that file, PyXLL will
expose the 'py_test' function to Excel as a user defined function to be called from a
worksheet.
Some additional features of PyXLL are:
Array functions
PyXLL can work with arrays of data and has support for NumPy and Pandas types. Functions
returning arrays can automatically resize to avoid errors when the dimensions of a result
change.
Real Time Data
Stream real time data into Excel from Python with PyXLL's Real Time Data feature.
Object Cache
For functions that return Python objects, rather than simple types (strings, numbers
etc) or arrays (NumPy arrays and Pandas DataFrames or Series) PyXLL has a clever 'object
cache'. Object identifiers are returned, and when passed into another function the
identifier is used to find the original object. This allows objects to be passed between
Python functions using Excel formulas. This can be very useful when dealing with large data
sets where the whole data set doesn't need to be visible in Excel all at once, but instead
is passed between Python functions – for example, loading a large data set and
performing some aggregation operations and presenting the aggregate results in Excel.
Excel Object Model
PyXLL has integration with the main COM packages, pywin32 and
comtypes , which allow the entire Excel Object Model to be used from Excel macros and
functions written with PyXLL. This enables anything that could be done in VBA to be done
in Python. It also integrates with xlwings so that the xlwings API can also be used to
read and write from Excel.
For more features take a look at the feature
matrix below.
The entire Excel API (or Object Model) is exposed via COM. Everything that can be written as
a VBA macro can also be written using the Excel COM API in Python by using pywin32 or
comtypes.
The Excel COM API can be used from outside of Excel (e.g. from a running Python prompt,
script or Jupyter notebook). If you already know how to do something in VBA then doing the
equivalent task in Python via the COM API is generally quite straightforward. Calling a routine
using pywin32 or comtypes from Excel (e.g. from a button on the ribbon bar, menu item or macro)
can be done using PyXLL .
First of all we must get the Excel Application object in Python. This code can be run from
an interactive Python prompt or a Jupyter notebook, or even run inside Excel itself using
PyXLL .
fromwin32com.client.gencacheimportEnsureDispatch# Get the Excel Application COM objectxl=EnsureDispatch('Excel.Application')
Now we have the Application object we can call the Range method in the same way as the VBA
code above. The first important difference to notice is that in VBA simply calling
'Range().Select' calls the Select method, but in Python we need to use '()' to call the
method.
xl.Range('B11:K11').Select()
The next line requires a constant, 'xlFillDefault'. To access the same constant in Python we
use the 'win32com.client.constants' module. Also notice that in VBA no parentheses are used
when calling an object method, but in Python there are.
The translated Python code looks very similar to the original VBA code! Automating tasks in
Excel, or just calling it interactively in this way from a Jupyter notebook can be very
powerful.
This Python code could be called from Excel as a macro using PyXLL's "@xl_macro" decorator. Instead of
using EnsureDispatch, pyxll.xl_app() should be used to
ensure that if there are multiple Excel processes running the correct one is returned.
xlwings provides a wrapper around the Excel COM API described above for
simplifying many common tasks, such as writing Pandas DataFrames to an open Excel workbook. It
uses pywin32's COM wrappers and gives you access to those, so you can always drop down to using
the normal Excel API should you need to.
In the same way as pywin32 and comtypes, xlwings can talk to Excel from a normal Python
prompt or Jupyter notebook. For calling code using xlwings from Excel itself, PyXLL provides a
convenient way of getting the Excel Application object as an
xlwings object . This allows you to script Excel in Python and trigger running your code
from a ribbon button or menu item. An example use-case could be a ribbon button for fetching
data from a database, building a report, and writing it straight into the running Excel.
The following shows how values can be read and written to a running Excel workbook,
including a Pandas DataFrame.
importxlwings as xwwb=xw.Book('workbook.xlsx')# Open an
existing Workbooksheet=wb.sheets['Sheet1']# read and write values from the
worksheetsheet.range('A1').value='Foo'print(sheet.range('A1').value)# Write a Pandas DataFrames directly to the Excel
sheetimportpandas as pddf=pd.DataFrame([[1,2],
[3,4]], columns=['a','b'])sht.range('A1').value=df# Read the DataFrame back,
using the 'expand' option to read the whole tablesht.range('A1').options(pd.DataFrame,
expand='table').value
xlwings includes a way of writing user defined functions (UDFs) or worksheet functions in
Python that are called from a formula in Excel, similar to the user defined functions offered
by PyXLL . These
rely on a server process running outside of Excel and VBA wrappers to call into that server.
It's a simple solution with some drawbacks, such as poor performance and that those functions
are only available from the workbook containing the VBA wrappers.
DataNitro
DataNitro is another API to control Excel from Python. It's not clear what the advantage
over its API and the existing and well understood Microsoft Excel COM API is, but it does allow
you to write and run scripts without leaving Excel. It has rudimentary support for user defined
functions (worksheet functions), but they run outside of the Excel process and only work if
there is only one Excel process running.
DataNitro is no longer under active development and is not available to license any more,
but it was included here for completeness.
Feature Matrix For Integrating Python and
Excel
Feature
DataNitro
xlwings
PyXLL
Comments
Basic worksheet functions
✔
✔
✔
DataNitro and xlwings use an external Python process, xlwings requires VBA wrapper
code
Real time data
✘
✘
✔
Stream real time data into Excel worksheets
Ribbon customisation
✘
✘
✔
Give users a rich user experience with custom ribbon menus
Menu functions
✘
✘
✔
Call Python code from the Excel menu
Object Cache
✘
✘
✔
Pass Python objects between worksheet functions seamlessly via an object cache
For some tasks you may need to read or write an Excel file directly. For batch processing or
tasks running on a server Excel may not be installed. The following packages allow you to read
and write Excel files directly without needing to use Excel.
OpenPyXL
For working with Excel 2010 onwards, OpenPyXL is a great all round choice. Using OpenPyXL
you can read and write xlsx, xlsm, xltx and xltm files. The following code shows how an Excel
workbook can be written as an xlsx file with a few lines of Python.
fromopenpyxlimportWorkbookwb=Workbook()# grab the active
worksheetws=wb.active# Data can
be assigned directly to cellsws['A1']=42# Rows can also be appendedws.append([1,2,3])# Save the filewb.save('sample.xlsx')
Don't confuse OpenPyXL with PyXLL . The two
are completely different and serve different purposes. OpenPyXL is a package for reading and
writing Excel files, whereas PyXLL is a tool
for building fully featured Excel Add-Ins for integrating Python code into Excel.
OpenPyXL covers more advanced features of Excel such as charts, styles, number formatting
and conditional formatting. It even includes a tokeniser for parsing Excel formulas!
One really nice feature for writing reports is its built-in support for NumPy and Pandas
data. To write a Pandas DataFrame all that's required is the included 'dataframe_to_rows'
function:
If you need to read Excel files to extract data then OpenPyXL can do that too. The Excel
file types are incredibly complicated and openpyxl does an amazing job of reading them into a
form that's easy to access in Python. There are some things that openpyxl can't load though,
such as charts and images, so if you open a file and save it with the same name then some
elements may be lost.
A possible downside of OpenPyXL is that it can be quite slow for handling large files. If
you have to write reports with thousands of rows and your application is time-sensitive then
XlsxWriter or PyExcelerate may be better choices.
If you only need to write Excel workbooks and not read them then XlsxWriter is an easy to
use package to use that works well. If you are working with large files or are particularly
concerned about speed then you may find XlsxWriter a better choice than OpenPyXL.
XlsxWriter is a Python module that can be used to write text, numbers, formulas and
hyperlinks to multiple worksheets in an Excel 2007+ XLSX file. It supports features such as
formatting and many more, including:
100% compatible Excel XLSX files.
Full formatting.
Merged cells.
Defined names.
Charts.
Autofilters.
Data validation and drop down lists.
Conditional formatting.
Worksheet PNG/JPEG/BMP/WMF/EMF images.
Rich multi-format strings.
Cell comments.
Textboxes.
Integration with Pandas.
Memory optimization mode for writing large files.
Writing Excel workbooks using XlsxWriter is simple enough. Cells can be written to using the
Excel address notation (eg 'A1') or row and column numbers. Below is a basic example that shows
creating a workbook, adding some data and saving it as an xlsx file.
If you are using Pandas then you'll want to use XlsxWriter's Pandas integration. It takes
the hard work out of writing Pandas DataFrames to Excel, and even creating charts.
importpandas as pd# Create a Pandas dataframe from
the data.df=pd.DataFrame({'Data': [10,20,30,20,15,30,45]})# Create a Pandas Excel writer using XlsxWriter as the
engine.writer=pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')# Get the xlsxwriter objects from the
dataframe writer object.workbook=writer.bookworksheet=writer.sheets['Sheet1']# Create a chart
object.chart=workbook.add_chart({'type':'column'})#
Configure the series of the chart from the dataframe data.chart.add_series({'values':'=Sheet1!$B$2:$B$8'})# Insert the chart into the
worksheet.worksheet.insert_chart('D2',
chart)# Convert the dataframe to an XlsxWriter Excel object.df.to_excel(writer, sheet_name='Sheet1')# Close the Pandas Excel writer and output the Excel file.writer.save()
When referencing the Pandas data in the worksheet (as the formula in the chart above does),
you have to figure out where the data will be in the worksheet so that the formulas point to
the correct cells. For reports involving a lot of formulas or charts this can become
problematic as doing something as as simple as adding an extra row requires adjusting all
affected formulas. For reports like that the package 'xltable' can help.
XLTable is a higher level library for building Excel reports from pandas DataFrames. Rather
than writing the workbook cell by cell or row by row, whole tables are added and can include
formulas that reference other tables without having to know ahead of time where those tables
will be. For more complex reports involving formulas xltable can be very useful.
The main feature that makes xltable more useful than just writing the Excel files directly
is that it can handle tables with formulas that relate to cells in the workbook, without having
to know in advance where those tables will be placed on a worksheet. Therefore only when all
the tables have been added to the workbook and the workbook is being written are formulas
resolved to their final cell addresses.
If you need to write a report that includes formulas rather than just data, XLTable makes it
easier by tracking the cell references so you don't have to construct the formulas by hand and
worry about references changing when tables grow or new rows or columns are added.
fromxltableimport*importpandas as pd# create a dataframe with three
columns where the last is the sum of the first twodataframe=pd.DataFrame({'col_1': [1,2,3],'col_2': [4,5,6],'col_3':
Cell('col_1')+Cell('col_2'),}, columns=['col_1','col_2','col_3'])# create the named xltable Table
instancetable=Table('table', dataframe)# create the Workbook and Worksheet
objects and add table to the sheetsheet=Worksheet('Sheet1')sheet.add_table(table)workbook=Workbook('example.xlsx')workbook.add_sheet(sheet)# write the workbook to the file using
xlsxwriterworkbook.to_xlsx()
XLTable can use either XlsxWriter to write an xlsx file, or it can use pywin32 (win32com) to
write directly to an open Excel application (Windows only). Writing directly to Excel is good
for interactive reports. For example, you could have a button in the Excel ribbon that a user
could press to query some data and produce a report. By writing it directly to Excel they can
get that report immediately in Excel without having it written to a file first. For details of
how to customise the Excel ribbon in Excel see PyXLL: Customizing the Ribbon .
For working with ranges of data and reading or writing them to Excel workbooks with no
frills, using pandas can be a very quick and effective method. If you don't need much in the
way of formatting and just care about getting data into or out of Excel workbooks then the
pandas functions "read_excel" and "to_excel" may be just what you need.
df=pd.DataFrame([('string1',1),('string2',2),('string3',3)],
columns=['Name','Value'])# Write dataframe to an xlsx filedf.to_excel('tmp.xlsx')
For more complex tasks because XlsxWriter, OpenPyXL and XLTable all have Pandas integration
any of those can also be used to write Pandas DataFrames to Excel. But, for just getting data
into Excel using Pandas directly as above is very convenient.
xlrd and xlwt read and write the old Excel .xls files respectively. These are included in
this list for completeness, but are now really only used when you are forced to deal with the
legacy xls file format. They are both extremely mature packages that are very capable and
stable, but xlwt will never be extended to support the newer xlsx/xlsm file formats therefore
for new code dealing with modern Excel file formats they are no longer the best choice.
If you need to deal with multiple file formats (eg xlsx, xls, ods or csv) then pyexcel can
be used to handle all of them. It wraps some of the packages above (xlrd/xlwt, openpyxl and
xlxswriter and others) to give a single consistent API regardless of the file format you are
working with.
The pyexcel packages focuses on data rather than formatting, so if you are looking to
produce high quality reports for Excel users then you should consider the alternatives, but if
you need to extract data from a spreadsheet without worrying so much about the file type then
this package will help you do that.
>importpyexcel as pe>
records=pe.iget_records(file_name='your_file.xls')>forrecordinrecords:...print('%s is aged at %d'%(record['Name'], record['Age']))Adamisaged at28Beatriceisaged at29Ceriisaged at30Deanisaged at26>
pe.free_resources()
There is a set of cross platform Python utilities - called xlrd, xlwt, and xlutils - for
reading & writing Excel files. There are some limitations (e.g. I don't think they can
process macros), but they do allow you to work with Excel files on non-Windows platforms, if
that's of use to you. See: http://www.python-excel.org/
Yes, absolutely. You want to use win32com module, which is part of pywin32 (
get it here ).
I've found you can really simplify Python integration by writing a macro in VBA for Python
to use, and then just have Python call the macro. It will look something like this:
from win32com.client import Dispatch as comDispatch
xl = comDispatch('Excel.Application')
xl.Workbooks.Open("Macros.xls", False, True)
xl.Run("Macros.xls!Macro_1")
I'm sure there are plently of examples on SO... Like this
one .
7
Or have a look at IronPython. IPy is a native .NET implementation of Python 2.6, you can
find it at http://www.codeplex.com/ironpython .
We have used it for several projects. You can use it "from the outside" using COM or - as we
do - write a Excel AddIn with a ScriptHost, which calls out to IronPython code giving you an
environment similar to VBA.
Being a .NET dll, IPy integrates extremely well into the modern Windows .NET stack.
I also really like PTVS from the same dev. team, which provides best debugging in Python
that I experienced so far.
2.
What you can do with VBA + Python is following:
Compile your py scripts that take inputs and generate outputs as text files or from
console. Then VBA will prepare input for py, call the pre-compiled py script and read back
its output.
3.
Consider OpenOffice or LibreOffice which support Python scripts.
This is assuming that available options with COM or MS script interfaces do not satisfy
your needs.
4.
This is not free approach, but worth mentioning (featured in Forbes and New York
Times):
What you end up with is a COM server (don't forget to make it In Process) that can be
created by a call to CreateObject() in VBA. You then call the methods on it as
you do with an COM object created with CreateObject() . You can't single step in
your Python script, but you can intercept logging with win32traceutil from Pywin32
distribution. Works flawlessly.
The following is a step-by-step guide for setting up your developer environment and
getting you started using Python for scripting and automating file system operations on
Windows.
Note
This article will cover setting up your environment to use some of the helpful libraries
in Python that can automate tasks across platforms, like searching your file system,
accessing the internet, parsing file types, etc., from a Windows-centered approach. For
Windows-specific operations, check out ctypes , a C-compatible foreign
function library for Python, winreg , functions exposing the Windows
registry API to Python, and Python/WinRT , enabling access Windows Runtime APIs
from Python. Set up your development environment
When using Python to write scripts that perform file system operations, we recommend you
install
Python from the Microsoft Store . Installing via the Microsoft Store uses the basic
Python3 interpreter, but handles set up of your PATH settings for the current user
(avoiding the need for admin access), in addition to providing automatic updates.
If you are using Python for web development on Windows, we recommend a different setup
using the Windows Subsystem for Linux. Find a walkthrough in our guide: Get started using
Python for web development on Windows . If you're brand new to Python, try our guide:
Get
started using Python on Windows for beginners . For some advanced scenarios (like
needing to access/modify Python's installed files, make copies of binaries, or use Python
DLLs directly), you may want to consider downloading a specific Python release directly
from python.org or consider
installing an alternative , such as Anaconda, Jython,
PyPy, WinPython, IronPython, etc. We only recommend this if you are a more advanced Python
programmer with a specific reason for choosing an alternative implementation.
Install
Python
To install Python using the Microsoft Store:
Go to your Start menu (lower left Windows icon), type "Microsoft Store", select the
link to open the store.
Once the store is open, select Search from the upper-right menu and enter "Python".
Open "Python 3.7" from the results under Apps. Select Get .
Once Python has completed the downloading and installation process, open Windows
PowerShell using the Start menu (lower left Windows icon). Once PowerShell is open,
enter Python --version to confirm that Python3 has been installed on your
machine.
The Microsoft Store installation of Python includes pip , the standard package
manager. Pip allows you to install and manage additional packages that are not part of
the Python standard library. To confirm that you also have pip available to install and
manage packages, enter pip --version .
Install Visual Studio Code
By using VS Code as your text editor / integrated development environment (IDE), you can
take advantage of IntelliSense (a code
completion aid), Linting (helps avoid making errors
in your code), Debug support (helps you find
errors in your code after you run it), Code snippets
(templates for small reusable code blocks), and Unit testing (testing your
code's interface with different types of input).
You will need to install the Microsoft Python extension in order to take advantage of
the VS Code support features. Learn more .
Open the VS Code Extensions window by entering Ctrl+Shift+X (or use the menu to
navigate to View > Extensions ).
In the top Search Extensions in Marketplace box, enter: Python .
Find the Python (ms-python.python) by Microsoft extension and select the green
Install button.
Open the integrated PowerShell terminal in VS Code
VS Code contains a built-in terminal that
enables you to open a Python command line with PowerShell, establishing a seamless workflow
between your code editor and command line.
Open the terminal in VS Code, select View > Terminal , or alternatively use the
shortcut Ctrl+` (using the backtick character).
Note
The default terminal should be PowerShell, but if you need to change it, use
Ctrl+Shift+P to enter the command pallette. Enter Terminal: Select Default Shell and a
list of terminal options will display containing PowerShell, Command Prompt, WSL, etc.
Select the one you'd like to use and enter Ctrl+Shift+` (using the backtick) to create
a new terminal.
Inside your VS Code terminal, open Python by entering: python
Try the Python interpreter out by entering: print("Hello World") .
Python will return your statement "Hello World".
To exit Python, you can enter exit() , quit() , or select
Ctrl-Z.
Install Git (optional)
If you plan to collaborate with others on your Python code, or host your project on an
open-source site (like GitHub), VS Code supports version control
with Git . The Source Control tab in VS Code tracks all of your changes and has common
Git commands (add, commit, push, pull) built right into the UI. You first need to install
Git to power the Source Control panel.
An Install Wizard is included that will ask you a series of questions about settings
for your Git installation. We recommend using all of the default settings, unless you
have a specific reason for changing something.
If you've never worked with Git before, GitHub Guides can help you get started.
Example script to display the structure of your file system directory
Common system administration tasks can take a huge amount of time, but with a Python
script, you can automate these tasks so that they take no time at all. For example, Python
can read the contents of your computer's file system and perform operations like printing
an outline of your files and directories, moving folders from one directory to another, or
renaming hundreds of files. Normally, tasks like these could take up a ton of time if you
were to perform them manually. Use a Python script instead!
Let's begin with a simple script that walks a directory tree and displays the directory
structure.
Open PowerShell using the Start menu (lower left Windows icon).
Create a directory for your project: mkdir python-scripts , then open
that directory: cd python-scripts .
Create a few directories to use with our example script:
Create a new python file in your python-scripts directory:
PowerShell Copy
mkdir src
new-item src\list-directory-contents.py
Open your project in VS Code by entering: code .
Open the VS Code File Explorer window by entering Ctrl+Shift+E (or use the menu to
navigate to View > Explorer ) and select the list-directory-contents.py file that
you just created. The Microsoft Python extension will automatically load a Python
interpreter. You can see which interpreter was loaded on the bottom of your VS Code
window.
Note
Python is an interpreted language, meaning that it acts as a virtual machine,
emulating a physical computer. There are different types of Python interpreters that
you can use: Python 2, Python 3, Anaconda, PyPy, etc. In order to run Python code and
get Python IntelliSense, you must tell VS Code which interpreter to use. We recommend
sticking with the interpreter that VS Code chooses by default (Python 3 in our case)
unless you have a specific reason for choosing something different. To change the
Python interpreter, select the interpreter currently displayed in blue bar on the
bottom of your VS Code window or open the Command Palette (Ctrl+Shift+P) and enter the
command Python: Select Interpreter . This will display a list of the Python
interpreters that you currently have installed. Learn more about configuring
Python environments .
Paste the following code into your list-directory-contents.py file and then select
save :
Python Copy
import os
root = os.path.join('..', 'food')
for directory, subdir_list, file_list in os.walk(root):
print('Directory:', directory)
for name in subdir_list:
print('Subdirectory:', name)
for name in file_list:
print('File:', name)
print()
Open the VS Code integrated terminal ( Ctrl+` , using the backtick character) and
enter the src directory where you just saved your Python script:
Use Python to print that file system directory output to it's own text file by
entering this command directly in your PowerShell terminal: python3
list-directory-contents.py > food-directory.txt
Congratulations! You've just written an automated systems administration script that
reads the directory and files you created and uses Python to display, and then print, the
directory structure to it's own text file.
Example script to modify all files in a
directory
This example uses the files and directories you just created, renaming each of the files
by adding the file's last modified date to the beginning of the filename.
Inside the src folder in your python-scripts directory, create a new Python file for
your script:
PowerShell Copy
new-item update-filenames.py
Open the update-filenames.py file, paste the following code into the file, and save
it:
Note
os.getmtime returns a timestamp in ticks, which is not easily readable. It must be
converted to a standard datetime string first. Python Copy
import datetime
import os
root = os.path.join('..', 'food')
for directory, subdir_list, file_list in os.walk(root):
for name in file_list:
source_name = os.path.join(directory, name)
timestamp = os.path.getmtime(source_name)
modified_date = str(datetime.datetime.fromtimestamp(timestamp)).replace(':', '.')
target_name = os.path.join(directory, f'{modified_date}_{name}')
print(f'Renaming: {source_name} to: {target_name}')
os.rename(source_name, target_name)
Test your update-filenames.py script by running it: python3
update-filenames.py and then running your list-directory-contents.py script
again: python3 list-directory-contents.py
Use Python to print the new file system directory names with the last-modified
timestamp prepended to it's own text file by entering this command directly in your
PowerShell terminal: python3 list-directory-contents.py >
food-directory-last-modified.txt
Hope you learned a few fun things about using Python scripts for automating basic
systems administration tasks. There is, of course, a ton more to know, but we hope this got
you started on the right foot. We've shared a few additional resources to continue learning
below.
Additional resources
Python
Docs: File and Directory Access : Python documentation about working with file
systems and using modules for reading the properties of files, manipulating paths in a
portable way, and creating temporary files.
The
Hitchhikers Guide to Python: Systems Administration : An "opinionated guide" that
offers overviews and best practices on topics related to Python. This section covers
System Admin tools and frameworks. This guide is hosted on GitHub so you can file issues
and make contributions.
I believe the issue is that you're trying to assign a fill object to a style.
ws['A1'].fill = redFill should work fine.
Callam Delaney ,
Thanks Charlie. I'm quite new to Python and the openpyxl docs were not clear about how to
apply the fill to a cell. Your answer has solved the problem. – Ahmed Rashad May 28 '15 at
8:12
The openpyxl.load_workbook() function takes in the filename and returns a value of the
workbook data type. This Workbook object represents the Excel file, a bit like how a File
object represents an opened text file.
Remember that example.xlsx needs to be in the current working directory in order for
you to work with it. You can find out what the current working directory is by importing os and
using os.getcwd() , and you can change the current working directory using os.chdir()
.
Getting Sheets from the Workbook
You can get a list of all the sheet names in the workbook by accessing the sheetnames
attribute. Enter the following into the interactive shell:
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.sheetnames # The workbook's sheets' names.
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # Get a sheet from the workbook.
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # Get the sheet's title as a string.
'Sheet3'
>>> anotherSheet = wb.active # Get the active sheet.
>>> anotherSheet
<Worksheet "Sheet1">
Each sheet is represented by a Worksheet object, which you can obtain by using the square
brackets with the sheet name string like a dictionary key. Finally, you can use the active
attribute of a Workbook object to get the workbook's active sheet. The active sheet is the
sheet that's on top when the workbook is opened in Excel. Once you have the Worksheet object,
you can get its name from the title attribute.
Getting Cells from the Sheets
Once you have a Worksheet object, you can access a Cell object by its name. Enter the
following into the interactive shell:
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1'] # Get a sheet from the workbook.
>>> sheet['A1'] # Get a cell from the sheet.
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # Get the value from the cell.
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # Get another cell from the sheet.
>>> c.value
'Apples'
>>> # Get the row, column, and value from the cell.
>>> 'Row %s, Column %s is %s' % (c.row, c.column, c.value)
'Row 1, Column B is Apples'
>>> 'Cell %s is %s' % (c.coordinate, c.value)
'Cell B1 is Apples'
>>> sheet['C1'].value
73
The Cell object has a value attribute that contains, unsurprisingly, the value stored in
that cell. Cell objects also have row , column , and coordinate attributes that provide
location information for the cell.
Here, accessing the value attribute of our Cell object for cell B1 gives us the string
'Apples' . The row attribute gives us the integer 1 , the column attribute gives us 'B' , and
the coordinate attribute gives us 'B1' .
In this tutorial we work with xlsx files. The xlsx is a file extension for an open XML spreadsheet file format used by
Microsoft Excel. The xlsm files support macros. The xls format is a proprietary binary format while xlsx is based on Office
Open XML format.
$ sudo pip3 install openpyxl
We install
openpyxl
with the
pip3
tool.
Openpyxl create new file
In the first example, we create a new xlsx file with
openpyxl
.
write_xlsx.py
#!/usr/bin/env python
from openpyxl import Workbook
import time
book = Workbook()
sheet = book.active
sheet['A1'] = 56
sheet['A2'] = 43
now = time.strftime("%x")
sheet['A3'] = now
book.save("sample.xlsx")
In the example, we create a new xlsx file. We write data into three cells.
from openpyxl import Workbook
From the
openpyxl
module, we import the
Workbook
class.
A workbook is the container for all other parts of the document.
book = Workbook()
We create a new workbook. A workbook is always created with at least one worksheet.
sheet = book.active
We get the reference to the active sheet.
sheet['A1'] = 56
sheet['A2'] = 43
We write numerical data to cells A1 and A2.
now = time.strftime("%x")
sheet['A3'] = now
We write current date to the cell A3.
book.save("sample.xlsx")
We write the contents to the
sample.xlsx
file with the
save
method.
Figure:
New file
Openpyxl write to a cell
There are two basic ways to write to a cell: using a key of a worksheet such as A1 or D3, or using a row and column
notation with the
cell
method.
write2cell.py
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A1'] = 1
sheet.cell(row=2, column=2).value = 2
book.save('write2cell.xlsx')
In the example, we write two values to two cells.
sheet['A1'] = 1
Here, we assing a numerical value to the A1 cell.
sheet.cell(row=2, column=2).value = 2
In this line, we write to cell B2 with the row and column notation.
Openpyxl append values
With the
append
method, we can append a group of values at the bottom of the
current sheet.
appending_values.py
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
rows = (
(88, 46, 57),
(89, 38, 12),
(23, 59, 78),
(56, 21, 98),
(24, 18, 43),
(34, 15, 67)
)
for row in rows:
sheet.append(row)
book.save('appending.xlsx')
In the example, we append three columns of data into the current sheet.
For the next example, we need to create a xlsx file containing numbers. For instance, we have created 25 rows of numbers in
10 columns with the
RANDBETWEEN()
function.
mystats.py
#!/usr/bin/env python
import openpyxl
import statistics as stats
book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
sheet = book.active
rows = sheet.rows
values = []
for row in rows:
for cell in row:
values.append(cell.value)
print("Number of values: {0}".format(len(values)))
print("Sum of values: {0}".format(sum(values)))
print("Minimum value: {0}".format(min(values)))
print("Maximum value: {0}".format(max(values)))
print("Mean: {0}".format(stats.mean(values)))
print("Median: {0}".format(stats.median(values)))
print("Standard deviation: {0}".format(stats.stdev(values)))
print("Variance: {0}".format(stats.variance(values)))
In the example, we read all values from the sheet and compute some basic statistics.
import statistics as stats
The
statistics
module is imported to provide some statistical functions, such as
median and variance.
book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
Using the
data_only
option, we get the values from the cells, not the formula.
rows = sheet.rows
We get all the rows of cells that are not empty.
for row in rows:
for cell in row:
values.append(cell.value)
In two for loops, we form a list of integer values from the cells.
We compute and print mathematical statistics about the values. Some of the functions are built-in, others are imported with
the
statistics
module.
$ ./mystats.py
Number of values: 312
Sum of values: 15877
Minimum value: 0
Maximum value: 100
Mean: 50.88782051282051
Median: 54.0
Standard deviation: 28.459203819700967
Variance: 809.9262820512821
This is a sample output.
Openpyxl filter & sort data
A sheet has an
auto_filter
attribute, which allows to set filtering and sorting
conditions.
Note that Openpyxl sets the conditions but we must apply them inside the Spreadsheet application.
filter_sort.py
#!/usr/bin/env python
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
data = [
['Item', 'Colour'],
['pen', 'brown'],
['book', 'black'],
['plate', 'white'],
['chair', 'brown'],
['coin', 'gold'],
['bed', 'brown'],
['notebook', 'white'],
]
for r in data:
sheet.append(r)
sheet.auto_filter.ref = 'A1:B8'
sheet.auto_filter.add_filter_column(1, ['brown', 'white'])
sheet.auto_filter.add_sort_condition('B2:B8')
wb.save('filtered.xlsx')
In the example, we create a sheet with items and their colours. We set a filter and a sort condition.
Openpyxl dimensions
To get those cells that actually contain data, we can use dimensions.
dimensions.py
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A3'] = 39
sheet['B3'] = 19
rows = [
(88, 46),
(89, 38),
(23, 59),
(56, 21),
(24, 18),
(34, 15)
]
for row in rows:
sheet.append(row)
print(sheet.dimensions)
print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))
print("Minimum column: {0}".format(sheet.min_column))
print("Maximum column: {0}".format(sheet.max_column))
for c1, c2 in sheet[sheet.dimensions]:
print(c1.value, c2.value)
book.save('dimensions.xlsx')
The example calculates the dimensions of two columns of data.
The example modifies the background colour of the sheet titled "March".
sheet.sheet_properties.tabColor = "0072BA"
We change the
tabColor
property to a new colour.
Figure:
Background colour of a worksheet
The background colour of the third worksheet has been changed to some
blue colour.
Merging cells
Cells can be merged with the
merge_cells
method and unmerged with the
unmerge_cells
method.
When we merge cells, all cells but the top-left one are removed from the worksheet.
merging_cells.py
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.styles import Alignment
book = Workbook()
sheet = book.active
sheet.merge_cells('A1:B2')
cell = sheet.cell(row=1, column=1)
cell.value = 'Sunny day'
cell.alignment = Alignment(horizontal='center', vertical='center')
book.save('merging.xlsx')
In the example, we merge four cells: A1, B1, A2, and B2. The text in the final cell is centered.
from openpyxl.styles import Alignment
In order to center a text in the final cell, we use the
Alignment
class from
the
openpyxl.styles
module.
We get the cell where we show the result of the calculation.
cell.value = "=SUM(A1:B6)"
We write a formula into the cell.
cell.font = cell.font.copy(bold=True)
We change the font style.
Figure:
Calculating the sum of values
Openpyxl images
In the following example, we show how to insert an image into a sheet.
write_image.py
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.drawing.image import Image
book = Workbook()
sheet = book.active
img = Image("icesid.png")
sheet['A1'] = 'This is Sid'
sheet.add_image(img, 'B2')
book.save("sheet_image.xlsx")
In the example, we write an image into a sheet.
from openpyxl.drawing.image import Image
We work with the
Image
class from the
openpyxl.drawing.image
module.
img = Image("icesid.png")
A new
Image
class is created. The
icesid.png
image
is located in the current working directory.
sheet.add_image(img, 'B2')
We add a new image with the
add_image
method.
Openpyxl Charts
The
openpyxl
library supports creation of various charts, including bar charts,
line charts, area charts, bubble charts, scatter charts, and pie charts.
According to the documentation,
openpyxl
supports chart creation within a
worksheet only. Charts in existing workbooks will be lost.
We create a category axis. A category axis is an axis with the data treated as a sequence of non-numerical text labels. In
our case, we have text labels representing names of countries.
Open the file, create a reader, and iterate through it to show the tabular data of all
rows (only three rows are shown):
>>> with open('top_films.csv') as file:
... data = csv.reader(file)
... for row in data:
... print(row)
...
['Rank', 'Admissions\n(millions)', 'Title (year) (studio)', 'Director(s)']
['1', '225.7', 'Gone With the Wind (1939)\xa0(MGM)', 'Victor Fleming, George Cukor, Sam Wood']
['2', '194.4', 'Star Wars (Ep. IV: A New Hope) (1977)\xa0(Fox)', 'George Lucas']
...
['10', '118.9', 'The Lion King (1994)\xa0(BV)', 'Roger Allers, Rob Minkoff']
Open the file and use DictReader to structure the data, including the
header:
>>> with open('top_films.csv') as file:
... data = csv.DictReader(file)
... structured_data = [row for row in data]
...
>>> structured_data[0]
OrderedDict([('Rank', '1'), ('Admissions\n(millions)', '225.7'), ('Title (year) (studio)', 'Gone With the Wind (1939)\xa0(MGM)'), ('Director(s)', 'Victor Fleming, George Cukor, Sam Wood')])
Each of the items in structured_data is a full dictionary that contains each
of the values:
>>> structured_data[0].keys()
odict_keys(['Rank', 'Admissions\n(millions)', 'Title (year) (studio)', 'Director(s)'])
>>> structured_data[0]['Rank']
'1'
>>> structured_data[0]['Director(s)']
'Victor Fleming, George Cukor, Sam Wood'
Understanding why
you may want to use a named range
You don't have to include named ranges in a financial model, and some of the best financial
models don't use them at all. Those who haven't used them before sometimes struggle to see the
benefits of including them in financial models. Most of the time, named ranges aren't really
necessary, but there are a few reasons why you should consider using them in a financial
model:
Named ranges can make your formulas easier to follow. A formula containing lots of
cell references can be confusing to look at and difficult to edit. But if the cell references
are replaced by a range name, it becomes much easier to understand. For example, the formula
=SUM(B3:B24)-SUM(F3:F13) could be expressed as =SUM(Revenue)-SUM(Expenses) to calculate
profit.
Named ranges don't need absolute referencing. By default, a named range is an
absolute reference, so you don't need to add any in.
Using named ranges is ideal when you're linking to external files. When the cell
reference in the source file changes (such as when someone inserts a row), the formula
linking to it will automatically update, even if the file is closed when the update is
made.
If you decide to use macros in your model, you should use named ranges when referring
to cell references in the Visual Basic code. As with external links, this practice is
more robust than using cell references.
Microsoft Excel has been around for over 30 years now, and chances are it's not going to change in
the foreseeable future. In fact, Excel is facing immense competition from challengers such as
Google Spreadsheets and well-funded start-ups like Airtable, which are both going after Excel's
massive user base of approximately 500 million worldwide. Tech-savvy small and mid-sized businesses
embrace innovative alternatives to Excel. However, making a dent in the large enterprise space is a
whole different ballgame. It's nearly impossible to rip and replace Excel along with Office 365 and
the massive underlying infrastructure, which is serving ever-growing workloads. Not that it can't
happen, but it might take decades.
The world runs on Excel, whether we like it or not
I recently spoke to a large consultancy which serves an enterprise with revenues north of 100
billion USD annually: "They run entirely on Excel." as one of the consultancy's top executive
jovially confessed. This is not an exception. Let's take as an example a bank holding trillions of
USD assets: How do they manage their credit risks? The chances are that their thousands of risk
managers are holding one of the most valuable assets buried in hundreds of thousands, if not even
millions of spreadsheets, scattered across the entire organization.
This is indeed no exception. Insurance companies? Pharmaceuticals? Retail? I would make an educated
guess here, that very likely trillions of USD in annual revenues globally literally run through
billions of spreadsheets.
How could things even get this far? Maybe Excel is being misused as a temporary quick-fix which has
never matured into a permanent, enterprise-grade solution.
Enterprise-grade, Mezzogiorno style: Living with temporary solutions for ever
Southern Italy, commonly referred to Mezzogiorno, is amongst the most beautiful, yet poorest and
worst-governed parts of Europe. A good friend of mine, who lives in Sicily, enjoys an enviably vast
number of sunny days, beautiful sceneries and mouth-watering cuisine. To wine aficionados' delight,
some of the world's best wineries are located in this region. On the flip side though, daily life
can be harsh. "This whole region is depending on temporary, quick-fix type of infrastructure, which
is never going to be replaced with permanent solutions. It's a miracle if things don't break." so
said my dear friend over a bottle of red wine from east-Sicily. But what if they do?
It might sound like a gross exaggeration at first glance. But are IT departments acting that
differently from officials in Sicily operating at the verge of possible a collapse? What could go
wrong? A lot. Oracle, historically a life-or-death Microsoft competitor, put together
a
list of biggest business failures due to Excel misuse
. The so-called "London Whale" incident,
for example, caused JPMorgan a whopping 6.5 billion USD in losses and fines. The bank's risk
analysts miscalculated the downside of its synthetic credit portfolio as a result of excessive
formula-shuffling in Excel.
Excel was introduced in 1987. People were using fax machines back then
I bought my first laptop in 1992. It came with a back then great feature: It allowed me to send
word documents via my telephone line to the recipient's fax machine. That was a great invention,
which allowed me as a former copywriter to submit my work to my boss, who would manually review the
printouts and send them back via fax. Excel was five years old back then, and I guess most people
have not even heard of the internet yet.
I started using Excel approximately five years ago, and I quickly turned into a power user. To
Excel's rescue, I have to admit that I am still joyfully using it on a daily basis. It has a wealth
of features, many of which even seasoned Excel users are not aware of. However, we should take a
hard look at Excel: Is this still the all-purpose solution for corporate data? It's certainly not.
Excel's role in the enterprise needs to be redefined
First and foremost: Is Excel the right place to hold mission-critical data? Large enterprises tend
to have highly formalized, very tight IT security policies. However, on the other hand, the misuse
of spreadsheets as the primary engine for multi-billion USD lines of businesses is entirely out of
control. This is very much like owning a house within a gated community, protected by various alarm
systems, and then handing over the keys to your teenage kids over the weekend – leaving a richly
equipped bar unlocked and your jewelry and life's savings in cash stuffed into a cardboard box on
the kitchen table.
However, what other options do business users have?
It's hard to imagine a corporate world without spreadsheets. Very much like it's hard to imagine a
teenage house party without their parents' wine & whiskey collection up for grabs to everyone who
shows up at the doorstep.
Your bank though, in case they manage their trillion USD crown jewels via spreadsheets, does so
quietly. Consultancies helping large enterprises overcome their spreadsheet-addiction act just as
secretively as the Betty Ford Center trying to protect their rich and famous clientele from
reputational damage. The stakes are high.
Python is for data scientists. Is it?
Most companies I came across still bucket their knowledge-working staff into two categories: those
with coding skills and those without them. Due to Evans Data Corporation EDC, there are roughly 23
million software developers worldwide. How many data scientists are out there? One can only
estimate, for example, based on the total Jupyter project user base of presently 3 million people.
Even if we round up the numbers generously to 30 million people with programming skills, this still
represents less than 1% of the world's total adult population. This should ring everyone's alarm
bells. Literally, everyone's.
Lack of programming skills is nothing else but illiteracy
Around the time when book production started to grow, roughly 12% of the adult world population was
able to read and write. Today, around 86% of all adults worldwide are literates. At the same time,
less than 1% of the total adult population is capable of reading and writing code.
"While the earliest forms of written communication date back to about 3,500-3,000 BCE, literacy
remained for centuries a very restricted technology closely associated with the exercise of power."
Ourworldindata.org
Literacy is associated with the exercise of power
The accumulation of wealth in the hands of few individuals and corporations can be well observed in
rankings provided by Forbes and Fortune. An ever-growing amount of fortunes is being made of
technology. However, is there a connection between technology literacy and the accumulation of
wealth and power? There is not much hard evidence to answer that question, as this subject is not
well researched yet.
In spite of a lack of research: What assumptions can we make regarding coding skills in the
business world?
Want to empower business users? Teach them to code
What had happened, if Amazon Alexa was invented in the 16th century, when only less than 12% of the
population was able to read and write? There would probably be little incentive for people to learn
how to communicate in written form. Why invest time and effort into mastering the skill of writing
tiny, little signs on a piece of paper when you can yell: "Alexa! What's the weather gonna be in LA
tomorrow?" Of course, it's convenient. However, convenience comes at a high price. Also, as users
get accustomed to the comfort of yelling and mumbling their commands into a smart device, they grow
increasingly scared of the foundational skill of reading and writing: "It must be very tough to
master "
Is coding in Python hard? Not harder than writing in natural language
Python is a high-level language. It's meant to be readable by humans and not surprisingly most
Python tutorials start with writing print("hello world"). Guess what print("hello world") does? It
prints the two worlds hello world. The process of displaying those two words on your computer
screen is abstracted away. This is why Python is also called a declarative language. You, the
author of your code, declare what you want the program to do: print hello world, perform a
calculation or visualize data. The rest is being taken care of. An imperative programming language
lets you describe the control flow, meaning: You have to write how exactly the program is going to
execute printing hello world, perform a calculation or visualize data. By comparison, this is like
driving a car with an automatic gear shift (declarative) vs. one with a manual gearbox
(imperative).
High level, declarative languages are making programming incredibly easy. Yet, less than 1% of the
world's adult population is capable of writing and reading code. Not surprisingly, businesses
start discovering Python as an alternative to Excel for their mission-critical workloads. JPMorgan
just recently made
headlines
with
their effort to teach their analysts to code, very likely thousands of employees across the entire
organization. Yes, the same JPMorgan which lost 6.5 billion USD in the course of the "London Whale"
disaster. Maybe they learned their lessons that not everything that can be done in Excel should
also be done in Excel?
If you have ever written an Excel function, you can do the same in Python and much more
Getting started with Python is just as easy as picking up Excel. Every Excel user knows how easy it
is to sum up numbers in a column. Just write =SUM and then select the cells you want to sum up. In
case you want to sum up cells which meet specific criteria, you can write an =IF or =IFS statement.
All this can be done in Python as well. As an advanced Excel user, you might ask yourself: "Why
should I learn Python if I already know how to do the same in Excel?" That's an excellent question,
and I kept asking it myself back a few years ago. Here's my answer:
#1 Mastering Excel is harder than wrapping your head around Python
Excel is not just one product, but three products in one:
Excel as you know it with its worksheets and function language
Power Query for mashing up and cleansing data
Power Pivot as the calculation engine for Pivot Tables
If you want to work with spreadsheets, clean up your data and perform advanced calculations on
multiple tables connected into one data model - sure you can do that in Excel (surprisingly, most
advanced Excel users are not aware of that). However, you have to juggle around with three
different UIs and three separate languages: Excel's regular function language, M-Language in Power
Query and DAX (Data Analysis Expressions) in Power Pivot. Oh, did I mention VBA?
I find it more convenient to do all of the above in one language with one consistent syntax and
semantics. By the way, why do I prefer Python over R? Because Python is easier to learn. Ease of
use matters a lot to me.
#2 People around you can understand what you write in Python
Data scientists are often left clueless when business users throw their Excel spreadsheets over the
fence. Even as a business user, it's hard to read and interpret someone else's spreadsheet. Now
imagine a data scientist, who has never worked with Excel spreadsheets.
Many data analysts and data scientists use Jupyter Notebooks. I call it "Word for Geeks" because
Jupyter Notebooks allows you to tell your story in natural language using headlines and body text
like in a Word document, and in addition to that you can write and execute code in the same place.
Once I discovered Jupyter Notebooks, I instantly fell in love with it, because it allows me to
communicate my ideas in natural language based on data I prepare, analyze and visualize in Python –
all in one document. If I hit the wall, I can share my Jupyter Notebook with a data scientist, and
he can just read through my document, understand my data analysis and pick up where I left. Once
he's done with his work, I can read through it and see what he has done.
Business users and data scientists finally united in love!
#3 Python is open source, and the community is very active and creative
I can't think of a data related problem that I would not be able to solve in Python in conjunction
with one of its libraries. Whether it's extracting data from Excel, cleansing data, performing
calculations, visualizing data or utilizing various APIs - there is certainly a package out there.
In a future not so distant from now, I envision analysts within banks, for example, utilizing
Python packages built internally specifically for their proprietary risk assessment. No more murky
Excel functions nobody other than its creator comprehends. Just one repository with one centrally
maintained set of Python packages for the bank's internal risk analysis and assessment.
"But didn't you say open source?" some concerned voices might ask. Open source should not be
misinterpreted as "open door". You can apply any security level you want and thus ensure that your
packages with your proprietary risk models are only available to a selected group of people within
your organization and nobody else.
#4 Utility grade is where Python shines and not Excel
There is a growing number of data analytics and data science PaaS solutions which are built with
petabyte-scale, highly collaborative use cases in mind such as Cloudera Altus, Microsoft Kusto and
T-Systems Data Science Workstation.
If your business requires you to crunch vast amounts of data in near real time, and if you are
increasingly faced with semi-structured and unstructured data, you are better served with Python,
Jupyter Notebooks and a big data cluster within one of the aforementioned PaaS solutions.
#5 You want to keep your job
Due to recent McKinsey research, around 300 million workers need to get retrained to meet the
requirements of job markets by 2030. I wrote two blog posts on transitioning from a line of
business career into data analytics and data science in my previous posts.
Programming is all about automation. The more companies automate their processes, the less they
need to rely on people performing mundane tasks. "Do you want to keep your job?" it all comes down
to this question. If your answer is "Yes," you should consider learning to code. Python is a good
starting point.
I work as a data literacy expert, and I cater to large companies in Europe and the US. Maybe you
have questions I didn't answer in my write-up? Please leave a comment or reach out to me via
email [email protected] or
LinkedIn
.
While this latest DSC podcast isn't about sandwiches, it is related to lunch, specifically the
no free lunch theorem. In short, the theorem states that no algorithm can be equally good at
learning everything, which means that you can't know in advance which algorithm will work best
on your data.
Download
now
.
Most Popular Content on DSC
To not miss this type of content in the future,
subscribe
to
our newsletter.
Comment
by
Ramesh
Gopal
on October 27, 2018 at 7:52pm
Hi Rafael,
I agree with what you're saying, and also what the other commenter has said.
Many consultants, IT experts and even risk managers have pointed out for decades about how fragile
spreadsheets are, and the risks posed to businesses who rely on Excel (or spreadsheets in general),
without adopting proper safeguards.
However, Excel is the ultimate business software tool. Its very weaknesses, which you clearly point
out in your article, are also its enduring strengths. It will be near impossible to dislodge Excel
from its current position as it fills an important niche or gap in enterprises as THE information
tool which runs business workgroups, departments and divisions, especially when corporate IT takes
ages to deliver functionality end-users want.
And trying to teach programming to existing workers isn't likely to succeed in a big way for
several reasons.
One, not everyone has a brain wired to "get" code. Studies have shown that you need a certain type
of abstract thinking capabilities to understand programming.
Two, those in operations who are taught to code will still find coding time-consuming and
frustrating if the skill is not exercised day-in, day-out. Developers do this as part of their
jobs, but operations people have real work to do, in addition to using Excel as a data and model
repository.
Three, daily work pressures and deadlines also mean that enterprises aren't likely to send their
personnel for programming training en masse.
However, the future is bright for the coming generation. They're likely learning programming in
school and such fundamental skills and concepts, learnt at a young age, will typically persist when
they enter the workforce.
So what is called for today is pragmatism, and figuring out how to co-exist with spreadsheets,
while trying to minimize the damage done by spreadsheets gone bad.
Comment
by
Ulf
Morys
on October 14, 2018 at 12:15am
Hi Raffael, great post - and I absolutely second the observations.
One thing, though, merits to be mentioned explixitly: the core of the Excel problem is simply the
fact that, at no point, there is a clear seperation of data from logic in Excel sheets. You see
results - which is usually good. But if you don't see what you expected, it's very tedious to
identify if (a) there really is an unexpected result or (b) there is a problem with the data or
(c) a problem with the logic.
The reason is obvious and simple: any Excel cell may hold either data...or a function and thus
logic.
I have stopped counting the sheets and models I have seen where just a single, accidental
copy&paste or data entry has produced huge confusion and loss of time.
The beauty of Python (...and Pandas, I suppose) in combination with Jupyter Notebook (...we seem
to have fallen for the same solution): You start out with a clear, pure data set, you apply a clear
logic (aka run the data thru the code) and end up with data again.
The approach separates clearly data from logic - and the logic becomes better understandable and
more clearly documented and reproducible with the Notebook.
Really, if I had to express the advantage of Python over Excel in a single statement it would be
just this: Separation of logic from data.
I agree with what you're saying, and also what the other commenter has said.
Many consultants, IT experts and even risk managers have pointed out for decades about how fragile spreadsheets are, and
the risks posed to businesses who rely on Excel (or spreadsheets in general), without adopting proper safeguards.
However, Excel is the ultimate business software tool. Its very weaknesses, which you clearly point out in your article,
are also its enduring strengths. It will be near impossible to dislodge Excel from its current position as it fills an
important niche or gap in enterprises as THE information tool which runs business workgroups, departments and divisions,
especially when corporate IT takes ages to deliver functionality end-users want.
And trying to teach programming to existing workers isn't likely to succeed in a big way for several reasons.
One, not everyone has a brain wired to "get" code. Studies have shown that you need a certain type of abstract thinking
capabilities to understand programming.
Two, those in operations who are taught to code will still find coding time-consuming and frustrating if the skill is not
exercised day-in, day-out. Developers do this as part of their jobs, but operations people have real work to do, in
addition to using Excel as a data and model repository.
Three, daily work pressures and deadlines also mean that enterprises aren't likely to send their personnel for programming
training en masse.
However, the future is bright for the coming generation. They're likely learning programming in school and such fundamental
skills and concepts, learnt at a young age, will typically persist when they enter the workforce.
So what is called for today is pragmatism, and figuring out how to co-exist with spreadsheets, while trying to minimize the
damage done by spreadsheets gone bad.
Hi Raffael, great post - and I absolutely second the observations.
One thing, though, merits to be mentioned explixitly: the core of the Excel problem is simply the fact that, at no point,
there is a clear seperation of data from logic in Excel sheets. You see results - which is usually good. But if you don't
see what you expected, it's very tedious to identify if (a) there really is an unexpected result or (b) there is a problem
with the data or (c) a problem with the logic.
The reason is obvious and simple: any Excel cell may hold either data...or a function and thus logic.
I have stopped counting the sheets and models I have seen where just a single, accidental copy&paste or data entry has
produced huge confusion and loss of time.
The beauty of Pyhton (...and Pandas, I suppose) in combination with Jupyter Notebook (...we seem to have fallen for the
same solution): You start out with a clear, pure data set, you apply a clear logic (aka run the data thru the code) and end
up with data again.
The approach seperates clearly data from logic - and the logic becomes better understandable and more clearly documented
and reproducable with the Notebook.
Really, if I had to express the advantage of Python over Excel in a single statement it would be just this: Seperation of
logic from data.
I am planning to learn Lua for my desktop scripting needs. I want to know if there is any documentation available and also if
there are all the things needed in the Standard Lib.
Thanks uroc for your quick response. If possible, please let me know of any beginner tutorial or atleast some sample code for
using COM programming via Lua. :) – Animesh
Oct 14 '09 at 12:26
More complex code example for lua working with excel:
require "luacom"
excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet = book.Worksheets(1)
excel.Visible = true
for row=1, 30 do
for col=1, 30 do
sheet.Cells(row, col).Value2 = math.floor(math.random() * 100)
end
end
local range = sheet:Range("A1")
for row=1, 30 do
for col=1, 30 do
local v = sheet.Cells(row, col).Value2
if v > 50 then
local cell = range:Offset(row-1, col-1)
cell:Select()
excel.Selection.Interior.Color = 65535
end
end
end
excel.DisplayAlerts = false
excel:Quit()
excel = nil
Another example, could add a graph chart.
require "luacom"
excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet = book.Worksheets(1)
excel.Visible = true
for row=1, 30 do
sheet.Cells(row, 1).Value2 = math.floor(math.random() * 100)
end
local chart = excel.Charts:Add()
chart.ChartType = 4 -- xlLine
local range = sheet:Range("A1:A30")
chart:SetSourceData(range)
A quick suggestion: fragments of code will look better if you format them as code (use the little "101 010" button). –
Incredulous Monk
Oct 19 '09 at 4:17
Overall, I like this book, even though it is somewhat confusing, both
in scope and in the target audience.
The techniques of "naming the range" or writing the "if" formula are
certainly targeted for beginners, but most of statistical tools are
normally used by more advanced users.
The worst thing, though, is that the book is full of errors, both typos
and mistakes in problem solutions on the disk. I consider myself an
intermediate user, so finding an error in "instructor solution" was
more like an additional challenge for me, but for the beginner this
could be very frustrating.
On the positive side - I really liked the idea of problems in the end
of each chapter; so many books just give you the theory and then you
do not know how to solve a real life problem. For most of chapters,
I knew the tools, but still had to spend time figuring out the best
way to implement it for problem solving.
Very practical book, good for an intermediate users. Just
be aware of the typos !
This book is aimed at a level slightly higher than the total beginner.
On page one, for instance he assumes that you understand the statement
SUM(A5000:A5049) means. Then he points out that writing SUM(USSales)
would make it easier to understand. He then describes how to name a
range of cells -- Not exactly what I'd call advanced, but certainly
above novice.
The first third, or so, of the book is on these kinds of slightly advanced
procedures that you could get out of several Excell books. After that
he kind of turns the approach around to things like "Estimating a Demand
Curve." This is looking for what you want to do rather than just looking
at a technique within Excell.
Many of his chapters have fairly sophisticated subjects, "Incorporating
Qualitative Factors into Multiple Regression." In this chapter he then
looks at multiple factors that might go into predicting things like
predicting sales, predicting the Presidential race - with fairly surprising
results.
In a couple of chapters he has a bit of fun, i.e. Chapter 73 is "Picking
Your Fantasy Football Team."
Excell is quite a powerful package. It can be used for a lot of things
beyond just filling out an expense account. In this book a lot of non
traditional uses are described in a very light but informative manner.
It is a very well done book.
Very practical, but full of errors, April 2, 2007
The depth of Excel development knowledge of the authors can be readily
seen a few pages into the Introduction. If you have been frequent
visitors to Excel/VBA programming newsgroups like myself, you will
notice immediately that all three authors are well-recognized experts
in this domain, and have answered countless tricky questions on
the topic of Excel/VBA. In my opinion, the particularly valuable
chapters include:
- Chapter 3 on "Best Practices"
- Chapter 17 on "Optimizing VBA Performance"
- Chapter 19 on "XLLs/C API"
- Chapters 20-21 on VB6
- Chapter 22 on VB.NET/VSTO
This is such a comprehensive book that will transform your Excel
development skills to the professional level. Without reservation,
the authors have delivered what the book title promises.
A remarkable book on creating complete Excel-based apps, April
13, 2005
Over the years, I've read a number of books on Excel programming.
Each one seemed much like the previous one, generally talking about
writing macros and creating data-entry forms. Professional Excel Development
takes the concept quite a bit farther. Rather than giving you the same
old tired lessons, this book goes into detail on exactly how to build
professional level applications. It even explains how to make your Excel-based
application look as though Excel had nothing to do with it. Suffice
it to say, this ain't your daddy's Excel book.
The authors, Stephen Bullen, Rob Bovey, and John Green, show a level
of sophistication well beyond the norm. They'd rather teach you the
proper way to program instead of teaching you how to use Excel. In fact,
the first thing they do is distinguish five different levels of usage:
Excel users, Excel power users, VBA developers, Excel developers, and
professional Excel developers. The book is written for the highest level,
so expect a lot of depth. The entire structure of the book builds around
a time-entry application that is developed from a simple spreadsheet
to a full-blown, production quality program. A CD-ROM is also included
with all of the source code and multiple examples that are scattered
throughout the book.
Reading Professional Excel Development is not something to be taken
lightly. The authors have done a fine job putting together a cohesive
methodology for using Excel as an application development platform.
I know of no other book that covers this platform in such depth. At
times I found myself lost in the details, but I suspect a "professional
Excel developer" (which I am not) would be delighted in the depth of
description and copious examples provided.
Professional Excel Development is an extremely well-written book
that covers the use of Excel to a depth few authors have dared to tread.
The text gives you the tools to build applications that are much more
than automated spreadsheets. Almost any program your imagination can
devise can be created using the techniques given, which is a testimony
to the power of Excel.
Bash Microsoft if you want, but they do sometimes come up with a
winner, and Professional Excel Development allows you to take full advantage
of its capabilities. I highly recommend this book...
Publisher: Wrox; New Ed edition
(September 26, 2001)
Language: English
ISBN: 0764543717
Shipping Weight: 3.15 pounds
Reviewer:Ruslan Moskalenko "Ruslan Moskalenko"
(Pleasanton, CA United States) -
See all my reviews
It's a great book! It actually consists of two parts: a brief but very
useful VBA overview with all popular tricks and hints in the beginning
and a deep and complete reference at the end. Normally you need to buy
two separate books to get this coverage. This book can get you started
in a few hours and answer virtually all your questions going forward.
Really, really good!
If you are reading this review, buy this book now., December
2, 2003
If you are interested enough to read this review, then buy this book.
Now. I have done quite a bit of programming ... The only problem was,
I had never programmed VBA/Excel and was having a very rough time getting
started.
I bought "Excel 2002 VBA Programmer's Reference". I sat down and
started reading Chapter 2. Before long, I was so excited that I was
firing up my laptop. Four hours after I started reading the book, I
had a prototype up and running. This book is amazing. Chapter 2 gave
me more useful information than I sometimes get from an entire book.
...
The authors are simply bursting with
knowledge and give it freely. Ideas are expressed clearly and succinctly.
Knowledge is well-organized and easy to find. Whether you want to approach
the topic methodically or flip through looking for gems, you will find
what you want here.
Getting the object model is a great bonus. I'm going to enjoy spending
a few days going through this one.
This book is aimed at a level slightly higher than the total beginner.
On page one, for instance he assumes that you understand the statement
SUM(A5000:A5049) means. Then he points out that writing SUM(USSales)
would make it easier to understand. He then describes how to name a
range of cells -- Not exactly what I'd call advanced, but certainly
above novice.
The first third, or so, of the book is on these kinds of slightly advanced
procedures that you could get out of several Excell books. After that
he kind of turns the approach around to things like "Estimating a Demand
Curve." This is looking for what you want to do rather than just looking
at a technique within Excel.
Many of his chapters have fairly sophisticated subjects, "Incorporating
Qualitative Factors into Multiple Regression." In this chapter he then
looks at multiple factors that might go into predicting things like
predicting sales, predicting the Presidential race - with fairly surprising
results.
In a couple of chapters he has a bit of fun, i.e. Chapter 73 is "Picking
Your Fantasy Football Team."
Excell is quite a powerful package. It can be used for a lot of things
beyond just filling out an expense account. In this book a lot of non
traditional uses are described in a very light but informative manner.
It is a very well done book.
I readily admit that the mistakes in the CD are an impediment to learning,
but I think the book still has value for users who are looking for the
application to business. Many of the CD's chapters were fine and proved
very useful and I don't agree with other reviewers that the CD is flawed
beyond use. Users of this book can glean a great deal from the unflawed
majority of the book.
The book introduces and expands on many different 'useful' formulas
and tools included within Excel.
The problems at the end of each chapter allows the reader to use what
was just learned in developing spreadsheets to solve realistic accounting
problems.
Even the 'above average' user of Excel, I believe, will learn from this
book.
The CD even has an electronic version of the book, so you don't have
to carry the book around everywhere.
The main problem: the CD STINKS !!!! It has MANY, MANY errors.
From forgetting to take into account given growth rates detailed in
a problem (therefore yielding incorrect answers) to just plain sloppy
copying of sheets (forgetting to delete the drawings and numbers not
pertinent to the current sheet).
As said in a prior review, if an updated CD were to become available,
with the CORRECT ANSWERS on it, this would be a definite 5 star.
(This said, perhaps finding the errors on the CD could be a means of
developing your spreadsheets skills):)
I love this book. This Book is a must for anyone who wants to learn
how to use Excel to perform Data Analysis and Business modeling. I learned
alot from this book. Winston shows us how to use Excel functions and
shows us with examples. Plenty of examples and answers provided with
the CD. This is a very good "foundational" book. You should also check
out the book by Jackson and Staunton "Advanced modeling in finance using
Excel and VBA". A book by Sengupta "Financial Modeling using Excel and
VBA" is also worth looking at. I own all 3 books
Edition: Hardcover
35 used & new from $19.45
7 of 7 people found the following review helpful:
Master Modeling employing Excel, September 23, 2005
This book focuses on several management science modeling problems and
explains clearly how to implement them with Excel.
Winston teaches you how to employ standards (colors, borders, etc) to
make your spreadsheet more professional. You will learn much about Excel
even if you are not interested in management science models.
Contrary to Optimization and Operations Research books, it does not
worry about algorithm details, being directed mainly to develop the
student modeling skills.
And as a friend told me "I guess anything by this author is worth the
money"
Then, if your goal is to find a text that contains excel-based ways
of solving managerial problems, and you are not concerned with solver
details (simplex method, transport algorithms, nonlinear programming
algorithms, interior point methods) this book is for you.
Another information: Chris Albright has a Modeling book directed to
people that want to learn how to use VBA in modeling, that is, if you
are a "want to be advanced" Excel user. He says that his VBA book is
the natural next step to "Practical Management Science".
It is often either inconvenient or financially unfeasible to solve every
mathematical problem with a standalone procedural language or an expensive
application such as Matlab or Mathematica.
Since Excel has such advanced computational capabilities built
into it and is ubiquitous in offices and homes, it would make sense
to mine this application for numerical problem solving techniques.
Bourg does a masterful job of presenting the tools available in Excel
and showing the reader how to put them to work to solve real world engineering
and mathematical problems.
First, he spends a couple of chapters briefly going over Excel and
its language VBA (Visual Basic for Applications). Next he goes over
some intermediate level tasks such as collecting and cleaning up data,
charting including 3D charts, and statistical analysis tasks such
as correlating data and generating random numbers.
Next Bourg moves into purely mathematical tasks such as working with
discrete Fourier transforms, manipulating matrices and vectors, as well
as working with basic mathematical functions.
Once you have learned to use these mathematical tools in Excel, Bourg
uses them to show you how to perform curve fitting and regression, solve
equations, perform integration and differentiation, and solve both ordinary
and partial differential equations. He even spends time on performing
multivariable calculus and the finite element method in Excel. Chapter
13, on optimization, was my favorite chapter.
Here, Bourg is actually getting into a little algorithm analysis
and design via linear programming and genetic algorithms. This chapter
showed me some truly innovative uses of Excel. The final chapter deviates
from the scientific flavor of the book and concentrates on more traditional
financial applications.
The writing is very accessible, the examples are clear and very creative,
and the author does a tremendous job of capturing the range of Excel's
mathematical capabilities. Do not expect mathematical theory in this
book. Bourg assumes you already know how to set up a mathematical problem
and that you just need a computational platform and tools with which
to perform your work. It is nice to know I am not necessarily held hostage
by Mathworks(maker of Matlab) every time I need to solve a mathematical
problem of any level of sophistication. Highly recommended.
The table of contents is as follows:
1. Using Excel
2. Getting Acquainted with Visual Basic for Applications
3. Collecting and Cleaning Up Data
4. Charting
5. Statistical Analysis
6. Time Series Analysis
7. Mathematical Functions
8. Curve Fitting and Regression
9. Solving Equations
10. Numerical Integration and Differentiation
11. Solving Ordinary Differential Equations
12. Solving Partial Differential Equations
13. Performing Optimization Analyses in Excel
14. Introduction to Financial Calculations
I won't say this book is "the only Excel VBA book you will need" --
there is no such kind of thing for programming books. However, I like
to say it is one of the best and definitive guides to Excel VBA.
The most important
thing is that there are several advanced topics you won't find on the
other popular Excel VBA books (such as Excel 2002 VBA programminer's
Reference by Stephen Bullen, John Green et al. or Excel 2002 Power Programming
with VBA by John Walkenback): for instance, recursion, details of random
numbers generation, matrix functions, automatic data reporting, OLAP
functionality and using Excel as ActiveX server.
However, I also must say this book is
definitely not for the beginners to learn VBA. --This text refers
to an out of print or unavailable edition of this title
Probably the two books to compare with are Walkenbach's "EXCEL 2000
Power Programming", and Green's "EXCEL 2000 VBA". A related book that
is a description of the language with nothing about the EXCEL object
model is Lomax "VB & VBA in a Nutshell".
Kofler can be cryptic at times. As explanation, he says (p. 336)
"The long and the short of it is that you [dear reader] will not be
spared the opportunity to do a bit of experimenting yourself". An example
of this brevity is the treatment of the MsgBox, which is used to interrogate
the user of the spreadsheet and offer a variety of choices. Kofler provides
an example on p. 220, and refers the reader to the online help for amplification.
In contrast, Julitta Korol offers over 7 pages of examples and tables
using this function. The pro of brevity is many features can be outlined,
and the reader can find the additional info for free. The con is that
the reader won't appreciate the full utility of the feature without
perusing the online help in some detail.
Compared to Walkenbach, Kofler has more about features new
to EXCEL 2000. For instance, Kofler has subroutines illustrating the
use of the Implements statement in VBA, while Walkenbach lists it as
a statement with no comment, Green doesn't even mention it. Lomax has
a complete discussion pp. 373 - 375. Kofler also describes the manipulation
of files using the FSO (File System Objects) Library (pp. 245ff.), a
new feature.
Compared to Green, Kofler is almost entirely exposition, while pp.
343-695 of Green is a listing of the EXCEL 2000 and VBE object models,
which I personally find not too useful. However, because the first 245
pages of Green is compact, I find it easier to find information there.
Kofler definitely is a notch more advanced than Walkenbach, at least
as knowledgeable, and much better organized. In fact, although there
is a lot of overlap, it is handy to have both books because it is usually
possible to find what you want in Kofler, and then (sometimes) use Walkenbach
for a more detailed hand-holding example. However, finding something
in Walkenbach by itself requires luck because of his poor organization
and index.
None of the three EXCEL books describes the special status of sub
main().
Bottom line: Buy Green first (cheapest), Kofler second and Walkenbach
last. --This text refers to an out of print or unavailable edition
of this title
Great reference book!, August 21, 2003
Reviewer: A reader from
Kansas City, MO
This book has proved a life saver on more than one occasion. It is written
in easy to understand language and organized in a way that doesn't force
you to read the entire chapter to glean the one tidbit of information
you were searching for. If you are looking for a tutorial style book,
this isn't it, however if you are looking for a book that gives you
answers quick - you're in luck. It has a thorough, helpful index. My
one gripe is that I have found that the examples are a little scarce.
I would like to see more examples of actual code. All in all, I am very
pleased with my decision to purchase this book. --This text refers
to an out of print or unavailable edition of this title
This book was my bible for VBA & Excel while creating an application
for work. I thought about getting Walkenbach's "EXCEL 2000 Power Programming",
but my dad owned that one so I figured I could always borrow it if needed
- but I never did need it.
I had not written a single line of Visual Basic or VBA prior to getting
this book - I learned everything I needed here. (Although I had plenty
of experience in C++/Java).
A few comments - I liked his style of mostly providing textual descriptions
of all the different Excel classes, methods, and properties. There's
not a lot of code in the book - but I never missed it. He provided clear
enough descriptions of how to use the Excel object model, that I didn't
need a ton of printed code to reinforce (don't get me wrong, there IS
code within the book + the sample CD, just not pages and pages reflecting
an entire application he's building up, like you get in a cookbook).
If you prefer to just cut&paste code from a book into your own app,
then you might be better off with Walkenbach's book.
The book was originally written in German and then translated to
English, but I only remember one or two times where that became evident.
As someone who has some programming
experience, this book was very helpful to me.
The project I am working on was already defined, and all I had to do
was look in the books where an example was given or a syntax reference
existed. Note that I used it in alongside O'Reilly Press' 'Writing Excel
Macros with VBA'. Where certain areas aren't covered in this book (rare),
there will almost certainly be something in the other.
As an intermediate level part-time but somewhat rusty programmer
(mostly self-taught) of Java, C, Javascript, HTML, CSS, XML, Assembly
and others, this book certainly had what I was looking for.
It offers useful language references and the descriptions are ample,
although occasionally apparently useful methods that were covered in
'the other' book and were overlooked in this one; it's just not possible
to include everything though, even in ~1000 pages.
It assumes some prior knowledge of programming techniques,
and is therefore not for the absolute beginner, but will serve
it's purpose very well indeed to the majority.
I would recommend this book all except the absolute beginner and
the advanced programmer/expert (who probably wouldn't be reading this
anyway!). Definitely worth the money.
I rarely buy books on the internet without having a good flick through
them at the local bookshop first. In this case I would advise
likeminded thinkers to make an exception.
The included CDROM is worth it's weight in gold and is all too
often a crucial ommission by authours/publishers. I can now take the
book with me on my laptop in PDF format (hooray!) and all of the book
examples are included too. BONUS!
Very light in content. Cannot do much after reading it, July
25, 2003
First of all, I am non-English speaker (forgive my English). I am an
Electrical Engineer, not a programmer. But I have many years programming
experiences in different languages (on my own!).
I have finished up to chapter 10. The
contents is very light. It talks about very basic syntax
that are almost the same in any language (e.g. C, Java, Perl C++...)
It puts hundreds of pages that can be done
in a half of the volume. It does not tell you much about
the object details. It seems to tell you to explore the objects and
methods by recording macro and learning by trial and error. If so, I
don't need this book.
First of all, time is money. I spent money, time to read hundreds
of pages. The author suggests you to trial and error. I really don't
think it is a very good book. But I still give 3 stars (I am quite generous!)
Maybe most audiences of this book are never program in his/her life.
This may be good for them. For someone who has experiences in programming.
It is not very useful.
The author should really concentrate on objects and methods after
some basic syntax. That is the most important. I can learn the syntax
in few days. But I don't know much about all the objects and methods.
Without knowing the objects and methods, nobody can do much. Just like
learning English, you know all the syntax but you don't know any WORDS
and USAGE. What do you think you can write!
Average Customer Review:
Based on 6 reviews.
Write a review.
Amazon.com Sales Rank: 50,111
The Title of the Book has Been Mistaken, December 4, 2003
Reviewer: J. Polihronov from London, UK
When I bought the book, I was impressed
by its title and TOC since they give the promise of leading the reader
into macro programming. However, it is rather a large-volume Excel/VBA
reference, not a book on how to write macros.
Unfortunately, there is no system of introducing the reader gradually
into the language (as in all usual programming textbooks) by introducing
concepts, giving examples and requesting particular exercises.
On a positive note, the book contains
(all) VBA concepts relevant to Excel macros and can very well serve
as a future reference once the reader has bought another book to learn
how to code within some range of macro programming.
VERY USEFUL COMPANION, June 13, 2003
Reviewer: A reader from Dansa28, Whangarei, New Zealand
As someone who has some programming experience, this
book was very helpful to me.
The project I am working on was already defined, and
all I had to do was look in the books where an example was given or
a syntax reference existed. Note that I used it in alongside Walkenbach's
Excel 2002 Power Programming with VBA. Where certain areas aren't covered
in this book (as it's only 500 pages), there will almost certainly be
something in the other.
As an intermediate level part-time programmer (mostly self-taught) of
Java, C, Javascript, HTML, CSS, XML, Assembly and others, this book
certainly had what I was looking for.
It mainly offers concise language references and the descriptions are
kept to the necessary, and this O'Reilly book is a vast improvement
on some that I have bought for other languages in the past.
It does however assume some prior knowledge of programming techniques,
and is therefore not for the beginner.
I would recommend THIS book only to those who have either a very keen
interest in programming or those who have had some formal programming
teaching. Definitely worth the money though.
The Last but not LeastTechnology is dominated by
two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt.
Ph.D
FAIR USE NOTICEThis site contains
copyrighted material the use of which has not always been specifically
authorized by the copyright owner. We are making such material available
to advance understanding of computer science, IT technology, economic, scientific, and social
issues. We believe this constitutes a 'fair use' of any such
copyrighted material as provided by section 107 of the US Copyright Law according to which
such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free)
site written by people for whom English is not a native language. Grammar and spelling errors should
be expected. The site contain some broken links as it develops like a living tree...
You can use PayPal to to buy a cup of coffee for authors
of this site
Disclaimer:
The statements, views and opinions presented on this web page are those of the author (or
referenced source) and are
not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society.We do not warrant the correctness
of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be
tracked by Google please disable Javascript for this site. This site is perfectly usable without
Javascript.
Very weak and expensive cookbook, November 4, 2006
Non-Traditional Uses of Excell, May 24, 2005
Very practical, but full of errors, April 2, 2007
Best Excel development book to date, April 26, 2006
John
Matlock "Gunny
Non-Traditional Uses of Excell, May 24, 2005
Master Modeling employing Excel, September 23, 2005
Based on 11 reviews. 
One of the definitive guides to Excel VBA books, August 21, 2002
Good organization, a bit terse, January 4, 2001 
Very light in content. Cannot do much after reading it, July
25, 2003 
Based on 6 reviews.
The Title of the Book has Been Mistaken, December 4, 2003 