Excel: Retrieve Stock Quotes with a Spreadsheet Formula

77 Responses to Excel: Retrieve Stock Quotes with a Spreadsheet Formula

1. ChrisNovember 3, 2012 at 07:41

   Hello,

   I ama private investor and used Excel Stock quote add-in which has now been discontinued.
   I have copied your function StockQuote into an Excel file and it seems to work as a User defined Fuction but it returns #VALUE.

   Is it still up to date?
   Many Thanks for your time

   Christopher Brewer
2. financial-modelling.netNovember 5, 2012 at 19:05

   Thank you for mentioning this! There was indeed an issue with request dates, e.g., the following won't work:

   http://ichart.finance.yahoo.com/table.csv?s=MCD&a=10&b=3&c=2012&d=10&e=4&f=2012&g=d&ignore=.csv

   The reason seems to be that this date range only includes a weekend. This is unexpected, in a way, because the Yahoo! website itself automatically corrects such a request and shows data for the preceding Friday. The CSV web service apparently does not do that.

   Hence, in the macro, it makes sense to look back for a full week:

   dtPrevDate = dtDate - 7

   That way you will download data for a full week and still just take the quote from the most recent date.

   The article above is adjusted accordingly, and the code should work fine again.

   If you encounter other errors, try to un-comment the lines with "Debug.Print"; that would help identifying errors.
3. RobertNovember 11, 2012 at 19:20

   Hello,
   Thanks for the code, it works very well.
   However some stock prices have three or more decimals, like ticker UCG.MI, but your code retrieves two decimals only.
   Can the code be modified to get more decimals?
   thanks

   Rob
4. financial-modelling.netNovember 17, 2012 at 18:11

   Not sure whether this is possible via the ichart-based URL without the API. It has nothing to do with the code, this is only a question of what Yahoo provides and what you need to enter in the URL parameters. If you find out how to retrieve 3 or more decimals, do post it here!
5. RobertNovember 23, 2012 at 17:39

   Found it and replaced your function as follows.
   For more details, see webpage
   http://www.gummy-stuff.org/Yahoo-data.htm

   Function Shareprice(YahooTicker As String)

   Dim strURL As String, strCSV As Double

   ' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
   ' Example for Apple Inc. share price
   ' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1
   strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"

   Set http = CreateObject("MSXML2.XMLHTTP")
   http.Open "GET", strURL, False
   http.Send
   strCSV = http.responseText
   Shareprice = strCSV

   Set http = Nothing

   End Function
6. George FisherDecember 8, 2012 at 00:22

   For an Excel VBA function that returns any parameter in the Google Finance XML stream try

   http://www.philadelphia-reflections.com/blog/2385.htm
7. Jerry ZMarch 23, 2013 at 03:16

   I have not been able to get either function to run.

   The function StockQuote come up with a formula error (I cut and pasted so no typos).

   For Shareprice, all I get is #VALUE! error. Thoughts? I am a newbie creating VBA scripts in Excel.

   Running Office 2011 Mac. Thanks for any guidance. My goal is just to get today's share price into a cell.
8. Peter FMarch 27, 2013 at 22:34

   Thank you for posting this. I was able to make it work with just one problem I can't seem to solve. When I get the price for the date NOW() and for NOW()-1, I get the same value (both return the correct value for yesterday's price). Other calls with NOW()-7, NOW()-30, etc. seem to retrieve correct values. Why am I unable to get today's price?

   Thanks.
9. AndersApril 13, 2013 at 16:00

   Works great! Thanks!!
10. Darren LohApril 15, 2013 at 18:33

    It works for me! You are the best.
11. NileshApril 24, 2013 at 05:51

    Hey thanks so much! This saved my day buddy 🙂
12. Stocks and analysis | Analyze ITApril 24, 2013 at 23:19

    [...] process immediately in Excel without having to download each file seperately. From the website Financial-Modelling I modified the code to perform this fairly simple vba code. You can download the file which is [...]
13. MikeApril 25, 2013 at 04:09

    This is pulling in the previous close if I put in today's date. It is 10PM eastern time currently. I am using date 4/24/2013.

    AAPL comes in at 406.13, but that was the close on 4/23. It closed today at 405.46.

    More noticeable, AT&T (ticker T) was down 5% today. Running =StockQuote("T",DATEVALUE("4/24/2013")) returns 39. T closed at 37.04 on 4/24. Previous close (on 4/23) was 39.
14. RupeshMay 1, 2013 at 20:49

    Is it possible for it to retrieve the current live price? I find like Mike above if I use eg. StockQuote("Goog") it returns the previous day's close price.
15. MattMay 16, 2013 at 19:07

    This is great!
    Is there anyway to pull the yield and full name of the stock too?
16. RupeshMay 30, 2013 at 01:29

    How do make it auto-update / refresh eg when I open the file in Excel. Also how do I implement a button which I can click to update manually?

    Hope someone can please help!
17. algaeJuly 19, 2013 at 08:10

    hello,

    i had created a portfolio in Yahoo.http://in.finance.yahoo.com/portfolio/....1/view/..
    in the past i could import the data into excel by the 'import from web' button. Now the querry returns a prompt saying that no data could be found. May be the data is now in a different format or java or something ?
    Could you help?
    thanks
18. VinceSeptember 14, 2013 at 05:19

    This works great. Thank you very much for posting the module to accomplish this.
19. GBornOctober 2, 2013 at 20:41

    hi, I saved the macro as "StockQuote", but when I return to Excel sheet and put in the formula, it doesn't recognize the macro and says #NAME in the cell where I tried to call the function? In VB, I was able to name the project and of course the function to StockQuote, but it does still say that the Module is called Module1...Back in the worksheet where I called it, I don't see "StockQuote" or any macros under "View Macros"...thanks
20. ShawnNovember 12, 2013 at 07:22

    Thanks! This is awesome! I seem to be getting a problem with historical splits. For instance, for AAPL on 1/30/2004, I get 22.56 when it should be half that for the split adjustment. Is this a Yahoo! thing? Or is there maybe a way to query historical splits?
21. BearNovember 18, 2013 at 14:56

    Can someone explain why i cannot run the module? I've coppied the code into the vba module but when i try to run it, the module isn't visible in de MACRO's finder. Can someone give me a quick tutorial how to make it run?

    tnx in advance
22. BalaDecember 7, 2013 at 09:35

    Hi,

    Is there anyway we can get closing price for a particular date which is adjusted to split?
23. XavierDecember 27, 2013 at 22:16

    Hi,
    With American numeric settings this macro works fantastic!
    Unfortunately with my "," as decimal separator setting I tend to get the share prices x100. I think dividing every price by 100 is not always a perfect solution. Does anyone have a VBA solution?

    thanks
24. Jeff OJanuary 7, 2014 at 18:01

    Very nice. This function returns the actual close. I prefer to use the adjusted close (adjusted for splits and dividends paid). The change was pretty simple. Find the line

    dbClose = strColumns(4)

    change to

    dbClose = strColumns(6)

    Hope that helps
25. RenéJanuary 17, 2014 at 15:45

    Thanks for this great macro! I simplified the program and removed the date input. So my version only outputs the current stock price ("l1"). I guess that is what Rupesh and Mike asked for further above. The input has to look like this: =StockQuote("ticker_symbol").

    I had difficulties with this macro to show exchange rates (e.g. EURUSD=X). That's why I changed the url in my version to "download.finance.yahoo.com". Somehow there it works.

    Here is the code:

    
Function StockQuote(strTicker As String)

    Dim strURL As String, strCSV As String
    Dim dbClose As Double

    ' Compile the request URL
    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & _
    "&f=l1"

    ' Debug.Print strURL

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    ' Debug.Print strCSV

    dbClose = strCSV

    ' Debug.Print vbLf
    ' Debug.Print "dbClose: " & dbClose

    StockQuote = dbClose

    Set http = Nothing

    End Function

26. RenéJanuary 20, 2014 at 10:42

    Another thing that I learned: The line

    dbClose = strCSV

    should be replaced with

    dbClose = Val(strCSV)

    then the output value is independent of your locale, i.e. no matter whether you use a comma or dot as a number separator, the value will be as expected.
27. MikeFebruary 7, 2014 at 02:28

    Rene, great edit! That is exactly what I was looking for out of this macro. Rene, what about refreshing?
28. RenéFebruary 12, 2014 at 17:59

    Hi Mike,

    I added some code (all the credit goes to Nick - http://excelexperts.com/vba-tips-run-code-every-hour-minute-or-second).

    You can change the update interval (Line 8. It's set to 5 minutes right now). I wouldn't recommend to set a a very small time though. Yahoo seems to block too many requests from the same user. So if you have many ticker symbols and/or a short update interval the macro hangs for quite some time.

    Another note: After copying the code into your Excel sheet, it might be necessary to save, close and reopen the file in order to start the auto update.

    Dim TimeToRun

    Sub auto_open()
    Call ScheduleUpdateAll
    End Sub

    Sub ScheduleUpdateAll()
    TimeToRun = Now + TimeValue("00:05:00") 'Set the update interval
    Application.OnTime TimeToRun, "UpdateAll"
    End Sub

    Sub UpdateAll()
    Application.CalculateFull
    Call ScheduleUpdateAll
    End Sub

    Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "UpdateAll", , False
    End Sub

    Function StockQuote(strTicker As String)

    Dim strURL As String, strCSV As String
    Dim dbClose As Double

    ' Compile the request URL
    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & "&f=l1"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    dbClose = Val(strCSV)
    StockQuote = dbClose

    Set http = Nothing

    End Function
29. RenéFebruary 12, 2014 at 23:45

    I uploaded a video on youtube for users, who are not so familiar with VBA. It's a step-by-step instruction on how to implement this macro into your Excel workbook:

    http://youtu.be/3gfgZWy5778
30. financial-modelling.netFebruary 13, 2014 at 08:17

    Brilliant! I included the link in the article.
31. MerleFebruary 19, 2014 at 16:08

    Rene, Thank you...Thank you... Thank you. I've been trying to replace an Open Office plug-in called "GetHistory" which also retrieved Yahoo historical stock prices as it quit working some time ago. Thank you also Jeff O for posting how to retrieve the adjusted closing prices also. Those are the only historical prices that mean anything to me. With much struggle I got it working on Excel 2013... a program that I hate along with Windows 8. Anyway, it works and it has simplified my life.

    Thanks again,
    Merle
32. yucanFebruary 25, 2014 at 09:28

    Why is there no simple function to pull a stock price into excel such as google provides with its online spreadsheet (=finance(MSFT)).
33. leeFebruary 25, 2014 at 11:56

    I'm getting a #VALUE error when I use this...anyone know why? Im' using Rene's modified version.
    Can't figure it out.. I added the Val(...)
34. DanFebruary 27, 2014 at 20:01

    the code works great, thank you! One question, I cant seem to get SPX to work. Yahoo finance has spx as the ticker ^SPXPM, and it errors out every time. Other futures such as ^VIX work just fine. Anybody have any idea how to fix this?
    Thanks
35. Mike MFebruary 27, 2014 at 23:15

    Yeah, this doesn't seem to work on Mac Office 2011. I'm getting the same problem as Jerry Z, where the something is going wrong in the http connection & response, resulting in #VALUE.
36. Paste CSV string directly onto rangeMarch 18, 2014 at 10:54

    […] […]
37. Christopher HallMarch 18, 2014 at 14:17

    This is great! I'm trying to modify the code to get either the High or the Close price without much luck.

    Thanks
38. MichaelMarch 25, 2014 at 20:03

    Dan, Use ^GSPC.
39. MichaelMarch 25, 2014 at 20:08

    Rene, Thanks for the macro! It works great and was exactly what i needed.

    Would you have any idea how to retrieve another piece of Yahoo Finance data for comparison?

    There is a Field called "Mean Target" on the Analyst Opinion Tab within Yahoo Finance (http://finance.yahoo.com/q/ao?s=AAPL+Analyst+Opinion) that i'd like to compare to the stock prices to see which stocks have the most and least theoretical upside.

    My Macro/VBA understanding is insufficient to figure out how to adapt your code to perform this additional function.

    If you have any ideas, i'd be grateful.

    (I have also been an investor for a long time and think it will help you to make money in stocks.)
40. RenéMarch 31, 2014 at 14:04

    Hello Michael,

    there is just a limited amount of data which can be extracted from Yahoo (have a look at http://www.gummy-stuff.org/Yahoo-data.htm). Your "mean target" is not in this list, so I am afraid it won't be possible to get it into Excel (at least not with this type of macro).
41. BillMarch 31, 2014 at 20:25

    Awesome Rene!

    A couple of indexes I have tried don't work either like ^DJI and ^SSEC.

    I'm trying to replace MyYahoo's stock reporting. How do I figure out what the fields like Daily change and percentage are, from yahoo finance?

    Thanks ............... Bill
42. LarryApril 2, 2014 at 23:21

    I like the function but was a little leery of using a function on my spreadsheet where I track historical info. I wouldn’t want to update a sheet that has values for a previous period. So I wrote a little macro that I will run once, it’ll update my sheet with the current values for the stocks. Then I can save the spreadsheet and if I open it six months later it won’t try to recalculate by sending the command to yahoo again.

    The macro is based on the assumption that the ticker symbols start at cell A5; there can be as many as 40 symbols (blanks are skipped). The value returned by the function is put in the cell three columns to the right of the ticker symbol.

    Here’s some sample data (so you can see the format I have):

    Ticker Name Shares Price Total
    CAH Cardinal Health, Inc. 100 69.73 $6,973.00
    Z Zillow, Inc. 200 92.27 $18,454.00
    T AT&T, Inc 300 35.09 $10,527.00

    Sub UpdatePrice()
    '
    ' Go down list of symbols and update price column
    '
    Dim Symbol As String

    OrigAdd = ActiveCell.Address
    Range("A5").Select
    Symbol = ActiveCell.Value

    For Count = 1 To 40
    Do While Symbol ""
    x = StockQuote(Symbol)
    ActiveCell.Offset(0, 3).Value = x
    ActiveCell.Offset(1, 0).Activate
    Symbol = ActiveCell.Value
    Loop
    ActiveCell.Offset(1, 0).Activate
    Symbol = ActiveCell.Value
    Next Count

    Range(OrigAdd).Activate

    End Sub

    Hope that helps someone.

    -Larry
43. LarryApril 2, 2014 at 23:47

    
Sub UpdatePrice()
'
' Go down list of symbols and update price column
'
Dim Symbol As String

    OrigAdd = ActiveCell.Address
    Range("A5").Select
    Symbol = ActiveCell.Value

    For Count = 1 To 40
    Do While Symbol ""
    x = StockQuote(Symbol)
    ActiveCell.Offset(0, 3).Value = x
    ActiveCell.Offset(1, 0).Activate
    Symbol = ActiveCell.Value
    Loop
    ActiveCell.Offset(1, 0).Activate
    Symbol = ActiveCell.Value
    Next Count

    Range(OrigAdd).Activate

    End Sub
44. BillApril 14, 2014 at 21:20

    Hello - would it be possible to use this data set to download TTM EPS data?
45. NischalApril 29, 2014 at 11:35

    Hi Rene,

    Your code works cool with NYSE, however i follow National stock exchange (NSE) and would like to get the quote for NSE.
    The stock codes are available at Yahoo finance with the code names suffixed by .NS
    eg "IDFC.NS" or "AXISBANK.NS" etc.
    now the code does not work for them (maybe because of a dot in the code) so can you update the code to work with the other exchange codes available on Yahoo finance but suffixed by a DOT
46. vikyMay 11, 2014 at 21:37

    iam using the below code for pulling historical data into excel from moneycontrol.com . for each page of the website its taking one sheet and a total of 8 to 10 sheets in excel because of the code written like that. i need little help from you, i want you to modify a little, that all the pages containing data should be imported in one sheet in excel only.

    Private Const URL_TEMPLATE As String = “URL;http://www.moneycontrol.com/stocks/hist_stock_result.php?sc_id=RI&pno={0}&hdn=daily&fdt=2000-01-01&todt=2013-11-01″
    Private Const NUMBER_OF_PAGES As Byte = 7

    Sub test()
    Dim page As Byte
    Dim queryTableObject As QueryTable
    Dim url As String

    For page = 1 To NUMBER_OF_PAGES
    url = VBA.Strings.Replace(URL_TEMPLATE, “{0}”, page)
    Set queryTableObject = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=ThisWorkbook.Worksheets.Add.[a1])
    queryTableObject.WebSelectionType = xlSpecifiedTables
    queryTableObject.WebTables = “3″
    queryTableObject.Refresh
    Next page

    End Sub
47. GrantJune 16, 2014 at 06:52

    I like Renee's idea with the auto-updater and Larry's idea of utilising an x=stockquote(symbol) and x=value of specified cell

    But I would like to know if it is possible to somewhat combine the two and use prices fetched from yahoo...without having to type the full formula into excel each time.

    E.g. I want to have the formula built into the cell, and all I have to type is the code AAPL which converts into the price (rather than typing: stockquote("aapl")

    Is this possible??

    Thanks so much guys, this macro has already made my days much easier!
48. NOYBJuly 11, 2014 at 01:02

    Thank you. Both for the original article and code and for Rene's mods.

    Microsoft really should provide a built-in function for such a commonly used data set as stock prices. And not just for the open close high low. But for much of the other parameters as well. Like P/E, PEG, bid, ask etc. For both current live and historical data.

    Again thank you all for this very useful VBA macro.
49. NOYBJuly 11, 2014 at 04:14

    Added option for specifying which value to return(strFieldName As String):
    Date
    Open
    High
    Low
    Close
    Volume
    Adj Close

    Usage:
    =StockQuoteHistory("INTC","Adj Close",DATEVALUE("7-10-2014"))

    Or using cells references to provide the parameters.

    =StockQuoteHistory($A2,G$1,$B2)

    Where:
    Cell G$1 is a field name such as "Adj Close" (typically the column header)
    Cell $A2 is a ticker symbol such as "INTC" (typically the row header)
    Cell $B2 is a date such as 7/10/2014

    Field Name defaults to "Close" if the parameter is missing or empty, like so...
    =StockQuoteHistory("INTC")
    =StockQuoteHistory("INTC",,DATEVALUE("7-10-2014"))
    =StockQuoteHistory("INTC","",DATEVALUE("7-10-2014"))

    
Option Compare Text
Function StockQuoteHistory(strTicker As String, Optional strFieldName As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.

    If IsMissing(dtDate) Then
    dtDate = Date
    Else
    If Not (IsDate(dtDate)) Then
    StockQuoteHistory = CVErr(xlErrNum)
    End If
    End If

    If IsMissing(strFieldName) Or strFieldName = "" Then
    strFieldName = "Close"
    End If

    Dim dtPrevDate As Date
    Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
    Dim dbClose As Double

    dtPrevDate = dtDate - 7

    ' Compile the request URL with start date and end date
    strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
    "&a=" & Month(dtPrevDate) - 1 & _
    "&b=" & Day(dtPrevDate) & _
    "&c=" & Year(dtPrevDate) & _
    "&d=" & Month(dtDate) - 1 & _
    "&e=" & Day(dtDate) & _
    "&f=" & Year(dtDate) & _
    "&g=d&ignore=.csv"

    ' Debug.Print strURL

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    ' Debug.Print strCSV

    ' Field Positions Index
    If (strFieldName = "Date") Then intFieldIndex = 0
    If (strFieldName = "Open") Then intFieldIndex = 1
    If (strFieldName = "High") Then intFieldIndex = 2
    If (strFieldName = "Low") Then intFieldIndex = 3
    If (strFieldName = "Close") Then intFieldIndex = 4
    If (strFieldName = "Volume") Then intFieldIndex = 5
    If (strFieldName = "Adj Close") Then intFieldIndex = 6

    ' The most recent information is in row 2, just below the table headings.
    ' The price close is the 5th entry
    strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
    strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
    dbClose = Val(strColumns(intFieldIndex)) ' 4 means: 5th position, starting at index 0

    ' Debug.Print vbLf
    ' Debug.Print strRows(1)
    ' Debug.Print "dbClose: " & dbClose

    StockQuoteHistory = dbClose

    Set http = Nothing

    End Function

50. AlexSeptember 1, 2014 at 20:28

    Hi,

    Did any mac user found a way to do the same on Excel for Mac or Numbers ?
    This one do not seem to work. Sadly.

    Thanks
51. Jay KnepperSeptember 12, 2014 at 00:43

    Thanks so much, it just WORKS!!!
52. KurtSeptember 29, 2014 at 08:25

    Hi Rene,
    This is really really cool! I tested it out tonight. Do you have code that pulls the 52-week high and low, dividend info? Thanks!
53. SuchinNovember 7, 2014 at 17:53

    Rene,

    How does one get the name of the Stock as well using your code? I was able to get other parameters but Name seems to fail.
    Thanks

    Function StockName(strstkname As String)

    Dim strURL As String, strCSV As String

    ' Compile the request URL
    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strstkname & "&f=n"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    StockName = strCSV

    Set http = Nothing

    End Function
54. PPKNovember 11, 2014 at 01:30

    This code works on Excel, Office for Mac 2011. It will put the quote to the right of the cell with the macro call.

    Function StockQuote(strTicker As String)

    Call StockQuote2(strTicker, ActiveCell.Offset(0, 1).Address)

    End Function

    Function StockQuote2(strSymbol As String, strDest As String)

    Dim strURL As String

    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strSymbol & "&f=l1"

    With ActiveSheet.QueryTables.Add(Connection:="URL;" & strURL, Destination:=Range(strDest))
    .PostText = ""
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With

    End Function
55. DannyNovember 11, 2014 at 11:37

    I have the same problem as Xavier, I'm using a European format where we invert decimal points with commas.
    Is there a work around to this, or extra code to implement ?
56. JoshNovember 12, 2014 at 16:35

    Thanks for publishing this. Is there any way to alter the code to import historical market cap data?
57. WalkerNovember 18, 2014 at 23:00

    I'm using Excel 2011 for Mac. As others have noted, this doesn't seem to work. I can add the function in VBA but the spreadsheet does not "see" it. I just get #NAME? in the cell. Ant suggestions?
58. CarloNovember 28, 2014 at 20:22

    @PPK: This works well also on my Mac - thanks

    Have the function to be necessarily called by another cell? I mean any way to get the result in the same cell?
59. BernardNovember 29, 2014 at 20:05

    I cannot get any version of this to work on Excel 2011 for Mac. PPK, exactly how did you create the function and save it?
60. AlejandroNovember 30, 2014 at 12:29

    For my case it does not work, as response text from yahoo is only including and not as line separator, and responseText is only able to get the first line. Did anyone see the same issue?
61. RobDecember 7, 2014 at 11:27

    Hi,
    Thanks for posting, great work.
    I'm finding that it works well for established stocks, but not for new IPOs (MPL.AX) or stocks where the ticker has changed (CFX.AX changed to NVN.AX).
    Any ideas on how to fix?
    Thanks,
    Rob
62. DanDecember 10, 2014 at 00:24

    Thanks Rene, it works fine. Does anyone know how I can get open, high and low data as well? Thanks to all.

    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & _
    "&f=l1"

    Where in here does it indicate the data field you want?
63. EdDecember 25, 2014 at 19:05

    I put stock symbols in column B, starting with cell B5. I then update prices in column D, using PPK's code. Stock symbols have to match Yahoo, i.e. BRK/B is BRK-B in Yahoo.

    Sub Quotes()
    '
    Dim Symbol As String
    Dim Comma As String
    Comma = ","

    Range("B5").Select
    Symbol = ActiveCell.Value
    Range("B6").Select
    Do Until IsEmpty(ActiveCell.Value)
    Symbol = Symbol & Comma & ActiveCell.Value
    ActiveCell.Offset(1, 0).Range("A1").Select
    Loop
    Range("B5").Select
    ActiveCell.Offset(0, 2).Range("A1").Value = StockQuote(Symbol)
    Range("A1").Select
    End Sub
64. John RamboDecember 28, 2014 at 07:07

    For Suchin's name function issue, try declaring the function as a string... such as:

    Function StockName(strTicker As String)as String
65. RobertJanuary 4, 2015 at 20:37

    This is awesome, I have never used VBA prior to today and was able to get the code to work the first time. Does anyone have a similar code for importing call bid premium.
66. CalixtoJanuary 8, 2015 at 13:20

    Here is how I solve de problem decimal points and commas:

    dbClose = CDbl(Replace(strColumns(4), ".", ","))
67. FasiJanuary 10, 2015 at 07:07

    Thank You it works for me..
68. AnthonyAugust 11, 2015 at 00:23

    I'm using two macros 1 for price history, and one for current price, using what I have seen above.

    Everything was running well until today. My current price (stockquote) quit working for me, and just returns a 0. Is this a yahoo issue? Did something change? Never had this issue before. Below is my code I'm running:

    Dim TimeToRun

    Sub auto_open()
    Call ScheduleUpdateAll
    End Sub

    Sub ScheduleUpdateAll()
    TimeToRun = Now + TimeValue("05:0:00") 'Set the update interval
    Application.OnTime TimeToRun, "UpdateAll"
    End Sub

    Sub UpdateAll()
    Application.CalculateFull
    Call ScheduleUpdateAll
    End Sub

    Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "UpdateAll", , False
    End Sub

    Function StockQuote(strTicker As String)

    Dim strURL As String, strCSV As String
    Dim dbClose As Double

    ' Compile the request URL
    strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & "&f=l1"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    dbClose = Val(strCSV)
    StockQuote = dbClose

    Set http = Nothing

    End Function

    I also use this code for historical prices:

    Function StockPrice(strTicker As String, Optional dtDate As Variant)
    ' Date is optional - if omitted, use today. If value is not a date, throw error.
    If IsMissing(dtDate) Then
    dtDate = Date
    Else
    If Not (IsDate(dtDate)) Then
    StockPrice = CVErr(xlErrNum)
    End If
    End If

    Dim dtPrevDate As Date
    Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
    Dim dbClose As Double

    dtPrevDate = dtDate - 7

    ' Compile the request URL with start date and end date
    strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
    "&a=" & Month(dtPrevDate) - 1 & _
    "&b=" & Day(dtPrevDate) & _
    "&c=" & Year(dtPrevDate) & _
    "&d=" & Month(dtDate) - 1 & _
    "&e=" & Day(dtDate) & _
    "&f=" & Year(dtDate) & _
    "&g=d&ignore=.csv"

    ' Debug.Print strURL

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    ' Debug.Print strCSV

    ' The most recent information is in row 2, just below the table headings.
    ' The price close is the 5th entry
    strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
    strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
    dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0

    ' Debug.Print vbLf
    ' Debug.Print strRows(1)
    ' Debug.Print "dbClose: " & dbClose

    StockPrice = dbClose

    Set http = Nothing

    End Function

    Any help as to why my stockquote quit working would be appreciated.
    Thank you!
69. matoAugust 19, 2015 at 17:24

    Hi. So has anyone figured out how to make it work in Excel on Mac, please? Thanks.
70. BeobachterAugust 27, 2015 at 11:41

    Great function!

    Does anyone understand why the formula works perfectly in my spreasheet with swiss stock symbols like JBGOUA.SW and CBCHN.SW, but NOT with others like IUSA.SW and IETA.SW ?!?

    Many thanks in advance...
71. BrianDecember 21, 2015 at 23:37

    OK, I just tried it an after much fooling around, figured out that the date as shown in the example above doesn't work now. You need to format the date cell as a DATE. I used the 3/21/2012 format, and now it works just fine.

    Now the only issue is that even at 5:30, it is pulling in the closing price from the day before. The summary quote on Yahoo still lists the previous close, which may be the issue. Does anyone know at what point Yahoo updates that info>?
72. Pulling data points from multiple webpages - Page 3February 28, 2016 at 15:28

    […] document.write(''); A quick google search turned up these two links that you may could use: https://www.linkedin.com/pulse/20140...me-stock-price Excel: Retrieve Stock Quotes with a Spreadsheet Formula | Financial-Modelling.net […]
73. julioMay 24, 2016 at 02:53

    Anybody has this working in MS Office 2016 for Mac?
74. rafaelJuly 12, 2016 at 00:17

    Hello!
    I can get this VBA to work on pretty much any ticker, but it doesnt seem able to return Brazillian's Ticker "BOVA11.SA" and "IVVB11.SA".
    Both are ETF's.. anyone know's how to fix this or why it doesnt work on them?
75. Kat81inTXAugust 12, 2016 at 18:31

    I'm trying to get this to work on MS Office 2016 for Mac, and the code fails at this statement:

    Set http = CreateObject("MSXML2.XMLHTTP")

    It has been a long time since I coded in VBA, and I've never used the MS DOM to write code, so I'm not finding the problem with that statement via searches on MS support forums.

    Thoughts?
76. Kat81inTXAugust 12, 2016 at 18:57

    By the way, that statement doesn't fail in MS Office 2016 for Win.
77. tim o'toolAugust 24, 2016 at 12:53

    hi guys. iam using the original macro provided and it works perfectly for what I need it. however, it does e.g. not work for ^ftse... any idea why this explicit one does not work properly?

    thanks and regards,
    tim

This site uses Akismet to reduce spam. Learn how your comment data is processed.