Looking at some post on ozgrid I found great information on this http://www.diytraders.com website.
For future reference I am posting the code extracting the ex-date and dividend value from yahoo finance to paste it in Excel.
Thanks for your help
Code
Sub Dividend()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set DataSheet = ActiveSheet
qurl = "LUX PXD XOM TOT "
Yahoourl = "http://download.finance.yahoo.com/d/quotes.csv?s=" & qurl & "%20&f=%20qd"
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Yahoourl, Destination:=DataSheet.Range("A2"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("A1").CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
End Sub
Display More