Although Python has become the de facto tool for machine learning and data analysis of large datasets, it is often under-utilized by stock market analysts as it lacks a UI for simplified access to its functions. The integration of Python into Excel from Q4 2024 is a major upgrade which can give stock market analysis an easy on-ramp to using Python from within Excel. Note that currently, Python in Excel is for the online version of Excel only, the installed version of Excel does not have Python on the roadmap this feature will likely be added as both versions have long-term feature parity.
Here is an overview of the enhancements the integration of Python into Excel brings for stock market data analysts.
Efficient Handling of Time-Series Data
A weak spot in Excel's extensive feature set is its limitations in working with time-series data. Excel has no internal capabilities for resampling data (for example, aggregating 1-minute timeframe data into 1-hour timeframes) or adjusting for timezone shifts which are necessary when harmonizing data fed from different exchanges. Michael Hanson, CTO at FirstRate Data noted that “the most common support requests we receive are how to resample or reformat high-frequency data from within Excel”.
Performance
Excel calculates cells sequentially which can cause performance lags when processing large spreadsheets with complex in-cell formulas. Python, by contrast, allows for asynchronous multi-thread calculations which can avail of the multiple cores in most modern processors.
In addition, Excel has a very limited number of native data types - numbers, for example, are a single data type which is optimized for convenience and not efficiency. Python allows analysts to target the most memory-efficient data type (such as an integer for counting processes) which improves performance.
Access to Python's Data Science Libraries
Many of the most popular libraries for data analysts are Python native - such as Pandas (for time-series analysis), Numpy for efficient numerical operations, or PyTorch for machine learning. With the addition of Python to Excel, these libraries are available from within Excel.
Although Excel ships with native functions which overlap with these libraries they are typically vastly inferior to the performance of dedicated libraries. For example, stock option valuation using Monte-Carlo simulations requires large volumes of random numbers. The native random number (RAND) function in Excel generates numbers typically exhibiting serial correlations and is not appropriate for use in modeling applications. By contrast, the Python numpy library outputs random numbers which are sufficiently independent for accurate modeling.
Code Sharing and Reuse
Excel workbooks can be shared and modified for reuse, but in general, the routines within a workbook are very difficult to abstract and use from within other workbooks. For example, a binomial lattice used in financial derivatives valuation usually spans over a hundred inter-connected cells and is difficult to copy and reuse in another workbook. By contrast, Python natively allows for function encapsulation which can then by easily used from any Excel workbook.
