Web Query

[Guest]

Thank you in advance for any help provided to the subject question. I am
operating Excel 2003. A data query has been established to pull investment
data from finance.yahoo.com (i.e. stock volume, % ownership of management,
etc.). The query works fine and pulls approximately 50 values from the
webpage. My questions is this: If I have a list of 100 stock symbols how
can I link that list to the web based data query to automatically insert each
symbol into the data query individually and then dump that data automatically
to another worksheet within the same workbook. Should I use a marco, pivot
table, etc.

I also have a follow up question if time pemits. If I have a drop down box
within a worksheet how do I link that to the data query so I can simply pick
an individual stock symbol and then have the data query pull the related data
from the web?

Thank you again for any help provided to this question.

 

[R.VENKATARAMAN]

1.is the query address is different for each scrip.
2.can each scrip data confined to one row
in which case you can create a vba macro after
downloading the data the data can be transfered to master sheet
that is atleast what I am doing for my scrips in <in.finance.yahoo.com>
there are also other ways in in.finance.yahoo.com

 

[Guest]

Thank you so much for your advice, the answers to your comments are below:

(1) The query address for each stock symbol is different.
(2) How do I create the VBA macro you speak of?

 

[R.VENKATARAMAN]

the data is like this in the active sheet from 4th row (4th row headings and
5 6 7 data act
col A col B col c etc (next line)
company name/ yahoo code/ yahoo
code/LTP/DATE/TIME/CHANGE/OPEN/HIGH/LOW--these two lines are in 4 throw
ABHISHEK IND ABHP.NS =====this is 5th row col A and B
ADOR WELDING ADOR.NS =====6th row
AKSH OPTIFIBRE AKSO.NS ======7th row
after the column headings only col A and B are there. other column
automatically filled after the sub is run

now run this code

Public Sub Aclear() 'this clears the data which are filled due to sub
Worksheets("sheet1").Activate
Range(Cells(5, 3), Cells(Rows.Count, Columns.Count)).Clear
End Sub

Public Sub Bdownloaddataxp()
Dim source As Range
Dim lastcell As Range
Dim dest As Range
Aclear ' this clears the data from col C to J except headingss
Set dest = Range("c5")
Set source = Range("B5")
Set lastcell = Range("a5").End(xlDown).Offset(1, 0)
Dim sNWind As String
Do
'the next two line are one line
'source is the variable of the scrip code inorproated in the web url
sNWind = "URL;http://in.finance.yahoo.com/d/quotes.csv?s=" & source
&"&m=b&f=sl1d1t1c1ohgv&e=.csv"

Worksheets("sheet1").Activate
Dim oQryTable As Object

Set oQryTable = Worksheets("sheet1").QueryTables.Add( _
sNWind & ";", source.Offset(0, 1))
oQryTable.Refresh False

source.Offset(0, 1).Copy

Set dest = dest.Offset(1, 0)
Set source = source.Offset(1, 0)
Loop Until source = lastcell
Ctexttocol
End Sub

Public Sub Ctexttocol()
Range(Range("C5"), Range("C5").End(xlDown)).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Comma:=True
Columns("e:h").NumberFormat = "0.00"
Columns("I:I").NumberFormat = "#,##0"
Columns("a:k").AutoFit
Columns("e:e").NumberFormat = "dd-mmm-yy"

Range("a5").Select

End Sub

after running this code the columns c to J will be filled with formats etc.
you can modify to suit you
this is what I used to get the current data and daily run this sub after
trading is over. naturally daily I copy the downloaded data to a master
sheet to give historical data.

 

[R.VENKATARAMAN]

i forgot to add run the sub<Bdownloaddataxp>

the data is like this in the active sheet from 4th row (4th row headings and
5 6 7 data act
col A col B col c etc (next line)
company name/ yahoo code/ yahoo
code/LTP/DATE/TIME/CHANGE/OPEN/HIGH/LOW--these two lines are in 4 throw
ABHISHEK IND ABHP.NS =====this is 5th row col A and B
ADOR WELDING ADOR.NS =====6th row
AKSH OPTIFIBRE AKSO.NS ======7th row
after the column headings only col A and B are there. other column
automatically filled after the sub is run

now run this code

Public Sub Aclear() 'this clears the data which are filled due to sub
Worksheets("sheet1").Activate
Range(Cells(5, 3), Cells(Rows.Count, Columns.Count)).Clear
End Sub

Public Sub Bdownloaddataxp()
Dim source As Range
Dim lastcell As Range
Dim dest As Range
Aclear ' this clears the data from col C to J except headingss
Set dest = Range("c5")
Set source = Range("B5")
Set lastcell = Range("a5").End(xlDown).Offset(1, 0)
Dim sNWind As String
Do
'the next two line are one line
'source is the variable of the scrip code inorproated in the web url
sNWind = "URL;http://in.finance.yahoo.com/d/quotes.csv?s=" & source
&"&m=b&f=sl1d1t1c1ohgv&e=.csv"

Worksheets("sheet1").Activate
Dim oQryTable As Object

Set oQryTable = Worksheets("sheet1").QueryTables.Add( _
sNWind & ";", source.Offset(0, 1))
oQryTable.Refresh False

source.Offset(0, 1).Copy

Set dest = dest.Offset(1, 0)
Set source = source.Offset(1, 0)
Loop Until source = lastcell
Ctexttocol
End Sub

Public Sub Ctexttocol()
Range(Range("C5"), Range("C5").End(xlDown)).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Comma:=True
Columns("e:h").NumberFormat = "0.00"
Columns("I:I").NumberFormat = "#,##0"
Columns("a:k").AutoFit
Columns("e:e").NumberFormat = "dd-mmm-yy"

Range("a5").Select

End Sub

after running this code the columns c to J will be filled with formats etc.
you can modify to suit you
this is what I used to get the current data and daily run this sub after
trading is over. naturally daily I copy the downloaded data to a master
sheet to give historical data.

--
remove $$$ from email addresss to send email
===================


I symbols