+ Reply to Thread
Results 1 to 35 of 35

Obtain historical stock prices for multiple symbols at the same time

  1. #1
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Obtain historical stock prices for multiple symbols at the same time

    Hello. What I would like to do, with either VBA or a simple web query, is be able to obtain the closing day adjusted stock price from a day in the past for multiple symbols at a time.

    I have a mac, and am able to web query historical prices for one symbol at a time from yahoo's historical prices page: http://finance.yahoo.com/q/hp?s=YHOO.

    I am also able to web query the current price for several symbols at at time.

    If someone knows of a site that displays historical prices for several symbols at a time, that would be helpful.

    I suppose I could write a script that queries each historical price symbol by symbol, but this seems very inefficient.

    Thanks for the help.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Here is a UDF that I found on the internet a while back and have used successfully myself

    Please Login or Register  to view this content.

    Syntax is =Stockquote(CellAddressOfSymbol,CellAddressOfDate)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Hi Alan. Thanks for the reply.

    I tried using the UDF code you sent, but I get #NAME? back, with the error message "This formula contains unrecognized text." I've attached the spreadsheet for you to view.

    Also, looking through the code, it seems that this only returns historical prices for one symbol, no? Have you come across something that returns historical prices from one specific day for multiple symbols at the same time? I would still like to know why this isn't working for me, but ultimately I would like to be able to grab multiple symbol prices at once. Thank you.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    Something like this might help?

    http://investexcel.net/multiple-stoc...der-for-excel/

    FYI, If you are going to do more than about 50 at a time you should add something to refresh the cache like

    Please Login or Register  to view this content.
    Inside the loop.
    Last edited by scottiex; 01-11-2015 at 06:27 PM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Opened your worksheet. Typed =Stockquote(A1) in B1 and received 7.99 back
    In E1, I typed =StockQuote(D1,D2) and it returned 7.99

    If you create a list of stock symbols it will work for each symbol in your list. Not sure what you issues are.

    Here is an example of a spreadsheet I have used.

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Stock
    Date Specific
    12/31/2014
    2
    DJIA
    17823.07
    3
    ^IXIC
    4736.05
    4
    ^GSPC
    2058.9
    You need to save this file as an .xlsm file as it contains a macro
    5
    ^TNX
    2.17
    to create a User Defined Function called StockQuote.
    6
    AAPL
    110.38
    7
    MS
    38.8
    The syntax is =StockQuote(Address for Symbol, Address for Date[optional])
    8
    K
    65.44
    ie. =StockQuote(A16, D1)---->
    41.35
    9
    T
    33.59
    If you don't include a date, then it defaults to current
    10
    QQQ
    103.25
    11
    MO
    49.27
    12
    PNW
    68.31
    13
    PRU
    90.46
    14
    CVX
    112.18
    15
    VGT
    104.48
    16
    XLK
    41.35

  6. #6
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Hmm, I keep getting #NAME? in the cell with the error message "This formula contains unrecognized text."

    When I type the "=" sign and then the letter "S," I don't see the function StockQuote as a choice with the rest of the Excel functions. It shows up at the very bottom in the User Defined Functions section as "Sheet1.StockQuote," and when I try selecting it and passing it cell A1, I get the message "That name is not valid."

    I also tried compiling the vba code and first got an error message saying "http" was not defined. I then defined "http" as a string, and then got the message "Object required," with "http =" highlighted.

    Any thoughts?

    And thank you also scottiex for the site and the suggestion.

  7. #7
    Registered User
    Join Date
    04-02-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Obtain historical stock prices for multiple symbols at the same time

    Eric, did you insert the StockQuote function into a VBA module in your workbook? I'm asking because the StockQuotes Error.xlsm file you provided above does not include the code. You'll have to open a VBA module and copy the StockQuote function into it. Then, when you enter "=S" in cell B1, you should see the StockQuote function as one of your choices in the dropdown list. I just finished trying it and it works fine.

  8. #8
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Hello nadewar. Just inserted the code into Module 2. Now at least I do see the StockQuote function from the list to choose from, but I still get the #NAME? and error message about "This formula contains unrecognized text."

    Just to clarify, user defined functions have to be stored in the Modules to work? I thought it could go in any of the worksheet listed under the Microsoft Excel Objects folder.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Eric, Suggest you upload your new worksheet for analysis.

  10. #10
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Ok here it is. I'm a bit new to VBA so maybe I'm not understanding where the Module is supposed to be. Do they always stay in the Personal Macro Workbook folder, or should there be Modules specific to each open file you are working with?
    Attached Files Attached Files

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Ok. Part of the problem analyzing your work is that you don't have the UDF in the worksheet. A clue from your last post leads me to believe that it is in your personal file. So in order for us to evaluate, you need to have it in the workbook we are working with.

    Whether to put it in the workbook or your personal file is strictly up to how will you use it. Will you use it globally with multiple workbooks? If yes, then put in peronsal. If you will only use in one workbook, then don't clutter you personal file and keep it in the workbook. And yes, it should be in a module.

    I am having upload issues with the forum right now. Look at this link for a spreadsheet that uses the UDF with multiple symbols. https://app.box.com/s/adjyjrfnvzf7n9l4u3vi
    Last edited by alansidman; 01-12-2015 at 08:37 PM.

  12. #12
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Oh I see. Now it's in the Module, in the folder specific to this workbook, and now I am getting the #VALUE error, saying a value used in the formula is of the wrong data type.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    progress? : )

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    I opened your file and it immediately gave me values of 3.88 in B1 and 7.99 in E1. So I am not sure what the issue is. Do you have macros enabled?

    Are you missing any references when you open tools in the VBE? Perhaps Visual Basic for Applications is indicated as missing?
    Last edited by alansidman; 01-12-2015 at 08:47 PM.

  15. #15
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Yeah I did have macros enabled. I tried in a new file and now I am getting the "variable not defined" message for "http" in the code. I tried taking off Option Explicit and I don't get the "variable not defined message," but I still get #VALUE in the cell instead of the stock price. I included a screen shot of the variable not defined message.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    And here is a screenshot of my spreadsheet showing #VALUE in the cell.
    Attached Files Attached Files

  17. #17
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    I opened you file and it works for me. Remove the Option Explicit for this UDF.

    Did you open the file I posted to Box.net? When you open it, does it work for you?

  18. #18
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    try adding

    Please Login or Register  to view this content.
    at the beginning of the code

  19. #19
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Still no. And I tried downloading the box.net file, but still get the #VALUE message.

  20. #20
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Here are my tool reference settings.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Tried inserting the Dim Http As Object code and still doesn't work. Though I don't get the error while the VBA is running. Just still displays #VALUE in the cell.
    Attached Files Attached Files

  22. #22
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    Does the spreadsheet from the site I gave you fail as well?

  23. #23
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    yes also displays #VALUE

  24. #24
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    oh wait did you send the Larry's Indices one or something else?

  25. #25
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Yes. Larry's indices. Its a file I created for a friend using the UDF. I am at a loss at this point. I wonder if this is a MAC vs PC issue. I have no other options in my arsenal.
    Last edited by alansidman; 01-12-2015 at 09:55 PM.

  26. #26
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Oh are you scottiex? I just tried downloading the Multiple Stock Quote Downloader that scottiex suggested yesterday, though it turned out to be Mac Fonts. Not sure if the stock spreadsheet is contained somewhere in there.

    What little I have read about Mac Excel tells me that accessing the web is a bit more difficult than on a PC, no? Perhaps that might have something to do with it.

    I do very much appreciate your help though. If you think of anything else, please let me know.

  27. #27
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    this file

    http://investexcel.net/wp-content/up...Downloader.zip

    I don't have a mac but I think he fixed it for mac
    if not maybe try this

    http://investexcel.net/wp-content/up...a-MAC-test.zip

  28. #28
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Got "method or data member not found" message for the first one. It highlighted Connections. I'll try the second one now.

    Please Login or Register  to view this content.
    Last edited by alansidman; 01-12-2015 at 11:25 PM.

  29. #29
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    And for the second one I get the "No Data For These Tickers" in the spreadsheet itself. I knew I should've been a writer : )

  30. #30
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Just tried the second one again. The first time I put in my own symbol, and the dates that came back were out of order, but I did get something.

    I ran it again and got a run-time error, but at least something.

    I'm off to bed, but thanks again for all of your help. I'll try going through the code. That should be a good way to pick up some VBA. Gnite.

  31. #31
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    Do you mean it lists the stock symbols under "no data for these tickers"?

    It is supposed to create the stock prices on the tabs so the "close" tab is the one I guess you are interested in.

    If it doesn't work at all - i guess I'm at a loss - seems you just cant connect to yahoo finance....
    Last edited by scottiex; 01-12-2015 at 10:56 PM.

  32. #32
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Obtain historical stock prices for multiple symbols at the same time

    "and the dates that came back were out of order"

    Do you mean like your date format was different to the date format it is using, and it was sorting the dates as text?

  33. #33
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    I ran scottiex' first downloaded file and it works for me. I think that scottiex is on the right track when he says that your issue may be connectivity with yahoo finance. Can you try this with a different internet service provider. I wonder if you are blocked by your ISP.

  34. #34
    Registered User
    Join Date
    09-19-2014
    Location
    Miami, FL
    MS-Off Ver
    Mac 2013
    Posts
    67

    Re: Obtain historical stock prices for multiple symbols at the same time

    Thank you for your efforts, but don't have option of trying with a different ISP.

  35. #35
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Obtain historical stock prices for multiple symbols at the same time

    Try a local coffee shop, Starbucks, Panera and test on their Wifi.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Retrieve Historical Stock Prices?
    By Robert K in forum Excel General
    Replies: 8
    Last Post: 03-18-2012, 04:42 AM
  2. Import Historical Stock Prices from Yahoo
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2011, 12:15 AM
  3. [SOLVED] Historical Stock Prices in Excel
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 02-10-2006, 10:50 PM
  4. Importing historical adjusted stock prices?
    By Schultz in forum Excel General
    Replies: 3
    Last Post: 02-02-2006, 01:55 PM
  5. historical stock prices
    By Stevo in forum Excel General
    Replies: 1
    Last Post: 01-24-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1