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

Anyone willing to help?

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.

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.

Originally Posted by 6StringJazzer

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.

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.)

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.

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!

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.

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.

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.

Anyone willing to help?

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.

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!

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.

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?

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.

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.

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

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.