Softpanorama

Home Switchboard Unix Administration Red Hat TCP/IP Networks Neoliberalism Toxic Managers
May the source be with you, but remember the KISS principle ;-)
Skepticism and critical thinking is not panacea, but can help to understand the world better

Best Books about Excel Macro Programming

News Reviews Definitive Guide to Excel VBA Excel 2003 Power Programming with VBA Excel Programming Writing Excel Macros with VBA, 2nd Edition  
See also   Financial modelling Random Findings Humor Etc

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.

Among few good books that I encountered:

  1. Excel 2016 VBA and Macros (includes Content Update Program) (MrExcel Library) by Jelen, Bill & Tracy Syrstad (606 pages, Nov 4, 2015)  It has some "best ever" reviews:

    Alexandr Syzonuk 5.0 out of 5 stars Best book fo VBA May 9, 2017

    Verified Purchase
    Buy it and you won't regret. Best book of VBA I ever had. Very detailed and simple explanation how VBA actually works

  2. Excel 2007 VBA Macro Programming by Richard Shepherd.

Now about bad books. I once mistakenly bought weak and disorganized  Excel 2003 Formulas Books John Walkenbach. Excel 2003 Power as well as  Programming with VBA by the same author.  After that I really hate John Walkenbach books, the author who actually almost monopolized the field :-).  

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). 

Reviews

[Nov 4, 2006] Excel 2003 Formulas Books John Walkenbach

Very weak and expensive cookbook, November 4, 2006
Reviewer: "kievite" (Budd Lake, NJ) - See all my reviews
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).

NEWS CONTENTS

Old News ;-)

[Mar 12, 2021] Microsoft Open Sources Low-Code Power Fx Language - Developer.com

Mar 12, 2021 | www.developer.com

Microsoft is looking to rally a community around a Power Fx low-code programming language that is now an open source project.

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.

[Nov 22, 2020] How to Supercharge Excel With Python, by Costas Andreou

Jul 21, 2020 | towardsdatascience.com

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.
  1. You can create a custom function in Excel without learning VBA (if you don't know it already)
  2. Your users are comfortable in Excel
  3. You can speed up your data operations significantly by using Python
  4. There are libraries for just about anything in Python (Machine Learning, Data Science, etc)
  5. 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:
mkdir C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART
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:
  1. Look for a Python Script in the same location as the spreadsheet
  2. Look for a Python Script with the same name as the spreadsheet (but with a .py extension)
  3. 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 :
import xlwings as xw
import pandas as pddef main():
    wb = xw.Book.caller()
    df = pd.read_csv(r'C:\temp\TestData.csv')
    df['total_length'] =  df['sepal_length_(cm)'] + df['petal_length_(cm)']
    wb.sheets[0].range('A1').value = df
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 xw@xw.func
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 !

[Nov 22, 2020] Hedge Fund Modelling and Analysis Using Excel and VBA

Nov 22, 2020 | www.amazon.com

[Aug 08, 2020] Tools for Working with Excel and Python

Aug 08, 2020 | www.pyxll.com

Posted on August 13, 2018 by Tony Roberts
Last updated May 22, 2020

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.

See the table of features along with the packages that support them below .

PyXLL – The Python Excel Add-In

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.

See PyXLL's Features

Using PyXLL, you can write Python code to create:

Writing a user defined function with PyXLL requires the 'xl_func' decorator to be applied to a normal Python function:

from pyxll import xl_func @xl_func def py_test(a, b, c): return (a + b) * c

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:

For more features take a look at the feature matrix below.

Home Page | Download PyXLL | Documentation

Download PyXLL pywin32 / comtypes

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 .

The Excel Object Model is documented here https://docs.microsoft.com/en-gb/office/vba/api/overview/Excel/object-model and once you understand the basic differences between VBA and Python you will find it's fairly simple to translate between the two.

To demonstrate let's go though an example. Suppose you had the following VBA code and want to translate it into Python:

Sub Macro1() Range( 'B11:K11').Select Selection.AutoFill Destination:=Range( 'B11:K16'), Type:=xlFillDefault Columns( 'B:K').Select Selection.ColumnWidth = 4 End Sub

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 .

from win32com.client.gencache import EnsureDispatch # Get the Excel Application COM object xl = 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.

from win32com.client import constants xl.Selection.AutoFill(Destination = xl. Range ( 'B11:K16' ), Type = constants.xlFillDefault)

The rest of the code is similar to those lines we're just translated, and so the entire function looks like

from win32com.client.gencache import EnsureDispatch from win32com.client import constants def Macro1(): xl = EnsureDispatch( 'Excel.Application' ) xl. Range ( 'B11:K11' ).Select() xl.Selection.AutoFill(Destination = xl. Range ( 'B11:K16' ), Type = constants.xlFillDefault) xl.Columns( 'B:K' ).Select() xl.Selection.ColumnWidth = 4

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

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.

import xlwings as xw wb = xw.Book( 'workbook.xlsx' ) # Open an existing Workbook sheet = wb.sheets[ 'Sheet1' ] # read and write values from the worksheet sheet. range ( 'A1' ).value = 'Foo' print (sheet. range ( 'A1' ).value) # Write a Pandas DataFrames directly to the Excel sheet import pandas as pd df = 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 table sht. 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
IntelliSense IntelliSense tooltip as you type – PyXLL integrates with the ExcelDNA Intellisense Addin
Thread safe worksheet functions Improve worksheet responsiveness by using Excel's own threadpool to run worksheet functions concurrently
Asynchronous functions Don't block Excel waiting for long running functions
Macros Macros are functions that can be attached to UI elements like buttons or called from VBA
Keyboard shortcuts Keyboard shortcuts can be assigned to macros with PyXLL
Macro sheet equivalent functions Call back into Excel from a worksheet function
Function documentation Include Python function docstrings in the Excel function wizard
Automatically resize arrays Array functions can resize automatically
Volatile Functions Volatile functions are called every time a worksheet is recalculated
Full Excel API exposed xlwings uses pywin32, PyXLL users can choose between pywin32, comtypes or xlwings
Reload without restarting Excel Modules can be reloaded without restarting Excel. PyXLL also supports 'deep reloading' where all module dependencies are also reloaded.
Automatic reloading Reload automatically whenever changes are made to your code.
See PyXLL's Features Download PyXLL Reading and Writing Excel workbooks

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.

from openpyxl import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws[ 'A1' ] = 42 # Rows can also be appended ws.append([ 1 , 2 , 3 ]) # Save the file wb.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:

from openpyxl.utils.dataframe import dataframe_to_rows wb = Workbook() ws = wb.active for r in dataframe_to_rows(df, index = True , header = True ): ws.append(r) wb.save( 'pandas_openpyxl.xlsx' )

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.

from openpyxl import load_workbook wb = load_workbook(filename = 'book.xlsx' ) sheet_ranges = wb[ 'range names' ] print (sheet_ranges[ 'D18' ].value)

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.

XlsxWriter

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:

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.

import xlsxwriter workbook = xlsxwriter.Workbook( 'hello.xlsx' ) worksheet = workbook.add_worksheet() worksheet.write( 'A1' , 'Hello world' ) workbook.close()

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.

import pandas 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.book worksheet = 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

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.

from xltable import * import pandas as pd # create a dataframe with three columns where the last is the sum of the first two dataframe = 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 instance table = Table( 'table' , dataframe) # create the Workbook and Worksheet objects and add table to the sheet sheet = Worksheet( 'Sheet1' ) sheet.add_table(table) workbook = Workbook( 'example.xlsx' ) workbook.add_sheet(sheet) # write the workbook to the file using xlsxwriter workbook.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 .

Pandas

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 file df.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/xlwt

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.

pyexcel

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.

> import pyexcel as pe > records = pe.iget_records(file_name = 'your_file.xls' ) > for record in records: ... print ( '%s is aged at %d' % (record[ 'Name' ], record[ 'Age' ])) Adam is aged at 28 Beatrice is aged at 29 Ceri is aged at 30 Dean is aged at 26 > pe.free_resources()
Additional Resources

[Aug 08, 2020] excel - Using Python to program MS Office macros- - Stack Overflow

Jan 01, 2013 | stackoverflow.com

9

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/

Also, there are SO questions already dealing with this sort of topic, including this: Is there a better way (besides COM) to remote-control Excel?

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.


denfromufa , 2013-10-13 14:43:11

here is a useful link:

http://continuum.io/using-excel

  1. Try Pyvot – A Python to/from Excel Connector from Microsoft:

http://pytools.codeplex.com/wikipage?title=Pyvot

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):

https://datanitro.com

5.

This is not free for commercial use:

PyXLL - Excel addin that enables functions written in Python to be called in Excel.

> ,

add a comment

> ,

This is a question from a long time ago, but I'm putting it here so others can point to it in their search.

One option not discussed here, which I use all the time, is to create a COM server from Python and call it from VBA in any Office application. There is a good tutorial of doing COM server with Python at: http://timgolden.me.uk/pywin32-docs/html/com/win32com/HTML/QuickStartServerCom.html

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.

[Aug 08, 2020] Get started using Python on Windows for scripting and automation

Aug 08, 2020 | oreilly.com

[Aug 06, 2020] python - Fill cells with colors using openpyxl

May 27, 2015 | stackoverflow.com

Ahmed Rashad , 2015-05-27 13:39:23

I am currently using openpyxl v2.2.2 for Python 2.7 and i wanted to set colors to cells. I have used the following imports

import openpyxl,
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell

and the following is the code I tried using:

wb = openpyxl.Workbook()
ws = wb.active

redFill = PatternFill(start_color='FFFF0000',
                   end_color='FFFF0000',
                   fill_type='solid')

ws['A1'].style = redFill

but I get the following error:

Traceback (most recent call last)
  self.font = value.font.copy()
AttributeError: 'PatternFill' object has no attribute 'font'

Any idea on how to set cell A1 (or any other cells) with colors using openpyxl?

The6thSense ,

Could you provide the full trace back – The6thSense May 27 '15 at 13:45

Charlie Clark ,

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

[Jul 28, 2020] WORKING WITH EXCEL SPREADSHEETS

Extracted from Automate the Boring Stuff with Python, 2nd Edition Please buy the book. It is really good
Jul 28, 2020 | amazon.com

... ... ...

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' .

[Jul 27, 2020] Statistica in Python

Jul 27, 2020 | zetcode.com

Ebooks Openpyxl tutorial

last modified July 6, 2020

In this tutorial we show how to work with Excel files in Python using openpyxl library.

https://www.facebook.com/v2.8/plugins/like.php?action=like&app_id=&channel=https%3A%2F%2Fstaticxx.facebook.com%2Fx%2Fconnect%2Fxd_arbiter%2F%3Fversion%3D46%23cb%3Df8ad26aeac378c%26domain%3Dzetcode.com%26origin%3Dhttp%253A%252F%252Fzetcode.com%252Ffaa3837d104018%26relation%3Dparent.parent&container_width=730&href=http%3A%2F%2Fzetcode.com%2Fpython%2Fopenpyxl%2F&layout=button_count&locale=en_US&sdk=joey&share=true&show_faces=true

https://platform.twitter.com/widgets/tweet_button.c4b33f07650267db9f8a72eaac551cac.en.html#dnt=false&id=twitter-widget-0&lang=en&original_referer=http%3A%2F%2Fzetcode.com%2Fpython%2Fopenpyxl%2F&size=m&text=Openpyxl%20tutorial%20-%20read%2C%20write%20Excel%20xlsx%20files%20in%20Python&time=1595883144162&type=share&url=http%3A%2F%2Fzetcode.com%2Fpython%2Fopenpyxl%2F Openpyxl

The openpyxl is a Python library to read and write Excel 2010 xlsx/xlsm/xltx/xltm files.

https://bf66057ac32c57f598df4bfd31d085c6.safeframe.googlesyndication.com/safeframe/1-0-37/html/container.html Excel xlsx

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.

New file
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.

rows = (
    (88, 46, 57),
    (89, 38, 12),
    (23, 59, 78),
    (56, 21, 98),
    (24, 18, 43),
    (34, 15, 67)
)

The data is stored in a tuple of tuples.

for row in rows:
    sheet.append(row)

We go through the container row by row and insert the data row with the append method.

Openpyxl read cell

In the following example, we read the previously written data from the sample.xlsx file.

read_cells.py
#!/usr/bin/env python

import openpyxl

book = openpyxl.load_workbook('sample.xlsx')

sheet = book.active

a1 = sheet['A1']
a2 = sheet['A2']
a3 = sheet.cell(row=3, column=1)

print(a1.value)
print(a2.value) 
print(a3.value)

The example loads an existing xlsx file and reads three cells.

book = openpyxl.load_workbook('sample.xlsx')

The file is opened with the load_workbook method.

a1 = sheet['A1']
a2 = sheet['A2']
a3 = sheet.cell(row=3, column=1)

We read the contents of the A1, A2, and A3 cells. In the third line, we use the cell method to get the value of A3 cell.

$ ./read_cells.py 
56
43
10/26/16

This is the output of the example.

Openpyxl read multiple cells

We have the following data sheet:

Items
Figure: Items

We read the data using a range operator.

read_cells2.py
#!/usr/bin/env python

import openpyxl

book = openpyxl.load_workbook('items.xlsx')

sheet = book.active

cells = sheet['A1': 'B6']

for c1, c2 in cells:
    print("{0:8} {1:8}".format(c1.value, c2.value))

In the example, we read data from two columns using a range operation.

cells = sheet['A1': 'B6']

In this line, we read data from cells A1 - B6.

for c1, c2 in cells:
    print("{0:8} {1:8}".format(c1.value, c2.value))

The format() function is used for neat output of data on the console.

$ ./read_cells2.py 
Items    Quantity
coins          23
chairs          3
pencils         5
bottles         8
books          30

This is the output of the program.

Openpyxl iterate by rows

The iter_rows method return cells from the worksheet as rows.

iterating_by_rows.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)
    
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()    

book.save('iterbyrows.xlsx')

The example iterates over data row by row.

for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):

We provide the boundaries for the iteration.

$ ./iterating_by_rows.py 
88 46 57 
89 38 12 
23 59 78 
56 21 98 
24 18 43 
34 15 67

This is the output of the example.

Openpyxl iterate by columns

The iter_cols method return cells from the worksheet as columns.

iterating_by_columns.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)
    
for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()    

book.save('iterbycols.xlsx')

The example iterates over data column by column.

$ ./iterating_by_columns.py 
88 89 23 56 24 34 
46 38 59 21 18 15 
57 12 78 98 43 67

This is the output of the example.

Statistics

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.

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)))

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.

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)

We add data to the worksheet. Note that we start adding from the third row.

print(sheet.dimensions)

The dimensions property returns the top-left and bottom-right cell of the area of non-empty cells.

print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))

Witht the min_row and max_row properties, we get the minimum and maximum row containing data.

print("Minimum column: {0}".format(sheet.min_column))
print("Maximum column: {0}".format(sheet.max_column))

With the min_column and max_column properties, we get the minimum and maximum column containing data.

for c1, c2 in sheet[sheet.dimensions]:
    print(c1.value, c2.value)

We iterate through the data and print it to the console.

$ ./dimensions.py 
A3:B9
Minimum row: 3
Maximum row: 9
Minimum column: 1
Maximum column: 2
39 19
88 46
89 38
23 59
56 21
24 18
34 15

This is the output of the example.

https://bf66057ac32c57f598df4bfd31d085c6.safeframe.googlesyndication.com/safeframe/1-0-37/html/container.html Sheets

Each workbook can have multiple sheets.

Sheets
Figure: Sheets

Let's have a workbook with these three sheets.

sheets.py
#!/usr/bin/env python

import openpyxl

book = openpyxl.load_workbook('sheets.xlsx')

print(book.get_sheet_names())

active_sheet = book.active
print(type(active_sheet))

sheet = book.get_sheet_by_name("March")
print(sheet.title)

The program works with Excel sheets.

print(book.get_sheet_names())

The get_sheet_names method returns the names of available sheets in a workbook.

active_sheet = book.active
print(type(active_sheet))

We get the active sheet and print its type to the terminal.

sheet = book.get_sheet_by_name("March")

We get a reference to a sheet with the get_sheet_by_name() method.

print(sheet.title)

The title of the retrieved sheet is printed to the terminal.

$ ./sheets.py 
['January', 'February', 'March']
<class 'openpyxl.worksheet.worksheet.Worksheet'>
March

This is the output of the program.

sheets2.py
#!/usr/bin/env python

import openpyxl

book = openpyxl.load_workbook('sheets.xlsx')

book.create_sheet("April")

print(book.sheetnames)

sheet1 = book.get_sheet_by_name("January")
book.remove_sheet(sheet1)

print(book.sheetnames)

book.create_sheet("January", 0)
print(book.sheetnames)

book.save('sheets2.xlsx')

In this example, we create a new sheet.

book.create_sheet("April")

A new sheet is created with the create_sheet method.

print(book.sheetnames)

The sheet names can be shown with the sheetnames attribute as well.

book.remove_sheet(sheet1)

A sheet can be removed with the remove_sheet method.

book.create_sheet("January", 0)

A new sheet can be created at the specified position; in our case, we create a new sheet at position with index 0.

$ ./sheets2.py 
['January', 'February', 'March', 'April']
['February', 'March', 'April']
['January', 'February', 'March', 'April']

This is the output of the program.

It is possible to change the background colour of a worksheet.

sheets3.py
#!/usr/bin/env python

import openpyxl

book = openpyxl.load_workbook('sheets.xlsx')

sheet = book.get_sheet_by_name("March")
sheet.sheet_properties.tabColor = "0072BA"

book.save('sheets3.xlsx')

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.

Background colour of a worksheet
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.

sheet.merge_cells('A1:B2')

We merge four cells with the merge_cells method.

cell = sheet.cell(row=1, column=1)

We get the final cell.

cell.value = 'Sunny day'
cell.alignment = Alignment(horizontal='center', vertical='center')

We set text to the merged cell and update its alignment.

Merged cells
Figure: Merged cells
Openpyxl freeze panes

When we freeze panes, we keep an area of a worksheet visible while scrolling to another area of the worksheet.

freezing.py
#!/usr/bin/env python

from openpyxl import Workbook
from openpyxl.styles import Alignment

book = Workbook()
sheet = book.active

sheet.freeze_panes = 'B2'

book.save('freezing.xlsx')

The example freezes panes by the cell B2.

sheet.freeze_panes = 'B2'

To freeze panes, we use the freeze_panes property.

Openpyxl formulas

The next example shows how to use formulas. The openpyxl does not do calculations; it writes formulas into cells.

formulas.py
#!/usr/bin/env python

from openpyxl import Workbook

book = Workbook()
sheet = book.active

rows = (
    (34, 26),
    (88, 36),
    (24, 29),
    (15, 22),
    (56, 13),
    (76, 18)
)

for row in rows:
    sheet.append(row)

cell = sheet.cell(row=7, column=2)
cell.value = "=SUM(A1:B6)"
cell.font = cell.font.copy(bold=True)

book.save('formulas.xlsx')

In the example, we calculate the sum of all values with the SUM() function and style the output in bold font.

rows = (
    (34, 26),
    (88, 36),
    (24, 29),
    (15, 22),
    (56, 13),
    (76, 18)
)

for row in rows:
    sheet.append(row)

We create two columns of data.

cell = sheet.cell(row=7, column=2)

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.

Calculating the sum of values
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.

create_bar_chart.py
#!/usr/bin/env python

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    Series,
    BarChart
)

book = Workbook()
sheet = book.active

rows = [
    ("USA", 46),
    ("China", 38),
    ("UK", 29),
    ("Russia", 22),
    ("South Korea", 13),
    ("Germany", 11)
]

for row in rows:
    sheet.append(row)
    
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
categs = Reference(sheet, min_col=1, min_row=1, max_row=6)

chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)

chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = "Olympic Gold medals in London"

sheet.add_chart(chart, "A8")    

book.save("bar_chart.xlsx")

In the example, we create a bar chart to show the number of Olympic gold medals per country in London 2012.

from openpyxl.chart import (
    Reference,
    Series,
    BarChart
)

The openpyxl.chart module has tools to work with charts.

book = Workbook()
sheet = book.active

A new workbook is created.

rows = [
    ("USA", 46),
    ("China", 38),
    ("UK", 29),
    ("Russia", 22),
    ("South Korea", 13),
    ("Germany", 11)
]

for row in rows:
    sheet.append(row)

We create some data and add it to the cells of the active sheet.

data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)

With the Reference class, we refer to the rows in the sheet that represent data. In our case, these are the numbers of olympic gold medals.

categs = Reference(sheet, min_col=1, min_row=1, max_row=6)

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.

chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)

We create a bar chart and set it data and categories.

chart.legend = None
chart.y_axis.majorGridlines = None

Using legend and majorGridlines attributes, we turn off the legends and major grid lines.

chart.varyColors = True

Setting varyColors to True , each bar has a different colour.

chart.title = "Olympic Gold medals in London"

A title is set for the chart.

sheet.add_chart(chart, "A8")

The created chart is added to the sheet with the add_chart method.

Bar chart
Figure: Bar chart

In this tutorial, we have worked with the openpyxl library. We have read data from an Excel file, written data to an Excel file.

https://bf66057ac32c57f598df4bfd31d085c6.safeframe.googlesyndication.com/safeframe/1-0-37/html/container.html

Visit Python tutorial or list all Python tutorials .

https://bf66057ac32c57f598df4bfd31d085c6.safeframe.googlesyndication.com/safeframe/1-0-37/html/container.html

© 2007 - 2020 Jan Bodnar admin(at)zetcode.com

[Jul 21, 2020] Python Automation Cookbook by Jaime Buelta

Jul 21, 2020 | amazon.com

How to do it...

  1. Import the csv module:
>>> import csv
  1. 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']
  1. 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')])
  1. 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'

[Jul 21, 2020] Financial Modeling in Excel For Dummies

Jul 21, 2020 | www.amazon.com
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:

[Jul 20, 2020] Why Excel Users Should Learn Python by Rafael Knuth

October 9, 2018 | www.datasciencecentral.com

Latest update: November 16, 2018

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:

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 rafael@knuthconcepts.com or LinkedIn .


me title=



DSC Podcast


Most Popular Content on DSC

To not miss this type of content in the future, subscribe to our newsletter.

Other popular resources

Archives: 2008-2014 | 2015-2016 | 2017-2019 | Book 1 | Book 2 | More

Follow us : Twitter | Facebook

Views: 15267

Tags: Analytics , Career , Data , Excel , Python , Science , Transition

Like 9 members like this

Share

https://www.datasciencecentral.com/profiles/blogs/why-excel-users-should-learn-python

https://www.facebook.com/v2.3/plugins/like.php?app_id=259683271111061&channel=https%3A%2F%2Fstaticxx.facebook.com%2Fx%2Fconnect%2Fxd_arbiter%2F%3Fversion%3D46%23cb%3Df3966dcd4762508%26domain%3Dwww.datasciencecentral.com%26origin%3Dhttps%253A%252F%252Fwww.datasciencecentral.com%252Ff4cd2f35728768%26relation%3Dparent.parent&container_width=0&href=https%3A%2F%2Fwww.datasciencecentral.com%2Fxn%2Fdetail%2F6448529%3ABlogPost%3A766500&layout=button_count&locale=en_US&sdk=joey&show_faces=false&width=450

Comment

You need to be a member of Data Science Central to add comments!

Join Data Science Central

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.

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.

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 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.

[Jan 05, 2019] Use a formula to determine which cells to format

Notable quotes:
"... If you are apply to group of cell horizontal-wise, make sure the formulas don't contain $ signs that would make the references absolute. ..."
Jan 05, 2019 | www.excelforum.com
  1. Re: Use a formula to determine which cells to format?
    After select the cell (e.g. B1)

    enter formula: =A1>B1 and choose RED

    then add new rule with formula =A1<B1 and choose GREEN from Fill tab.

    If you are apply to group of cell horizontal-wise, make sure the formulas don't contain $ signs that would make the references absolute.

[Jan 03, 2019] Using Lua for working with excel - Stack Overflow

Jan 03, 2019 | stackoverflow.com

Using Lua for working with excel Ask Question 2


Animesh ,Oct 14, 2009 at 12:04

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.

uroc ,Oct 14, 2009 at 12:09

You should check out Lua for Windows -- a 'batteries included environment' for the Lua scripting language on Windows

http://luaforwindows.luaforge.net/

It includes the LuaCOM library, from which you can access the Excel COM object.

Try looking at the LuaCOM documentation, there are some Excel examples in that:

http://www.tecgraf.puc-rio.br/~rcerq/luacom/pub/1.3/luacom-htmldoc/

I've only ever used this for very simplistic things. Here is a sample to get you started:

-- test.lua
require('luacom')
excel = luacom.CreateObject("Excel.Application")
excel.Visible = true
wb = excel.Workbooks:Add()
ws = wb.Worksheets(1)

for i=1, 20 do
    ws.Cells(i,1).Value2 = i
end

Animesh ,Oct 14, 2009 at 12:26

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

sagasw ,Oct 16, 2009 at 1:02

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)

Incredulous Monk ,Oct 19, 2009 at 4:17

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

Business Analysis with Microsoft Excel(3rd Edition) by Conrad Carlberg

Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston

Microsoft Press February 25, 2004

Non-Traditional Uses of Excell, May 24, 2005

Reviewer: Lena (Toronto, Canada) - See all my reviews
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 !

Reviewer: John Matlock "Gunny" (Winnemucca, NV) - See all my reviews
(REAL NAME)
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

[Nov 3, 2006] Professional Excel Development The Definitive Guide to Developing Applications Using Microsoft(R) Excel and VBA(R) (The Addison-Wesley Microsoft Technology Series) Books Stephen Bullen, Rob Bovey, John Green

A remarkable book on creating complete Excel-based apps, April 13, 2005
Reviewer: Raymond Lodato "grey geek" (Boston, MA) - See all my reviews
(REAL NAME)

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...

Used and New Excel 2002 VBA Programmers Reference

Reviewer: Ruslan Moskalenko "Ruslan Moskalenko" (Pleasanton, CA United States) - See all my reviews
(REAL NAME) 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

Reviewer: Dennis Eichenlaub (Kingsville, MD United States) - See all my reviews
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.

[Oct 25, 2006] Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston

This book that touch interesting topics. The CD has an electronic version of the book
John Matlock "Gunny Non-Traditional Uses of Excell, May 24, 2005

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.

Patrick B. Meegan Worthwhile in spite of shortcomings, May 26, 2006

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.

D. Lamont "cloud" CD has MANY, MANY mistakes...., August 25, 2004

The idea behind this book is great.

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):)

"wallstbully11729" Great Book!! Very Useful, March 23, 2004

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

Practical Management Science: Spreadsheet Modeling and Applications (with CD-ROM Update) by Wayne L. Winston

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".

Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)

An average book from a weak, talentless author. Avoid it.

by John Walkenbach (Paperback - Jan 13, 2004)

Excel Scientific and Engineering Cookbook (Cookbooks (O'Reilly)) Books David Bourg

Great showcase of Excel's mathematical capabilities, February 6, 2006
Reviewer: calvinnme "Texan refugee" (Fredericksburg, Va) - See all my reviews
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

Analyzing Business Data with Excel by Gerald Knight

Excel VBA,

Definitive Guide to Excel VBA, Second Edition

by Michael Kofler, David Kramer (Translator)

5 out of 5 stars One of the definitive guides to Excel VBA books, August 21, 2002
Reviewer: Weiqin Xie (see more about me) from Plainsboro, NJ United States

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

About the Author
Michael Kofler was born in 1967 in Innsbruck, Austria. He completed his PhD in Computer Science at the Graz Technical University in 1998. Kofler has written a number of successful computer books on topics such as Visual Basic 5 and 6, Client/Server Databases with VB, VBA programming for Excel, Linux, Mathematica and Maple. --This text refers to an out of print or unavailable edition of this title.

4 out of 5 stars Good organization, a bit terse, January 4, 2001

Reviewer: John Robert Brews from Santa Fe, NM
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

4 out of 5 stars 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

5 out of 5 stars This book was my bible, June 12, 2003

Reviewer: Michael R Mayer (see more about me) from Fort Worth, TX United States
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.

I wholeheartedly recommend this book.

Excel 2002 Power Programming with VBA

Excel 2003 Power Programming with VBA

by John Walkenbach (Author); Paperback

5 out of 5 stars Good for beginners and intermediate alike, June 13, 2003
Reviewer: dansa28 (see more about me) 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 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!

3 out of 5 stars Very light in content. Cannot do much after reading it, July 25, 2003
Reviewer: book_addict1 (see more about me) from SOMEWHERE
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!

Writing Excel Macros with VBA, 2nd Edition

Writing Excel Macros with VBA, 2nd Edition by Steven Roman; Paperback

2 out of 5 stars 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.

4 out of 5 stars 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.

Excel Dashboards

Excel® 2007 Dashboards & Reports For Dummies

By: Michael Alexander

Publisher: John Wiley & Sons

Pub. Date: March 04, 2008

Excel Programming

Excel 2007 VBA Macro Programming by Richard Shepherd.

VBA and Macros- Microsoft Excel 2010 (MrExcel Library)

Excel Programming: Your Visual Blueprint for Creating Interactive Spreadsheets (With CD-ROM)
by Jinjer Simon (Editor) (Paperback)
Avg. Customer Rating: 5.0 out of 5 stars
List Price: $26.99

Random Findings



Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotesSomerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose BierceBernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOSProgramming Languages History : PL/1 : Simula 67 : C : History of GCC developmentScripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-MonthHow to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology 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


Copyright © 1996-2020 by Softpanorama Society. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

FAIR USE NOTICE This 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.

Last modified: November 22, 2020