donaldb36 has given a very good macro for this
ref : http://tech.groups.yahoo.com/group/xltraders/files/
in the file list go down and use the file given by donaldb36
At present I am not able to think of any formula . There is an elegant solution by donalsb36 see reference above
This is based on yahoo.finance
I am sending you the sheet below. the important cells are
C2 which contains the parameters required. see reference for this in C1 of the sheet and explanation in c3.
the data starts ONLY from A7 (macro is is designed like this.can be modified if you want)
maximum of about 70 stocks can be entered in A7 down.
the latest price will be in D7 down.
remember the stock codes are yahoo codes for e.g for google the yahoo code is goog. i HAVE NOT DONE ANY MODIFICTION TO HIS CODE
If this is useful thank donalb36 and not me.
the macro is (as given by donaldb36). If you want you can have button for this macro on the sheet. That is what donald has done.
you have to fill up the codes in column A and run the macro
if you are familiar with macros you can go the main reference I have given above and download donald's file (This will be the best thing)
Code:
Sub GetData()
'this is a code taken from the files of the forum "xltraders"
'http://tech.groups.yahoo.com/group/xltraders/files/
'file by donaldb36
Dim yahoourl As String
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set DataSheet = ActiveSheet
Range("C7").CurrentRegion.ClearContents
i = 7
yahoourl = "http://quote.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
yahoourl = yahoourl + "+" + Cells(i, 1)
i = i + 1
Wend
yahoourl = yahoourl + "&f=" + Range("C2")
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Columns("C:C").ColumnWidth = 28#
Cells(2, 3).Select
End Sub
******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="6" style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book2</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2002 XP : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td colspan="6" style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F</td></tr></tbody></table></td></tr></tbody></table></center>
<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="6" bgcolor="#0c266b">
</td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="6" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">)ile (
E)dit (
V)iew (
I)nsert (
O)ptions (
T)ools (
D)ata (
W)indow (
H)elp
(A)bout</td><td align="right" valign="center"><form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" value="Copy Formula" name="btCb942116" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="6" bgcolor="white"><table border="0"><tbody><tr><form name="formFb202339"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="" selected="selected">A1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">
=</td><td align="left" bgcolor="white"><input size="80" name="txbFb150492"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>E</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">C2 taken from http://www.etraderzone.com/free-scripts/50-yahoo-stock-quotes.html</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(204, 255, 255); text-align: left;">nl1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">n stands for name and l1 for CMP</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 255); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>6</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">YAHOO CODE</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">name</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">CMP</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>7</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">msft</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">Microsoft Corpora</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">29.61</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>8</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">goog</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 255); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">Google Inc.</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">566.4</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>9</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">ibm</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">International Bus</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">128.38</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="6"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">
Sheet1</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>