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.
In the R language, there is a package that you can use to get the prices of stocks easily. You include the library (quantmod) and then call the function getSymbols(StockSymbol) and pass in the stock symbol.
The getSymbol function returns a structure of stock prices. This structure is an xts zoo package. This is a fancy package for handling time series data among other features. However, for the purposes of getting the latest stock price, it’s not necessary to worry about the history of prices. I just need the latest.
UPDATE May 28, 2020 – In response to helping a reader with a problem, I loaded the software on my machine. The add-in does not seem to recognize any changes that I make to register the functions that I write. I have reached out to the creators of the BERT package, but the last update I saw from them is in 2018. It’s likely they are no longer supporting the package.
I am currently looking for a new solution and will update this page when I find something comparable. My guess is that newer versions of Excel are having difficulty with this add-in. My suggestion is to hold off from loading this add-in as one user stated it causes problems with his version of Excel.
Please do not proceed without reading the update in the previous two paragraphs!
How to Marry R and Excel
Being the lazy SOB that I am, I didn’t want write a scraper in VBA, especially since the functionality exists in this quantmod package in R. If there only was a way to tie the two together…
There are packages that allow you to call Excel functions. However, only one truly stood out and did exactly what I needed. Here are three popular packages, the third being my choice.
This package looked promising at first. But, then when I read what it needed to make it work, I decided to look for something else. It needed DCOM packages and a proxy. I probably could have made it work, but if you have ever worked with DCOM before, it can be a PIA.
This is a great package when you want to read and write Excel files from R. In order to make it work for my solution, I would have to get all the prices for my portfolio in R, then save those prices ahead of time. I would need to use code to add new stocks to my list. Plus, it’s an intermediate step, which this lazy SOB programmer doesn’t like!
This is an acronym for Basic Excel R Toolkit (BERT). It does exactly what I wanted to do. I can access R functions right from Excel. The code that you write automatically is available to Excel as soon as you save the R file. What’s even nicer is BERT includes a console that you can run the code to test to see if they produce any errors. This saves a ton of time.
How It Works
You’ll need to install a program that you download from the site. It will install an Add In to Excel and create a default folder for you to add functions. The BERT interface uses that default folder to look for functions that are inherent in BERT along with the functions you’ll write.
BERT is dual-function, meaning you can control R in Excel or control Excel from R. Both of these can be done in real time. I have not tried the interface to control Excel from R. For this particular solution, I only needed the Excel controlling R aspect of the program.
Once the BERT software is installed, start an instance of Excel. Then, go to the Add-ins menu item.
Once you click on the Add-ins, you should see an option for BERT Console:
When you click on this option, a new window will open. It will be the BERT console:
You won’t have the Custom.r tab available as that is something I created. You can create as many of these files as you’d like as long as they reside in the proper folder (more below). Any file you create that you want integrated into the system must end with the extension .r
Your instance of BERT Console won’t load the xts and zoo packages the first time running it. After you are finished with loading the proper modules for this tutorial, however, you should see a screen similar to mine above.
Install quantmod Package
In the instructions console (right), type the following:
The quantmod package has some dependencies. If you get errors regarding some of those dependencies, try install those packages from the BERT console. In the past, by reinstalling the dependencies it worked for me when I ran into an error. If you are still getting errors after doing this, contact support of BERT.
After you install the packages, try typing the following:
If you don’t get any errors, you are good to go!
Start With a Simple Function
Before you start adding too much code, you want to see if the BERT interface will recognize your code. From the file menu, choose File->New
You should see a blank page on the left with the title “Untitled-1” as follows:
Let’s write a function called MyValue that returns the value 5. After you create the function make sure you save the file. Let’s call it Lesson1.r to make it easy. You can call it whatever you like as long as you use .r as the extension.
Once you save the file, it will “compile” your code immediately and make it ready for you to use. If you did not type the code correctly, it will show you the errors in the console. Otherwise, it will show that your module is loaded.
Run MyValue() in the console and you should see the value 5 returned:
The next step is to run the command in Excel. When you compiled the code by saving it, the BERT interface puts it into the library of available functions. All BERT functions that are available to Excel will have the prefic R. The MyValue will be shown as R.MyValue in Excel. You can call up a list of available functions in Excel by typing the equals sign (=) just like you would any native Excel function. If you can’t find your MyValue function when you hit the equals sign try hitting the letter “R” immediately after.
Select your function and don’t forget to add the open and close parentheses if Excel doesn’t do it for you. It is just like calling any other Excel function except it will have a prefix of R.
If all goes well, you should see the value 5 in the cell you called the function in.
How to Get Stock Prices
Let’s step up our game and implement what this post is supposed to accomplish, i.e., getting a stock quote. I won’t go into too many details about how quantmod works. For simplicity, you can copy the code as is and use it for your in your code.
Here is a quick explanation of what the code does:
The function is called getPrice and requires one parameter, i.e, the symbol of the stock you want to lookup. The code will call getSymbols from quantmod. This returns a series of stock prices with the last item in the list being the most recent stock price. It’s also the one we want to extract. That is why I used nrow() to get the last indexed item. I then convert the structure into a data frame which makes it easy to index. In this code sample I called this new structure pricedf. Then, I index the first row and column which will the latest price.
Save the file so that it updates it into the system. I call it compiling even though R is an interpreted language and isn’t technically compiling the code.
Try the function out in the console. You will need a stock ticker to pass into the function. I use MSFT (Microsoft) for my example:
Be aware the value you receive will likely be different than the one shown here. That makes sense as the closing prices change from day-to-day.
Now, let us call the function in Excel. The function name will be R.getPrice() and you must pass in a symbol:
You should get the latest price of Microsoft in the cell you entered the formula into.
Another option is to set up an Excel column of stock symbols and call the function reference the column of symbols. Again, your custom function is called in the same manner as any other native Excel function.
This is illustrated in the image to the left!
The BERT Console Is Attached to Your Excel Session
If you are wondering if you need to have the BERT console open for the interface work correctly, you don’t. The BERT session is integrated with the Excel session. The next time you fire up Excel, the custom functions that you wrote will be available for your use.
That is pretty cool in my book!
The data that quantmod retrieves is free of charge. It comes from Yahoo Finance or you can switch sources to get the data from Google. Both companies cannot guarantee the data is free from errors. Therefore, you should verify any prices before acting on them.
Also, the data is meant for research purposes and you should proceed with caution when using that data for any other purposes. Please don’t misconstrue this as legal advice. Therefore, if you have other intentions for this data, please consult a legal professional.
Is this the best way to get stock information into a spreadsheet? I would maintain that it’s not bad and it serves my purposes. But, there likely are other ways to accomplish this. It is one choice that you could use when you want something that works. I haven’t tested the solution extensively by any stretch. So, if you use it for any major applications, I cannot make any claims of it being durable. But, it works for this solution.