Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Excel Quotes
I know this has been asked before, but not in this variation. Ideally, a solution wouldn't require VBA knowledge. What I would like to do is to use a web query to get historical to-present stock quotes for multiple ticker symbols. I am open to using any free source, be it yahoo finance, MSN, google, or some other. I really just need to be able to pick up the raw data. Thanks in advance for your help! -- Aashish.Shukla ------------------------------------------------------------------------ Aashish.Shukla's Profile: http://www.excelforum.com/member.php...o&userid=36094 View this thread: http://www.excelforum.com/showthread...hreadid=558741 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Excel Quotes
I have prepared for my use a macro which not only donwloads yahoo(indias
historical data and also chart it. the macro consist of main macro "daily chart" in which I call another macro at the end "charting" .I am sending the 2 macros below. They may be clumsy but works there are only two problems. in.finance.yahoo gives only about three month sdata in the first page and rest in the next pages. My macro(s) download only the firt page. another problem is that in.finance.yahoo takes 24 hours to update. so today (6 july monring) you will get only upto 4 july. the macro dorps down an input box where you type the yahoo symbol of the scrip.e.g. cipla.ns. unfortunately yahoo in finance does not give historical data for BSE symbols. perhaps the macros can be tweked. if you are interested I can send you my whole workbook as attachment if you send me a personal email ============================= Dim symbol Sub dailychart() Application.DisplayAlerts = False Dim enddate, endmonth enddate = Day(Date) endmonth = Month(Date) symbol = Application.InputBox("type yahoo symbol of the scrip as vsnl.ns") Application.DisplayAlerts = False On Error Resume Next Sheets("daily chart").Delete Worksheets("sheet1").Activate Cells.Clear With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://in.finance.yahoo.com/q/hp?s=" & symbol & "&a=00&b=1&c=2006&d=" & endmonth & "&e=" & enddate & "&f=2006&g=d" _ , Destination:=Range("A1")) .Name = "hp?s=" & symbol & "&a=00&b=1&c=2006&d=" & endmonth & "&e=" & enddate & "&f=2006&g=w_3" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "27" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Range("a1").End(xlDown).Clear Range(Range("a1"), Range("a1").End(xlDown).End(xlToRight)).Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess Range("k1") = symbol Range("K1").Font.Bold = True '++++++++++++++ Range("a1").Select Dim cfind As Range With Range(Range("G1"), Cells(Rows.Count, "g").End(xlUp)) Set cfind = .Find(what:="") If Not cfind Is Nothing Then ActiveCell.EntireRow.Delete End With Range("A1").Select charting Application.DisplayAlerts = True End Sub ================================ Sub charting() Dim low1, low2, high1, high2, vol1, vol2, date1, date2 As Range Dim low, high, vol, ddate As Range Worksheets("sheet1").Activate Set low1 = Cells.Find("Low").Offset(1, 0) Set low2 = low1.End(xlDown) Set high1 = Cells.Find("High").Offset(1, 0) Set high2 = high1.End(xlDown) Set vol1 = Cells.Find("volume").Offset(1, 0) Set vol2 = vol1.End(xlDown) Set date1 = Cells.Find("date").Offset(1, 0) Set date2 = date1.End(xlDown) Set llow = Range(low1, low2) Set hhigh = Range(high1, high2) Set vol = Range(vol1, vol2) Set ddate = Range(date1, date2) ' Range(Range("B1"), Range("B1").Offset(0, 3)).Select ' Range(Selection, Selection.End(xlDown)).Select 'On Error Resume Next 'Sheets("daily chart").Delete Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Name = "daily chart" ActiveChart.SeriesCollection(1).Values = llow ActiveChart.SeriesCollection(1).Name = "low" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(2).Values = hhigh ActiveChart.SeriesCollection(2).Name = "high" ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(3).Values = vol ActiveChart.SeriesCollection(3).Name = "volume" ActiveChart.SeriesCollection(1).XValues = _ ddate ActiveChart.SeriesCollection(2).XValues = _ ddate ActiveChart.SeriesCollection(3).XValues = _ ddate ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).AxisGroup = 2 With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "DAILY DATA FOR " & Worksheets("sheet1").Range("K1").Value .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PRICE" End With ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = WorksheetFunction.RoundDown((WorksheetFunction.Min (llow)), -1) .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With Application.DisplayAlerts = True End Sub ======================================= "Aashish.Shukla" <Aashish.Shukla.2aielb_1152165002.1856@excelforu m-nospam.com wrote in message news:Aashish.Shukla.2aielb_1152165002.1856@excelfo rum-nospam.com... I know this has been asked before, but not in this variation. Ideally, a solution wouldn't require VBA knowledge. What I would like to do is to use a web query to get historical to-present stock quotes for multiple ticker symbols. I am open to using any free source, be it yahoo finance, MSN, google, or some other. I really just need to be able to pick up the raw data. Thanks in advance for your help! -- Aashish.Shukla ------------------------------------------------------------------------ Aashish.Shukla's Profile: http://www.excelforum.com/member.php...o&userid=36094 View this thread: http://www.excelforum.com/showthread...hreadid=558741 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
don't insert quotes in excel save as tab-delim txt | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
How do I get real time stock quotes to my excel worksheet? | Excel Discussion (Misc queries) |