Importing Stock Data with Stock Symbol Web Query

·    Method-1-

First record macro and import Income statement.

-       Than type symbol in cell A1 as shown.

-       Change A1 to Tcker as shown below.

-       Stop recording macro.

-       Go to VBA and update the code by-

Sub Macro2()

'

' Macro2 Macro

'

' Define Ticker

Ticker = Range("Ticker").Value

   

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;http://finance.yahoo.com/q/is?s=" & Ticker & "+Income+Statement&annual", _

        Destination:=Range("$A$9"))

        .Name = "is?s=" & Ticker & "+Income+Statement&annual"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = True

        .BackgroundQuery = True

        .RefreshStyle = xlOverwriteCells

        .SavePassword = False

        'Change savedata to false

        .SaveData = False

        .AdjustColumnWidth = True

        .RefreshPeriod = 60

        .WebSelectionType = xlSpecifiedTables

        .WebFormatting = xlWebFormattingNone

        .WebTables = "9"

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

End Sub

·        Method-2 –

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 get a feel for how Web queries operate. 

Required URL-

·        Stock Data.

Yahoo Finance- Enter Stock Symbol – Get the URL – Copy the URL (a)

e.g.- https://in.finance.yahoo.com/q?s=TCS.NS&ql=1

1-     Getting Stock Data-

‘Go to Data’– ‘From Web’ – ‘Paste the URL (a) ‘https://in.finance.yahoo.com/q?s=TCS.NS&ql=1 – 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-

Retrieving Data by stock symbol-

This process is very time consuming every time you need to retrieve stock data.

Here are the steps to retrieve data every time you change the symbol.

‘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 Stock Data’ – ‘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- Type the ticket symbol of any company in any cell of the sheet accept where data is or where you want to import data.

e.g. TATAMOTORS.BO

Select existing connections- go to the query – click open.

This window will prompt –

Select the cell where you want to import data. As in the example $G$1 is selected-

After clicking ok- This window will prompt-

Select the cell where you entered the stock ticket-

Click on Ok.

The data will get imported

If you want to import it to the same destination first delete the data. Than go to Existing Connections – select query – open – destination ($A$1) – Symbol - Ok

https://drive.google.com/file/d/0Bx3mfFH5R-y3U3VTbEJxRFdJZWM/view?usp=sharing