Applying Python’s Time Series Forecasting Method in Microsoft Excel – Integration as a Business Process Supporting Tool for Small Enterprises

Jolanta Litwin

a:1:{s:5:"en_US";s:50:"Politechnika Rzeszowska im. Ignacego Łukasiewicza";}

Marcin Olech



Anna Szymusik




Abstract

The paper describes the current state of research, where integration of Microsoft Excel and Python interpreter, gives the business user the right tool to solve chosen business process analysis problems like: forecasting, classification or clustering. The integration is done by using Visual Basic for Application (VBA), as well as XLWings Python’s library. Both mechanisms serve as an interfaces between MS Excel and Python to allow the data exchange between each other. Creating the suitable Graphical User Interface (GUI) in Microsoft Excel, gives the business user opportunity to select specific data analysis method available in Python’s environment and set its parameters, without Python’s programming. Running the method by Python’s interpreter can bring the results, which are hard or even impossible to obtain by using Microsoft Excel only. However, the data analysis methods stored in the Python’s script, which are available to the business user, as well as VBA source code, must be designed and implemented by the data scientist. Sample, basic integration between Microsoft Excel and Python’s interpreter is presented in the paper. To present value-added of the proposed software solution, simple case study according to time series forecasting problem is described, where forecasting errors of different methods available in the Microsoft Excel and Python are presented and discussed. The paper ends with conclusions according to the results of the current researches and suggested directions of further research.


Keywords:

time series forecasting, python integration, excel integration

BIRCH D., LYFORD-SMITH D., GUO Y. 2018. The Future of Spreadsheets in the Big Data Era. Proceedings of the EuSpRIG 2017 Conference “Spreadsheet Risk Management”. Imperial College, London, UK.   Google Scholar

BROWNLEE J. 2020. Introduction to Time Series Forecasting with Python: How to Prepare Data and Develop Models to Predict the Future. Machine Learning Mastery, San Francisco, p. 2-5.   Google Scholar

CHICCO D., WARRENS M.J., JURMAN G. 2021. The coefficient of determination R-squared is more informative than SMAPE, MAE, MAPE, MSE and RMSE in regression analysis evaluation. PeerJ Computer Science, 7: e623. DOI: https://doi.org/10.7717/peerj-cs.623.   Google Scholar

DONATI G., WOOLSTON C. 2017. Information management: Data domination. Nature 548: 613–614. DOI: https://doi.org/10.1038/nj7669-613a.   Google Scholar

EHRHARDT M., GÜNTHER M., TER MATEN E.J.W. 2017. Novel Methods in Computational Finance. Springer, Cham, p. 545.   Google Scholar

HANSUN S., KRISTANDA M.B. 2017. Performance Analysis of Conventional Moving Average Methods in Forex Forecasting. Proceedings of 2017 International Conference on Smart Cities, Automation & Intelligent Computing Systems. Yogyakarta, Indonesia.   Google Scholar

HYNDMAN R.J., ATHANASOPOULOS G. 2018. Forecasting: Principles and Practice. 2nd ed. OTexts, Melbourne, p. 57-58. https://otexts.com/fpp2/ (access: 18.06.2021).   Google Scholar

JANUSCHOWSKI T., GASTHAUS J., WANG Y. 2019. Open-Source Forecasting Tools in Python. The International Journal of Applied Forecasting, 55: 20-26.   Google Scholar

KIM S., KIM H. 2016. A new metric of absolute percentage error for intermittent demand forecasts. International Journal of Forecasting, 32(3): 669-679.   Google Scholar

KOH L., ORZES G., JIA F. 2019. The fourth industrial revolution (Industry 4.0): technologies disruption on operations and supply chain management. International Journal of Operations & Production Management, 39(6/7/8): 817-828.   Google Scholar

KURZAK L. 2012. Importance of forecasting in enterprise management. Advanced Logistic Systems, 6(1): 173-182.   Google Scholar

NELLI F. 2018. Python Data Analytics with Pandas, NumPy and Matplotlib. 2nd ed. Apress, Rome, p. 143-145.   Google Scholar

PENA-SANCHEZ Y., RINGWOOD J. 2017. A Critical Comparison of AR and ARMA Models for Short-term Wave Forecasting. Proceedings of the 12th European Wave and Tidal Energy Conference, Kildare, Ireland.   Google Scholar

RASCHKA S., MIRJALILI V. 2019. Python Machine Learning: Machine Learning and Deep Learning with Python, scikit-learn, and TensorFlow 2. 3rd ed. Packt Publishing Ltd., Birmingham, p. 207-211.   Google Scholar

SAABITH A.L.S., FAREEZ M.M.M., VINOTHRAJ T. 2019. Python Current Trend Applications – An Overview. International Journal of Advance Engineering and Research Development, 6(10): 6-12.   Google Scholar

SHIM J.K., SIEGEL J.G., SHIM A.I. 2012. Budgeting Basics and Beyond. 4th ed. John Wiley & Sons, Inc., Hoboken, p. 277-279.   Google Scholar

SIAMI-NAMINI S., NAMIN A.S. 2018. Forecasting economic and financial time series: ARIMA vs. LSTM. http://arxiv.org/abs/1803.06386 (access: 17.06.2021).   Google Scholar

SPEIGHT A. 2021. Visual Studio Code for Python Programmers. John Wiley & Sons, Inc., Hoboken, p. 3-49.   Google Scholar

SWAMYNATHAN M. 2019. Mastering Machine Learning with Python in Six Steps. A Practical Implementation Guide to Predictive Data Analytics Using Python. 2nd ed. Apress, Bangalore, p. 234-243.   Google Scholar

WINKOWSKI C. 2019. Classification of forecasting methods in production engineering. Engineering Management in Production and Services, 11(4): 23-33.   Google Scholar

Download


Published
2021-09-22

Cited by

Litwin, J., Olech, M., & Szymusik, A. (2021). Applying Python’s Time Series Forecasting Method in Microsoft Excel – Integration as a Business Process Supporting Tool for Small Enterprises. Technical Sciences, 24(1), 115–133. https://doi.org/10.31648/ts.7058

Jolanta Litwin 
a:1:{s:5:"en_US";s:50:"Politechnika Rzeszowska im. Ignacego Łukasiewicza";}
Marcin Olech 

Anna Szymusik 




License

Copyright (c) 2021 Jolanta Litwin, Marcin Olech, Anna Szymusik

Creative Commons License

This work is licensed under a Creative Commons Attribution 4.0 International License.





-->