Query to Import Financial Statement in Excel

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

http://economictimes.indiatimes.com/tata-consultancy-services-ltd/profitandlose/companyid-8345.cms

– 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 –

http://economictimes.indiatimes.com/tata-consultancy-services-ltd/profitandlose/companyid-8345.cms

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-

http://economictimes.indiatimes.com/infosys-ltd/profitandlose/companyid-10960.cms.

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.