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
===================
"ebgehringer" wrote in message
...
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" wrote:
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
--
remove $$$ from email addresss to send email
=========
"ebgehringer" wrote in message
...
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.
|