Excel- How to Pull Beta for Stock from Yahoo Finance

Or you could create a user defined function. To use it, put a stock symbol in a cell. For example, put INTC in cell A1. Then add the function to a different cell. For example, put this in cell B1:

=GetBeta(A1)

Thanks so much guys... tbh I have no idea what any of that means hah... this is kind of asking a lot but could someone walk me through- not the code but the applicability)

Do I go to Developer-> Visual Basic-> and then what?

Say I want the beta to appear in a certain cell too

Open the Visual Basic editor. You can do that through the menus or ribbon bar, or you can do it by pressing ALT-F11.

On the upper-left side of the editor you should see something that says Project - VBAProject. Below that in a window you should see something that says ThisWorkbook. Right click on that and insert a module. There should now be something below ThisWorkbook names Module1. Double click on that. In a large window to the right of the Project - VBAProject window is where you will be pasting the code provided above (the second post by me, that has the GetBeta function). The large window where you will paste the data should have 2 dropdowns above it. One says (General) and the other says (Declarations).

Once you have pasted the code in that window, go back to the Excel workbook and put a stock symbol in cell A1. In cell B1 enter the formula:

=GetBeta(A1)

Cell B1 should now contain the beta value for the stock.

Thanks so much mjbeam. I'm trying to learn VBasic but I need these codes ASAP.

Two more questions.

1) What if I want to get the Total Debt/Equity (https://finance.yahoo.com/q/ks?s=X+Key+Statistics)
2) Whats a quick and dirty way to learn how to program Vbasic ? Or rather read the language. I had experience in java 4 years ago. Forgot a good amount

In the code provided above, where it says t = xHttp.responseText, once this line is executed, the variable t will hold the HTML of the Yahoo webpage with all of the stock information. You will need to extract the information you need from t. You can get an idea of what the contents of t are going to look like by going to the website in your browser and right clicking on it somewhere and selecting "View Source" from the context menu that pops up. Then search for what you want to extract, in this case if I search for "Total Debt/Equity (mrq):" this is the data that will be around it in the variable t:



So, I can trim off the stuff I don't need before the data I'm looking for like this:


Then t will look like this:


Now to get to the 114.11I can strip off everything in t that comes before a ">" character 2 times like this:


...and t will look like this:


Now I'll strip off everything after and including the first "<" in t:


Now t holds the string, 114.11. We need to convert it into a number before we return it to the spreadsheet:



To extract the rest of the stuff you want to extract you just need to do some string manipulation on the variable t in the code I provided earlier. Also, you will need to modify thhe xHTTP.Open line to use the new URL you provided:


One other thing, each cell that has this formula is doing a unique query from a Yahoo server so don't over do it. This is not the best way to go if you are needing to pull thousands of stock quotes. And, if you need to pull multiple pieces of information for each stock, this is also not the best way to achieve that. It would be better to pull all the pieces of information in one function and return the results to multiple cells. The reason is because you don't want to do 10 queries to Yahoo to get the same web page but extract a different piece of information from each query. You want to do one query and extract all the info at once, then return those results to multiple cells on the worksheet.


Hope this helps.


To extract Total Dept/Equity (new function name is GetTDE):

Thanks mjbeam.

Had I not had your help I would've been screwed haha. How long would it have taken me to learn/develop this macro myself if I have 0 knowledge of VBasic? Is this more complicated VBasic?

And is it complicated to do this:

"One other thing, each cell that has this formula is doing a unique query from a Yahoo server so don't over do it. This is not the best way to go if you are needing to pull thousands of stock quotes. And, if you need to pull multiple pieces of information for each stock, this is also not the best way to achieve that. It would be better to pull all the pieces of information in one function and return the results to multiple cells. The reason is because you don't want to do 10 queries to Yahoo to get the same web page but extract a different piece of information from each query. You want to do one query and extract all the info at once, then return those results to multiple cells on the worksheet."

It is not complicated to pull multiple values for a particular stock, it's just tedious. If you know that you want to pull more than one item from the page per stock I could convert this into an array function.

If you are wanting to pull a dozen or so stocks, this will work fine. If you need to pull hundreds or thousands of stocks, you should find a website that lists this information for multiple stocks on a single page and extract it that way.

Do you know whats wrong with my code if I want to pull market cap? I tried modifying your code:

Function GetMC(ByVal tRange As Range) As Single


Dim xHttp As Object
Dim t, ticker As String

Set xHttp = CreateObject("Microsoft.XMLHTTP")

ticker = tRange.Text

xHttp.Open "GET", "http://finance.yahoo.com/q?s=" & ticker & "&ql=1", False
xHttp.Send


t = xHttp.responseText

t = Mid(t, InStr(t, ">Market Cap:<"))
t = Mid(t, InStr(t, ">") + 1)
t = Mid(t, InStr(t, ">") + 1)

GetMC = Val(Left(t, InStr(t, "<") - 1))


End Function