The puzzles facing Excel data analysts

In the world of data, data analysts who work with Excel are like a group of “knights holding the ‘Excalibur’”. Relying on Excel’s powerful interactive ability and ease of use, Excel knights cut through obstacles easily and perform data analysis efficiently. However, potential challenges are surging continuously in the said world. As the scale of data continues to grow and the requirements for data analysis are increasingly complex, relying solely on the ‘Excalibur’ seems insufficient to cope with the ever-emerging challenges. Excel is good, but it seems powerless when faced with complex ‘data enemies.’

For example, Excel knights may encounter the following puzzles in the data world:

1. Less capable of handling massive amounts of data

When facing millions of rows of data, the ‘Excalibur’ of Excel knight becomes “stuck”. For example, when analyzing the monthly sales trends, the sword will become slow under high-intensity calculations, falling into a ‘freeze’ state from time to time or a sudden ‘crash’ frequently, as if it has slipped into chaos. It seems difficult to navigate freely in the vast ocean of data relying solely on Excel’s capabilities.

2. Powerless to cope with complex puzzles

On one occasion, an Excel knight wanted to find out “the users who clicked on the same category of products at least 3 times every 7 days”. He tried many times in Excel but still couldn’t find a suitable formula – multiple-condition nesting, cross-table association and dynamic grouping. Excel’s response seems powerless in the face of such a complex series of “tricks”. After repeated attempts, the knight still found it difficult to break through the bottleneck.

3. Difficult to implement batch operation

Another Excel knight worked hard for several consecutive days to make hundreds of regional sales reports in order to meet the deadline, and eventually became exhausted. Although his ‘Excalibur’ is sharp, Excel’s solutions lack the convenience of batch processing. As a result, he had to copy and paste one by one. Over time, he inevitably became discouraged and complained of being exhausted mentally and physically.

Therefore, in the face of these puzzles, Excel knights begin to look for new “weapons” in the data world, hoping to obtain more powerful computing power while preserving the interactive advantages of Excel.

An attempt at programming: The dilemma of SQL and Python

It the world of data, it is said there are two major programming ‘sects’ that can help Excel knights improve their abilities, namely SQL and Python - they are called the Medieval Knights and French light cavalry of the data world, either of which has profound skills and infinite forces. However, although the two ‘sects’ are powerful, Excel knights found them unsuitable and difficult to truly meet their needs.

SQL: Medieval Knights, profound heritage but somewhat inflexible

Relying on databases, SQL demonstrates powerful capabilities, especially in processing structured data, and can be regarded as ‘authentic’. However, for those who are accustomed to Excel interactive operation, the disadvantages of SQL are obvious:

· Lack of flexible debugging due to the need to execute whole code

SQL advocates for ‘one move to win’, and each ‘move’ must execute the whole code, which is unlike Excel where adjustments can be made step by step. When you want to check the intermediate results, you have to split the complex query statement into multiple steps and use temporary tables to store the intermediate results. As a result, SQL men often need to make continuous modifications after executing a statement, which is very slow and laborious.

· Not good at flexible change

SQL mostly adopts a static thinking and lacks flexible control structures. For example, when faced with dynamic calculation involving multiple grouping or recursive query, SQL code becomes lengthy and complex, making it difficult for SQL men to intuitively implement flexible adjustment.

· Dependent upon database environment

Putting SQL’s skills to good use can only be implemented in a database, which is quite unfamiliar to those who are accustomed to working with Excel. Importing Excel data into database, writing SQL code and returning the results back to Excel is a complex and cumbersome process, which will really daunt Excel knights.

Python: French light cavalry, extremely skillful yet difficult to hit the core of puzzles

Python has come to prominence in the data world. With its comprehensive data analysis capabilities and powerful library support (such as pandas), Python has become the “new favorite” of many analysts. However, for Excel knights, Python still has several shortcomings that are difficult to overcome:

· Troublesome to check results step by step

Excel knights have become accustomed to checking the analysis results step by step. In Python, however, whenever a data analyst wants to view the changes between steps, he has to actively use the print() or display() function at each stage to output results, which is very cumbersome during the debugging process and makes many Excel knights feel uncomfortable.

· Non-intuitive to operate table data

For Excel knights who have got used to tables and formulas, although Python is powerful in table operation, it requires the help of pandas, which has numerous objects and obscure syntax. In addition, while Python code is not long, it makes people confused. For those who are not familiar with programming, working with pandas is much more complex than Excel tables and formulas.

· Cumbersome to configure

To use Python, one needs to install and configure several libraries, as well as set up the debugging environment before running the code. Compared to the ready-to-use approach that Excel knights are used to, the cumbersome steps of Python discourage many people.

SPL: The winning secret in the data world

In the data world, when Excel knights are trapped by complex requirements, worried about big data, and mentally and physically exhausted from repetitive operations, SPL (Structured Process Language) emerges like a “Heracles’ Bow”. It not only breaks through the limitations of Excel but also shows greater compatibility and flexibility than SQL and Python, truly allowing Excel Knights to wield a sharp tool to navigate the data world with ease.

High interactivity: Instant feedback, stepwise adjustments, effective techniques

The first technique of SPL is its high interactivity:

Ø Grid-style code: An important reason why SPL enables Excel knights to get up to speed quickly is its use of grid-style code.

SPL code is written in cells, which are naturally neat. It also allows for referencing previous calculation results using cell names such as A1. Similar to Excel, the cell names referenced in the code will automatically change when a row or column is inserted or deleted, and SPL supports the definition of variables. Therefore, SPL is not confined to a single method; it is flexible and varied.

Ø Instant feedback: After the SPL executes, the data effects are immediately visible, without waiting for full execution. The results of each operation can be viewed in real time, allowing Excel knights to determine their next action based on these results and effectively deal with challenges as they arise.

Ø Stepwise adjustments: When Excel knights want to modify the code of a specific step, they don’t have to start from scratch; they can directly adjust and run it, making it as convenient as adjusting an Excel formula.

This technique combines the flexibility of Excel with the power of programming languages, truly achieving ‘what you see is what you get.’ Furthermore, it allows for a quick start while providing interactivity equivalent to Excel.

Low threshold: ready-to-use, zero configuration, get started directly

The second technique of SPL embodies a minimalist philosophy: ‘great skill is simple and unadorned’:

Ø No need for complex configuration: SPL does not require the installation and configuration of a complex development environment, enabling Excel knights to get started easily with a simple installation.

This technique is specifically tailored for Excel knights, avoiding the complexity and obscurity of SQL and Python. It’s like adding a new technique to a familiar martial arts form. While the techniques change, the underlying principle remains the same.

Embeddable plug-in: give a powerful boost to Excel knights

The third technique of SPL is a ‘magical technique’ that makes Excel Knights feel especially familiar:

Ø Seamless integration of XLL plug-in: SPL integrates itself directly into Excel, enabling Excel knights to write code and view results directly in Excel without leaving the familiar environment.

Ø Plug and Play: SPL opens up a new battlefield for knights right within Excel, eliminating the need for frequent switching between different software or managing data imports and exports.

This technique transforms SPL from an external tool into a capable assistant of Excel knights, allowing them to say, “With Heracles’ Bow in hand, I can master the data world.”

Powerful computing: infinite possibilities, simple to handle complex calculations

The fourth technique of SPL embodies its ‘agility and elegance’ in computation.

Ø Easy implementation of complex calculations: SPL offers a wide range of libraries and data objects for tabular data computation, and it particularly excels in ordered operations on sets, making it easy to tackle challenging tasks in Excel.

For example, in stock analysis, calculating the periods where a stock has risen for more than 5 consecutive days is challenging in Excel. This is because Excel does not provide a method to retain the grouped subsets and cannot further filter the corresponding periods. In contrast, the task can be accomplished quite easily using SPL.

	A
1	=file("StockRecords.xlsx")
2	=A1.xlsimport@t().sort(CODE,DT)
3	=A2.group@i(CODE!=CODE[-1] || CL<CL[-1])
4	=A3.select(~.len()>5)
5	=A4.conj()

This technique retains the intuitiveness of Excel while significantly simplifying the implementation of calculations, making it a powerful tool to tackle the complex requirements of the ‘stubborn enemy in the data world’.

Big data: a technique of combining memory and external storage, flexible yet powerful

The fifth technique of SPL can be considered ‘tailor-made for handling big data’.

Ø Native big data processing capability: SPL supports cursors to handle storage computations that exceed memory capacity. The codes for memory and storage computations are almost identical (changing from import to cursor), which will not add extra workload. If further speedup is desired, using the @m option allows for direct parallel computation, fully utilizing multi-core hardware capabilities:

	A
1	=file("StockRecords.xlsx")
2	=A1.xlsimport@t().sort(CODE,DT)
3	=A2.group@i(CODE!=CODE[-1] || CL<CL[-1])
4	=A3.select(~.len()>5)

This technique allows Excel knights to finally say goodbye to the ‘lag crisis’ when facing big data, as if they have received guidance from a master in their internal training, taking their abilities to the next level.

Batch processing and Excel file generation: the terminator of repetitive tasks

The sixth technique of SPL can be considered a ‘sharp knife’ specifically designed for repetitive tasks:

Ø Batch processing capability: SPL possesses batch processing capabilities typically found in programming languages. This allows it to handle different files as well as multiple directories, truly embodying the saying ‘A heavy sword has no edge; great skill is simple and unadorned’.

Ø Seamless interaction with Excel: SPL can directly generate one or more Excel files, which achieves seamless integration with the original data tables and allows Excel knights to keep their familiar operating habits.

This technique completely liberates the hands of Excel knights, allowing them to say goodbye to repetitive work, escape from the ‘repetitive quagmire’, and reclaim the joy of being a knight.

SPL, with its high interactivity, low threshold, powerful computing capabilities, big data processing, batch generation capabilities, and seamless integration with Excel, addresses all the pain points of Excel knights. It offers them a unique set of techniques that distinguish them in the data world.

“With SPL, Excel knights will fear no challenge in the world of data. Their techniques will be more flexible, and their internal strength will be deeper. As long as they possess Heracles' Bow and Excalibur, every Excel knight can reach the pinnacle.”