I work with Microsoft Excel quite often as do many others. I use Excel to track my stock portfolios. Often, this entails getting prices for the stock online and updating Excel (manually). There is no function (that I am aware of) that does this natively in Excel. This article will describe how to import the price of a stock in Excel.
Previously, I found a library that fit the bill for exporting prices. It was a third-party library called BERT. It was created to allow Excel users to extend the functionality of spreadsheets using the R language. Coders would create a functionality in R to retrieve prices with a library such as Quantmod. Then, Excel users needed only to include the name of the function as a formula.
As is often the case with third-party packages (especially free ones), BERT stopped working about a year ago. When I look on their website, the latest update was 2018. I reached out to the developers about plans for the future or fixing the current library. I never heard back.
Python Is the King of the Road
I love the R language, but it's not secret that Python has taken over as the quintessential leader in the data science programming space. I have been a reluctant convert to Python, and because of my love of R, I was holding out. R is not a dead language and it still pays to learn it. But clearly, learning Python is a more strategic move at this stage of the game.
I've gotten up to speed in Python (not yet an expert, but working on it). I have also found that financial libraries for Python seem to be better supported than R. That wasn't the case a few years ago when I first implement my BERT solution. If you are in the technology field, you already know how volatile the environment is. Things change. It's a fact of life that all of us need to accept.
For this tutorial, I will be using Python for reasons described in the past few paragraphs. Also, the functionality is not going to be like BERT, where it's integrated into Excel. You'll need to run the Python code to update the spreadsheet. Then, you'll need to open up the spreadsheet after the process runs.
Isn't This a Roundabout Way to Load Data?
While it's not ideal to run processes outside of Excel to update data, I believe this paradigm will be the future for Excel (or other spreadsheets). The capacity of spreadsheets is being taxed with our current data demands. Big data is becoming massive data, and spreadsheets are not meant to handle this work load.
While spreadsheets are not going away anytime soon, we'll need to rework how we retrieve the data and update the spreadsheets. Keeping everything self-contained inside a spreadsheet takes up memory, which will be in short supply with spreadsheets soon enough, if not already.
The paradigm I introduce in this article may seem a bit of a roundabout solution. However, I am seeing a shift in the trends towards more robust data processing in organizations. As many of these organizations use spreadsheets, when they run out of memory or disk capacity, they'll need to find ways to solve these problems.
Microsoft and other spreadsheet creators will address the problem eventually. However, it's not likely they bring Excel up to the challenge. Instead, Excel will serve as a front-end dashboard and data processing will happen in another layer. This layer will likely be on the cloud somewhere, as cloud providers can handle big data, massive data, and distributed processing.
