by James 

How to Import the Price of a Stock in Excel

10 Comments

Stock Prices in Excel

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.

Stock Prices 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.

About the author 

James

James is a data science writer who has several years' experience in writing and technology. He helps others who are trying to break into the technology field like data science. If this is something you've been trying to do, you've come to the right place. You'll find resources to help you accomplish this.

Leave a Reply

Your email address will not be published. Required fields are marked

  1. Hello sir, I am getting an error ….could not find function(“C1”)…….How to solve that problem…Thanks in advance….

    1. Hi Swapnil, can you be more specific about the problem you are having? I don’t have this package installed anymore, but I’ll try to help you through it the best I can.

      1. I may see the problem. If you are referring to the line number 7 in the code, i.e. price <- Cl(stock[nrow(stock)], that is the letter "l" and not a "1". In other words the Cl() function in quantmod is to get the closing prices.

        1. Oops!!!…My Mistake…..I thought its function with name C1 …Finally i can use code fetch current price in Excel…….Thanks a lot…..

        2. Hello sir, One more question sir….Whenever i run this script in console…It run without an error…But when i call a getPrice() function in Excel ….Excel just stop working….It shows Excel Not Responding…….I tried multiple times…but the results are same….Any suggestions ???

          1. It’s possible that the later versions of Excel do not support the interface. I haven’t used the interface in a while. Are you able to access the BERT console while trying to run Excel? If memory serves me correctly, I believe it should give you any messages as to why it’s hanging up your Excel. I will try to install this again today or tomorrow and see if it happens to me. I’ll let you know what I find.

          2. Hi Swapnil, I tried to install the package. But for some reason I was not able to get the BERT console to work. I noticed that the interface has not been updated since 2018. It doesn’t look like the developer is supporting it anymore. I did send a support message, but we’ll see if I get any response. I’ll let you know. Best Regards, Jim

          3. In the meantime, I have begun the search for other possible (and more recent) packages that could do something similar. That too, is something I’ll let you know if I find something.

  2. Dear James,
    I have tried to make a function from my code which is :
    install.packages(“htm2txt”)
    library(“htm2txt”)
    url<- "https://cactus.nci.nih.gov/chemical/structure/InChI=1S/C20H30O2/c1-12(2)14-11-13-7-8-15-19(3,4)9-6-10-20(15,5)16(13)18(22)17(14)21/h11-12,15,21-22H,6-10H2,1-5H3/stdinchikey&quot;
    inchikey <- gettxt(url)
    inchikey

    and I have ended up with this:

    library("htm2txt")
    getInchikey <- function (link) {
    url<- "link"
    inchikey <- gettxt(url)
    inchikey
    }

    which is not working. it would be so kind of you to let me know what is my mistake?

    Bests
    Rozgar

    1. I am not familiar with that package. However, when I tried your cactus.nci.nih.gov link that you included, it gave me an error. Then, I noticed that the very last part of the link has a " instead of an actual quote. My guess is that could be the source of your problems.

      Good luck,
      Jim

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Subscribe to get the latest updates