Automating Excel with Python

 Nice idea.  In fact can think of several application examples where we were appending to capabilities of existing spreadsheets.   One a much used, almost historical example.  Could have led to quicker prototypes at least.  Pre-familiarity to the data in the spreadsheets.  Our coders probably would not want to deal with excel, but it still has useful capabilities:

Automate Your Excel Using Python

From manual to an automated approach  By Pranjal Saxena

 Python is an amazing programming language. It is easier to learn and adapt. The error messages in python are self-explainable. We don’t need to invest hours to find the solution to our error message. That’s why I love this programming language.

I think this should be the ideal programming language. A programming language's goal should be to help us build exciting products, not wasting time in solving errors.

Recently, I have learned about automating excel tasks using python. I thought of sharing those amazing tricks with a wider audience. This automating strategy use python programming. The good thing is — each line of python code is self explainable that you don’t need to google anything.

You can use the code editor of your choice. In this article, I will be using the Jupyter Notebook for demonstration purpose.   ..... (details below at the link) 

Excel as a Programming Language

Intriguing Podcast.  The mere notion will  get considerable disdain from coders.   But an interesting point is made about the idea. There is power here.    Podcast and text transcript:  

Advancing Excel as a programming language with Andy Gordon and Simon Peyton Jones

Episode 120 | May 5, 2021   from Microsoft Research. 

Today, people around the globe—from teachers to small-business owners to finance executives—use Microsoft Excel to make sense of the information that occupies their respective worlds, and whether they realize it or not, in doing so, they’re taking on the role of programmer. 

In this episode, Senior Principal Research Manager Andy Gordon, who leads the Calc Intelligence team at Microsoft Research, and Senior Principal Researcher Simon Peyton Jones provide an inside account of the journey Excel has taken as a programming language, including the expansion of data types that has unlocked greater functionality and the release of the LAMBDA function, which makes the Excel formula language Turing-complete. They’ll talk specifically about how research has influenced Excel and vice versa, programming as a human-computer interaction challenge, and a future in which Excel is the first language for budding programmers and a tool for incorporating probabilistic reasoning into our decision-making.  

Learn more: 

Excel Blog: “Announcing LAMBDA: Turn Excel formulas into custom functions” 

Microsoft Research Blog: “LAMBDA: The ultimate Excel worksheet function” 

Research Collection: “Innovation by (and beyond) the numbers: A history of research collaborations in Excel”    ... " 

Custom Live Data Drama in Excel

Though its now quite outdated, we used to do lots of things in spreadsheets, remember this could have been very useful.   Drama?   Well maybe. 

Microsoft Excel spreadsheets now take custom live data

Who knew spreadsheets could be exciting?  By Jon Fingas, @jonfingas in Engadget

Microsoft is still finding ways to inject drama into spreadsheets. The Verge reports that Microsoft is giving Excel support for custom live data types, expanding the content you can include well past text, numbers and the occasional stock quote. You could slip a country’s data into a cell and create a formula that extracts the most recent population for your sheet, for example.

The approach works by using logic to structure the data you insert into a given cell, using Power BI to connect data types with Excel for business users. Existing cells can even be turned into linked data types, and you can use a Power Query feature to turn imported data into its own type. ... " 

Insert Spreadsheet by Photograph

Would be a nice thing to be able to do at times, especially for test data.

Import Excel data just by photographing a spreadsheet
The feature is available in the updated Microsoft Excel app for Android.

AJ Dellinger, @ajdell

Microsoft recently rolled out a number of major updates to its products, including a particularly handy new feature for Excel. Using the Android version of the app, users will be able to snap a photo of a printed data table and have it converted into a digital, editable spreadsheet. The feature, which is available to Microsoft 365 subscribers, isn't available on iOS yet but Microsoft is promising it will arrive soon.

The new import feature uses image recognition technology to identify all of the information on the page and convert it into an Excel spreadsheet in the app. It should eliminate the need to manually enter or copy data, depending on its accuracy. The feature, labeled in-app as "Insert Data from Picture," can perform the conversion from analog to digital with a single tap. Microsoft first floated the idea for this tool last year before being made widely available in the most recent update to the Android app. It's just the latest in the company's ongoing efforts to insert more machine learning models into its products. ....  " 

Excel Embeds the Knowledge Graph

This is the kind of thing I have been waiting for for some time.  The integration of knowledge about data with the data itself.   A kind of metadata.  An extension of the now common idea of 'spell checking'.   And using a 'knowledge graph' to do it.  Participated in some projects recently where this would have been very useful.  Its really a step towards useful intelligence in a commonly used business tool.   Looking forward to try it and see how it might be extended.

Microsoft Excel gets smarter with new rich data types  By Mike Wheatley in SiliconAngle.

 Microsoft Corp. is enhancing its Excel spreadsheet software with two new “rich data” types that provides a better way to access information on companies and places.

The geography and stocks data types allow users to pull information from Microsoft’s extensive Knowledge Graph and insert it inside their spreadsheets. The general idea is to make Excel smart enough to understand some entries and offer additional information, Kirk Koenigsbauer, Microsoft’s corporate vice president for the Office team, wrote in a blog post today.

For example, after adding a list of cities to a sheet, clicking on the Geography button would bring up a list of all the data Microsoft has on those locations, which can be accessed directly from within Excel. This includes information such as a city’s population, area, median income of its residents and so on.

Excel also draws on Microsoft’s artificial intelligence capabilities to help define any ambiguous entries. For example, if a user enters the common place name “Springfield” in a list of cities, Excel will reference the correct one depending on the context, or else it will prompt the users to choose which one they mean – such as “Springfield, Massachusetts,” or “Springfield, Missouri.” ... "

Microsoft: from Excel, to BI, to Advanced Analytics?

Excel is a legitimate starting place, that is used by many if not most enterprises.  So can it be a reasonable path for industry, when linked to right resources?   Good thoughts in linked piece.

Microsoft BI: From Excel to Power BI
Posted by Ravi Madhavan

Gartner's latest Magic Quadrant for Business Intelligence software ranks Microsoft as the Leader. Here's how their Power BI solution emerged as the evolution of Excel, along with some clarity on the various components and product names which Microsoft has been using. 

In 1985, Madonna was everywhere- on the radio, in the movies, and inside record stores on that new disruptive technology called compact discs. Back to the Future was the biggest movie of the year. Each Thursday night nearly 30 million households spent the evening watching the Cosby Show, Family Ties, Cheers and Night Court. It was also the year a ten-year-old Microsoft introduced a humble little program called Excel to the world. The story of Microsoft's rise to the top of the Business Intelligence software game with their Power BI product begins with Excel.

If there were a Mount Rushmore for software, Excel would make the cut. Of course, the real Mount Rushmore is a monument for past great, long dead presidents. So one may think BI, Big Data and Analytics would have made Excel little more than a flashback to the 80's and 90's. Think again. Estimates for total Excel users worldwide range from 400 to 750 million. Almost every organization around the globe still does some portion of their data analysis on Excel. It's fair to say even companies providing the most advanced BI and Analytics tools today still probably use Excel internally, although they may not want to mention it. ..... "

Solver Methods Improved by Frontline

Was an early user of Solver.   Should not be forgotten given all the new claims out there today. Solver works directly as an add-on to Excel 365.    And as mentioned below, this new version is cloud ready and free. Has been used and thus tested since 1990.   This is based on pre AI methods of optimization and modeling that proved successful for many years.   We used them successfully in many contexts. Ask me.  Let me know of your experiences.   Always worth looking at, especially if your data is already hosted in Excel.

In PRWeb: 
Frontline Systems, developer of the ‘classic Solver’ add-in that ships with desktop Microsoft Excel, has released a cloud-based, free Solver add-in that works equally well in desktop Excel 2016 and Excel Online – and handles the smallest to the largest, most challenging optimization models. Users can obtain Solver immediately from Microsoft AppSource. Over 100,000 existing “cloud Solver” users will receive the new version automatically.

“Today Frontline offers tools for the full spectrum of predictive and prescriptive analytics, but we’re still best known for Solver,” said Daniel Fylstra, president of Frontline Systems. “In our third-generation free version for cloud use, we’ve greatly extended Solver’s flexibility and power for everyone.”

Earliest Tool for Prescriptive Analytics is a Perennial Favorite
The Excel Solver, developed by Frontline in 1989, was first introduced in 1990 as part of Microsoft’s launch of Excel 3.0 and Windows 3.0 – partly in answer to the then-popular Lotus 1-2-3/G, which featured its own Solver tool, and Borland Quattro Pro, which had its own linear programming tool. Both Borland and Lotus later adopted Solver versions created by Frontline to replace their own tools. Twenty years later, the INFORMS professional society cited the “the overwhelming success and impact of Solver” when awarding Frontline its 2010 Impact Prize.   .... '

Allari on PowerPivot

Don't forget, if you have data, insight into your business problem, and software, you are a data scientist.   Here is a simple example:

The Business Intelligence Software You Didn't Know You Had   Posted by Ravi Madhavan

Users of MS Excel 2013 or above have BI capabilities built in with PowerPivot; a powerful, low-cost solution for collecting and analyzing your data.  This case study shows its implementation and productivity increasing abilities can be gained without investing too much time or money. .... " 

Excel 2016 Cheat Sheet

More like a good description of what is new, and even what is old and interesting.  A cheat sheet should be glanced at, not read. They have done a good job of hiding the complexity.  And there is lots of that.   Finding the right thing can be hard, especially if its new to you.  No doubt its powerful, the new data viz forms alone are worth the exploration.  Don't overplay it,  or try to build a user interface with it, but you should know it enough to munge your data with.

Excel 2016 cheat sheet
Are you getting the most from Microsoft Excel 2016? Get to know the key new features.
By Preston Gralla,  Contributing Editor, Computerworld  .....   " 

Doing Machine Learning in Excel

Although we never recommended using Excel for things like this in the enterprise, it might be useful for someone who is already an Excel expert to get started in machine learning methods.  From DSC, nicely done.

Doing real machine learning using only the basics of Excel.    Posted by Vincent Granville: 
In this article, I present a few modern techniques that have been used in various business contexts, comparing performance with traditional methods. The advanced techniques in question are math-free, innovative, efficiently process large amounts of unstructured data, and are robust and scalable. Implementations in Python, R, Julia and Perl are provided, but here we focus on an Excel version that does not even require any Excel macros, coding, plug-ins, or anything other than the most basic version of Excel. It is actually easily implemented in standard, basic SQL too, and we invite  readers to work on an SQL version. ... " 

Useful Excel Add-ins for Analytics

I have been brought in to clean up after the use of Excel for data storage and analysis.  Still, it is unavoidable to address it's use, especially, but not limited to the small business.  Here from DSC, a look at useful add-ins to Excel for analytical methods.  Some very powerful, that you should know about.    Regardless, I would always examine the consequences of long term use of Excel for any production-style use.

Spreadsheets

In the HBR:

An Ode to the Underappreciated Spreadsheet  by Alexandra Samuel

I used to teach accountants to use early spreadsheets, an example of where you could really impress people with technology.   I have also been brought in to fix up things that should not have been implemented in spreadsheets.  Both sides should be considered.

NodeXL: Creating Network Graphs from Excel

Simple, clever idea.

These are network graphs created with NodeXL, a template for graphing network data in Excel® (2007, 2010, 2013 and 2016).

NodeXL is a template for Microsoft® Excel® (2007, 2010, 2013 and 2016) on Windows (XP, Vista, 7, 8, 10) that lets you enter a network edge list into a workbook, click a button, see a network graph, and get a detailed summary report, all in the familiar environment of the Excel® spreadsheet application.  .... 

More.   .... 

Future of Excel in Business Intelligence

Via Paulina Gibson from Investintech:

" .... An interview with 27 Excel experts, where they talk about the future of Excel in Business Intelligence. ... "    Some good thoughts here.    Every enterprise uses Excel, so you can't ignore it as an existing consolidation place for data in the enterprise.  So also a place where business intelligence will live.   It will take some time to evolve away from this situation.   And effort's like Microsoft's Power BI will further slow the change.

(Update) I have added the comments on the interviews of my Excel Expert, Walter Riker below:
....

Exploratory Analysis with Excel

As the piece says,  still common in even very sophisticated organizations.    Data and metadata is often stored and, edited, monitored and analyzed in Excel.

Lotus 1-2-3 Support Ended

Historical note. We used Lotus extensively in the enterprise in the late 80s.  It was the second major electronic spreadsheet offering, after Visicalc.  During that same time we wrote some of our own spreadsheet like systems.  We also used it to integrate certain kinds of corporate data.  IBM has just dropped support.  I was unaware it was still around.

Machine Learning in Excel

Slide show on a number of techniques, including add-ons to Excel for machine learning.  Not much detail, but informative about software I had not seen as yet.

Excel Tips

Despite all the new packages out there, you are going to have to deal with existing systems written in Excel.  So it's very useful to understand how it works.  Here a set of simple tips for using it efficiently.   Useful, but also pretty selective, there are probably thousands more to consider.  I did find a few here that were on point.

Spreadsheet Day is Today

A correspondent reports that today is spreadsheet day. " ... in honour of the date that VisiCalc was first shipped, way back in 1979. ... "   I used to teach Visicalc back around that time, to some very amazed and appreciative accountant types. It was the first application that empowered the end-user, and caused organizations to buy PCs wildly , calling them for procurement purposes:  'calculators'. The rest is history.  There is even a site that celebrates spreadsheets.   With some history and silliness.

Top Data Science Languages

Vincent Granville usefully summarizes a survey of language use.

Top Languages for analytics, data mining, data science:

Very interesting study published by KDNuggets. Here are the highlights:

The most popular languages continue to be R (used by 61% of KDnuggets readers), Python (39%), and SQL (37%). SAS is stable at around 20%. The highest growth was for Pig/Hive/Hadoop-based languages, R, and SQL, while Perl, C/C++, and Unix tools declined. We also find a small affinity between R and Python users. ... Previous KDnuggets polls looked at high-level Analytics and Data mining software, but sometimes a full-power programming language is needed.  ... ' 

Useful yes, but the respondents here are followers of KDNuggets, skewed I believe to researchers rather than practitioners.    By my observation, Excel is still the most used in applications of these ideas, but mentioned nowhere in the article.   I think we need more than Excel to do our job, but we can't ignore its existence.