Thanks! I'll give it a try.
Posts by wadevcamp
-
-
I've found a way to pull in Yahoo daily close data for a particular stock using vba code, but Yahoo seems to allow me to pull only 100 days (100 rows). If anyone has a way to get more of the historical data using vba code, I'd appreciate knowing how to do this.
The code that pulls 100 rows is below...
Code
Display MoreActiveWorkbook.Queries.Add Name:="Table 2", Formula:= _ "let" & Chr(13) & "" & Chr(10) & _ " Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/MSFT/history/""))," & Chr(13) & _ "" & Chr(10) & " Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & _ " #""Changed Type"" = Table.TransformColumnTypes(Data2,{{""Date"", type date}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Close*"", type text}, {""Adj Close**"", type text}, {""Volume"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 2"";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [Table 2]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table_2" .Refresh BackgroundQuery:=False End Sub -
-
Apologies again. I cannot recall the other forum.
(When one does not get any responses on a forum, it seems to make sense to post it in another forum. Now that I know I need to inform each forum when this is done, I will.)
-
Apologies.
The link is http://www.vbaexpress.com/foru…ype-1&p=397487#post397487
And Rory's solution works -- thank you Rory. Too bad this issue isn't more widely known.
-
In both attempts to assign 'i' the value in the code below, a type mismatch error occurs. If I change the match type to '0', it works as expected. If I resort the range wrng2 to be ascending values and use a match type of either 0 or 1, it works as expected. Only when using a match type of -1 with a descending range of values does the error occur. Help! (wrng2 is depicted in the attachment below)
CodeDim i As Integer Dim d As Date d = #8/4/2019# i = Application.Match(d, Range("wrng2").Value, -1) d = Range("wrng2").Cells(7, 1).Value i = Application.Match(d, Range("wrng2").Value, -1)