Web Query to Import Financial Statement and Stock Data with Stock Quote { All Financial Statements in one sheet from -}
Method-1
http://www.advfn.com/stock-market/NYSE/IBM/financials?btn=annual_reports&mode=company_data
Sub Macro1()
'
' Macro1 Macro
'
'
SE =
Range("SE").Value
SS =
Range("SS").Value
With
ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.advfn.com/stock-market/" & SE &
"/" & SS &
"/financials?btn=annual_reports&mode=company_data" _
,
Destination:=Range("$A$2"))
.Name = "financials?btn=annual_reports&mode=company_data"
.FieldNames =
True
.RowNumbers =
False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle =
xlOverwriteCells
.SavePassword =
False
.SaveData =
True
.AdjustColumnWidth = True
.RefreshPeriod
= 60
.WebSelectionType = xlEntirePage
.WebFormatting
= xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh
BackgroundQuery:=False
End With
End Sub
Method-2
Importing Financial Statements in Excel Web Query
An
Excel Web query allows you to import data from a Web site into an Excel
worksheet. We're going to work through a basic example by taking TCS to import financial statements (Balance
Sheet, Income Statement and Cash Flow) in excel
from economic times.
A query has two parts- Static and Dynamic.
In this tutorial we will import the financial
statements by copy and pasting dynamic part of the query every time we have to
import statements of company.
Required
URL-
1- Go to economic times.
2- Add the stock symbol or company name in the right end
of the page. In our case Tata Consultancy Services Ltd.
3- Than go to financial tab. Profit & Loss or income
statement will be displayed in the bottom of the page under heading-
Profit & Loss - Tata Consultancy
Services Ltd.
Importing Statements-
First Step – Is same for all the three statements-
In this tutorial I will explain how to import income
statement rest will be imported in the same manner by adding
the specific URL.
‘Go to Data’– ‘From Web’
– ‘Paste the URL (a) ‘
– Click Go –
(Select the required data as shown, select cell as in this
case $A$1 is selected-
Go to properties- select refresh every (SELECT TIME) and
refresh data when opening the file.
Click ok
Then click ok-
Selected data will get import-
This process is very time consuming every time you need to
import the statement.
Retrieving statement by query-
Here are the steps to
retrieve data every time you want to import statement of other company with the
help of query.
In the query –
Bold part in red is
static and remaining blue is dynamic.
By adding the dynamic part every time we refresh the given
statement we can import the financial statement of any company
‘Select the data imported’ – ‘Right Click’ – ‘Edit Query’
– ‘Check the data to be imported is selected’ – ‘Click on save query “(Let it to be saved in
the default location as in Local Disk C in the example)”’- ‘Name the query as in example named as tcs
companyid-8345 – ‘Click Save’.
Close the window.
Got to the location
where query had been saved
Right click and select
Edit with Notepad -
Do the changes as shown
below-
Save the query.
How this works-
Go to sheet- Paste the dynamic part of the URL of the company
whose financial statements is to be imported in any cell of the sheet.
Now we will import Income Statement or Profit & Loss
statement of Infosys.
The URL of profit and loss statement of Infosys is-
We have to copy and
paste the highlighted part i.e. the dynamic part of the URL only.
Importing Statement-
o
Select the whole imported
data, delete it.
o
Select existing
connections-
o
go to the query, in e.g.
it’s by the name tcs
companyid-8345 –
o
open-
o
select the cell where you
want to import the financial statement-
o
Then select the cell where
you pasted the dynamic part of the URL of company Income statement to be
imported.
In our example J3-
static part of the company in this case Infosys-
o
Click ok- Income statement
of Infosys will be imported.
Screen Shots of the steps-
Existing connections
This window will prompt –
Select the cell where you want to import data. As in the
example $A$1 is selected-
After clicking ok- This window will prompt-
Select the cell where you entered the static part of the
querry-
Click Ok.
The income statement will get imported
For Balance Sheet after saving query change-
For Cash flow after saving query change-
Please let me know if you have any
questions.
Thanks.
