+ Reply to Thread
Results 1 to 23 of 23

VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Hi everyone!

    I have a spreadsheet that contains a stock symbol with a validation so I can change the symbol from a given list. This is in cell: B4 in spreadsheet A for example. Now I am in Spreadsheet B and have a cell H1 that is linked to the stock symbol in B4/spreadsheet A.

    In Spreadsheet B I am trying to get Balance Sheet and Income Statement data for the stock symbol that I have selected.

    Web query's failed for me for two reasons

    1) I want the data pulled to be dynamic so that if I change the stock symbol in my validation cell B4, the relavant balance sheet and income statement is pulled
    2) I want to be able to send the data to other people in my company that dont have the query file saved on their desktop

    is vba is the only way to do it?

    Can anyone help with the code to be able to do this in excel? Thanks a ton!
    Last edited by akash1229; 07-31-2013 at 09:55 PM.

  2. #2
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Anyone willing to help?

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Welcome to the Forum akash1229!

    VBA is the only way I know to solve this problem automatically. Otherwise you are required to refresh the web query manually. The code would go in the module for Worksheet (spreadsheet) A, because that is where you change the stock symbol. It would look something like this:
    Please Login or Register  to view this content.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Here is more detail needed to complete the code:
    Please Login or Register  to view this content.
    Replace A1 with the cell where your web query is placed. Replace the string "Spreadsheet B" with the name you see on the tab for that sheet.

  5. #5
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Thank you 6stringJazzer. I was refraining from using the webquery since I will be sending the spreadsheet to other people in my firm. They will not have the webquery file saved on their PC.

    is there a way to entirely pull the data through VBA? the weblink and all the requirements through code and not through webquery.

    I dont know VBA so any help would be very much appreciated.


    Quote Originally Posted by 6StringJazzer View Post
    Here is more detail needed to complete the code:
    Please Login or Register  to view this content.
    Replace A1 with the cell where your web query is placed. Replace the string "Spreadsheet B" with the name you see on the tab for that sheet.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    I don't understand where you are getting the data from. The web? A web query isn't stored in a separate file, it's built into the Excel file. Attached is an example. If you type a stock symbol, VBA code will automatically retrieve the corresponding stock data. You can see the code in the worksheet module.

    (Also, since you are a new member I'll note that you don't have to quote an entire post when responding to it; takes up a lot of space. You don't need to quote at all, or you can just quote the part that's relevant.)

  7. #7
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Thanks for reply 6StringJazzer. The excel file is exactly what I want plus an additional row for Total Assets for the stock.

    I was achieving the web query by clicking Data - Web connections - typing finance.yahoo.com and pulling the relevant tables - then saving that webquery onto my hard drive - to open the file in notepad and editing it.

    The above did not work for me at all. If I wanted to do exactly what you did from scratch.. what should I do to get stock information and the balance sheet data?

    Your file is pretty much what I want to acheive plus the balance sheet data.

    Thanks a ton.

    P.S: I will keep in mind not to quote the whole post.

  8. #8
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Also would it be possible for the code/macro to gain the data from another cell on another sheet?
    The cell containing the data is in =Fee_Summary!B4

    I want it to look at the cell and select the stock that is prevalent in that cell and then update the data accordingly.


    Thank you very much!

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    You need to move the code to the sheet containing the cell that will change. That code will detect the change then make the changes on the sheet containing the web query.

    The way that I built this code was to use the Macro Recorder and then build the web query. That captured the code needed to create the query in VBA. Then I replaced the stock symbol in the URL with a variable. I set the variable to the cell in the worksheet containing the stock symbol.

  10. #10
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Please Login or Register  to view this content.
    I made the above changes that I have put in Bold. I copied your code into sheet containing stock symbol, but wanted the destination to be another sheet. But I get the error that "Destination range not in same sheet as query is created on"

    I do not know VBA, I am trying trial and error so any help would be appreciated.

    Also, I do not get "Total Assets" as the output from the webquery, how do I change the code to get that value?
    Last edited by akash1229; 08-05-2013 at 02:00 PM.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    The easiest way to resolve this is:

    Move the code for Sub RefreshQuery into the module for Sheet1
    Change the call to the sub from
    Please Login or Register  to view this content.
    Change the following code
    Please Login or Register  to view this content.
    where Sheetx is the name of the sheet containing the stock symbol

    If you still have trouble, post your entire workbook instead of just the code.

    Also, I do not get "Total Assets" as the output from the webquery
    It looks like you just copied my query instead of creating your own. I don't know where you expect to get Total Assets from; it's not on the page. A web query returns tables selected from a web page. It doesn't retrieve the whole page or arbitrary text. If you right-click on the data from the web query and click on Edit Query you'll see the green checkmark to show what is selected on what page.

  12. #12
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    I am not sure what you meant by

    Move the code for Sub RefreshQuery into the module for Sheet1
    Change the call to the sub from

    I tried to move the code after Sub RefreshQuery into different modules but to no avail. When I changed the Refreshquery to Sheet1.Refreshquery, it gave me an error everytime.

    I have sent my spreadsheet to you through private message. Please let me know if Google Drive does not allow you to download.

    Thank you.

  13. #13
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Anyone willing to help?

  14. #14
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    I thought I responded to your PM but I don't see it in my outbox, so I apologize for that.

    The Forum frowns on solving problems by PM because the community does not benefit from the solution.

    If the file does not contain private data then please attach the file, or at least post the URL here and I (or other members here) can post back a file with a solution.

    Is there some reason you don't want to attach a file to a post on this forum? If your file contains private data then I will have a look and post only my code on this forum. I do not have any way of returning the completed file back to you however.



    Quote Originally Posted by akash1229
    [URL removed in consideration of possible privacy concerns]

    Here is the spreadsheet to which I am trying to gain Total Assets and other financial data for the stock selected in Fee_Summary!B4

    I want the output in another sheet. eg. Sheet1

    The webquery for balance sheet through Yahoo is http://finance.yahoo.com/q/bs?s=CHMG...e+Sheet&annual

    I can get data for a single stock using the above weblink but when I try to get dynamic data it fails.

    Thanks a ton!

  15. #15
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Thanks for the reply. I did not get your reply in my inbox. There are some privacy concerns with the file I sent you through direct message and hence I decided to work on the macro and webquery as a separate file.

    Please find the link below to my spreadsheet.
    https://docs.google.com/file/d/0B3Sr...it?usp=sharing

    So far I have managed to run a macro and record the webquery. But to run the webqueries I need to create a macro button. The code for both webqueries is in Module 1 and Module 2

    I want to find a way to refresh the webquery automatically and paste the data in the destination when the Cell value containing the stock (B1 in this case) changes. From what I have read it is done through VBA? I dont know how..


    Again, Thanks a lot.
    Last edited by akash1229; 08-13-2013 at 09:37 AM.

  16. #16
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Here is a revision. It automatically calls your macros if the stock symbol changes. You can still use the buttons to update manually. There is one problem with your code:

    Please Login or Register  to view this content.
    The variables Key and Statistics are never assigned values so I don't know what you intend to do there.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Thank you very very much! This is what I wanted.

    Two things:

    1) When I copy this code to my Original spreadsheet (the one I could not share publicly) All I need to change is the Stock Symbol Cell value and the Range in the webquery right?

    2) is there a way I can choose to have the stocksymbol cell in another sheet?

  18. #18
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    duplicate post removed
    Last edited by 6StringJazzer; 08-14-2013 at 09:00 AM. Reason: duplicate post

  19. #19
    Registered User
    Join Date
    07-31-2013
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    Fixed it! Thanks man! Appreciate all the help!!
    Last edited by akash1229; 08-14-2013 at 10:49 AM. Reason: fixed the issue

  20. #20
    Registered User
    Join Date
    11-17-2014
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    2

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    First off, I want to thank you both for this thread. This is a huge help. I have been getting into macros at work but am trying to write this one for personal use. My only question is how to you determine what to set ".Name" to in the RefreshQuery subs? I also might run across an issue with setting which webtable to pull from. How do you know what to set that equal too?

    Sorry if these are common sense answers.

  21. #21
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    grayronn26, although your questions are related to this thread, they are specific to your own situation. Please start a new thread with your question (I also suggest attaching your file) according to our rules:

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  22. #22
    Registered User
    Join Date
    02-27-2015
    Location
    saudi arabia
    MS-Off Ver
    2010
    Posts
    1

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    sir i need to extract the financial data from the stock market data for which i need vba code,The stock market is Saudi Arabia Name TASI (Tadawul). Could any one provide me the vba to pull the financial data such as balance sheet, cashflow, income statement information from the below website.

    http://www.tadawul.com.sa/wps/portal...!/?symbol=1090




    Thanks in advance

  23. #23
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell

    fardeenkhan2008 , please take the time to review our rules. There aren't many, and they are all important.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ 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. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  2. Replies: 0
    Last Post: 10-28-2012, 07:22 AM
  3. [SOLVED] Stock market data find symbol from csv data file, copy and paste to Master workbook
    By wkurukul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2012, 10:14 AM
  4. Can anyone help with a stock count / balance sheet?
    By pontelad in forum Excel General
    Replies: 4
    Last Post: 05-16-2012, 11:25 PM
  5. excel base stock balance sheet...
    By johncena in forum Excel General
    Replies: 2
    Last Post: 11-25-2010, 12:28 AM

Tags for this Thread

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