Printable View
hi
i want vba script to get stock quotes i have tried some codes but with zero success i am specifically looking to get with VBA Excel Yahoo Stock Quotes that will give me historical past records also current records also .please give me if any one have such script thank you.
try this code code which i found t it will help you to handle more stock
Quote:
Private Sub CommandButton1_Click()
Application.displayalerts=False
Dim Symb As String
Dim startdate As Date
Dim enddate As Date
Dim a, b, c, d, e, f As Integer
' in the line above only f is declared as an Integer - all the other variables are
' by default declared as Variants. The reason is that in VBA variables need to be
' declared one by one and cannot be declared as a group.
Dim g As String
'symb = Range("B1").Value
Sheets("Sheet1").Select
startdate = Range("B2").Value
enddate = Range("B3").Value
a = Month(startdate)
b = Day(startdate)
c = Year(startdate)
d = Month(enddate)
e = Day(enddate)
f = Year(enddate)
g = LCase(Left(Range("B4").Value, 1))
For i = 1 To 50
Sheets("Sheet1").Select
Symb = Range("a1").Offset(0, i).Value
'Historical Data
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=" & Symb & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=" & g _
, Destination:=Range("A6"))
.Name = "Quote: " & Symb
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "19"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
'This was false
Sheets("Sheet1").Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1)), TrailingMinusNumbers:=True
End With
'Call Module1.Display
'Sheets("Sheet1").Range("A6").Select.End(xlDown).Copy Sheets("Sheet2").[A2].Offset(0, i)
'Range(Selection, Selection.End(xlDown)).Select
Sheets("Sheet1").Range("A6:A7000").Copy Sheets("Sheet2").[A2].Offset(0, 0)
Sheets("Sheet1").Range("G6:G7000").Copy Sheets("Sheet2").[A2].Offset(0, i)
Sheets("Sheet1").Range("B1:BW1").Copy Sheets("Sheet2").Range("B1:BW1")
Next i
'this copies dates
Sheets("Sheet1").Range("A7:A7000").Copy Sheets("Sheet2").[A3]
Columns("A:A").ColumnWidth = 12.86
'this formats output cells
'Sheets("Sheet2").[A2] = "Date"
'Sheets("Sheet2").Range("A2:H2").Style = "Calculation"
'copies symbol tickers in the output sheet
Sheets("Sheet1").Range("B1:W1").Copy Sheets("Sheet2").Range("B1:W1")
Sheets("Sheet1").Range("A6").Select
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Selection.ClearContents
Selection.QueryTable.Delete
Sheets("Sheet1").Range("A6").Select
End Sub
Yahoo Finance has a link on each summary page that allows the user to download the stock information in CSV format. that direct access to stock information .
The format is download from below
get this data into Excel with manual function:
The addin can be downloaded here. It needs MSXML 4.0Quote:
=YF_Price(STOCK SYMBOL, PRICE SIDE)
1. =YF_Price("VOD.L", "BID") returns the bid price for Vodafone
2. =YF_Price("VOD.L", "ASK") returns the ask price
3. =YF_Price("VOD.L", "LAST") returns the last price
4. =YF_Price("VOD.L", "CLOSE") returns the close price
5. =YF_Price("VOD.L") defaults to the last price
I am using the add-in "Stock Quotes from MSN Money" in a spreadsheet to retrieve stock data from the internet. I works fine.This add-in for Excel 2003 and Excel 2002 allows you to get dynamic stock quotes from the MSN Money Web site. The tools and features found in Excel are particularly well suited to analyzing financial data such as stocks. This add-in allows you to easily gather and study the stocks of interest to you, refresh your quotes when you want, and readily change or modify the quotes gathered.if you want to try just check here
I also play stock exchange.......