Loop Through Array Copy/Paste Value into VBA Code

[ryguy7272]

Many people that visit this Discussion Group have provided me with help in
the past; I am hoping someone can help me out once more.

I have been battling this problem for quite some time now. I have a long
list of stock symbols (106 total, all in ColumnD). I am wondering if there
is a way to loop through the entire array and copy/paste each value into a
certain section of a VBA macro that I recorded to get daily stock prices from
the Yahoo finance web site.

Again, am trying to get the code to start in Cell D2 (for instance)
recognize that the value is KFT (for Kraft Foods Inc.), copy/paste this value
into the snippet of code into the "KFT" spot below:
"URL;http://finance.yahoo.com/q/hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d" _


Below is my full macro:
Sub HistoricalData()

'KFT - Historical Data
Sheets("KFT").Select
Cells.Select
Range("A1:IJ50000").ClearContents


With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://finance.yahoo.com/q/hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d" _
, Destination:=Range("A1"))
.Name = "hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"
.FieldNames = True
.RowNumbers = False
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 11.14

Cells.Select
With Selection
.MergeCells = False
End With


Range("A1").Select
Range("B,F:G").Select
Range("F1").Activate
Selection.Delete shift:=xlToLeft
Range("A1").Select

End Sub


I’ve seen Excel do some pretty AMAZING things over the years. Not sure if
Excel can handle this task or not, but it would be awesome if it could!!

Regards
RyGuy---

 

[Vergel Adriano]

Hi,

Assuming the sheet name is the same as the stock symbol, I would suggest
making HistoricalData sub accept a string parameter. i.e,

Sub HistoricalData(strSymbol as string)


Then, use the parameter in your code as:

Sheets(strSymbol).Select

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/hp?s=" & strSymbol &
"&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"


Next you can loop through A2 through A100 for example as

Dim i as integer
For i=2 to 100
HistoricalData Range("A" & i).text
Next i


Would that work? Post back if not..

 

[Dave Peterson]

Without looking at your code, here's a macro that I saved:

Option Explicit
Sub testme()

Dim ce As Range
Dim LastRow As Long
Dim myGroup As Long
Dim myStep As Long
Dim iRow As Long
Dim myString As String
Dim myStringEnd As String
Dim qURL As String

Application.ScreenUpdating = False

myStep = 200

With ActiveSheet
.AutoFilterMode = False
.Range("B2:IV" & .Rows.Count).Clear

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = 2 To LastRow Step myStep
If iRow > LastRow Then
Exit For
End If

myString = ""
For Each ce In .Cells(iRow, "A").Resize(myStep)
If ce.Row > LastRow Then
Exit For
End If
myString = myString & ce.Value & "+"
Next
myStringEnd = myString + "&f=sl1d1t1c1ohgv&e=.csv"
'qurl = http://finance.yahoo.com/d/quotes.csv?s=T+CSCO+FE+^&d=e"
qURL = "http://finance.yahoo.com/d/quotes.csv?s=" & myStringEnd
With .QueryTables.Add(Connection:="URL;" & qURL, _
Destination:=.Cells(iRow, "B"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Next iRow

On Error Resume Next
.Columns("B:iv").SpecialCells(xlCellTypeBlanks).Delete _
Shift:=xlToLeft
On Error GoTo 0

Application.DisplayAlerts = False
.Columns("B:B").TextToColumns Destination:=.Range("B1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 3), _
Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1))
Application.DisplayAlerts = True

With .Range("b1").Resize(1, 9)
.Value = Array("Name", "Close", "Date", "Time", "Change", _
"Open", "High", "Low", "Volume")
.Font.Bold = True
End With

.Range("d1:e1,i1").EntireColumn.HorizontalAlignment = xlCenter
.Range("c1,F1:h1").EntireColumn.NumberFormat = "#,#0.00"

With .Range("j1").EntireColumn
.NumberFormat = "#,##0"
.HorizontalAlignment = xlRight
End With

.UsedRange.Replace what:="N/A", replacement:="", lookat:=xlWhole
.UsedRange.AutoFilter
.UsedRange.Columns.AutoFit

End With

Application.ScreenUpdating = True
End Sub

It works against the activesheet and the symbols are in A2:Axxx.

Even if you don't like this, maybe you could pick out some pieces to save.

 

[Dave Peterson]

ps. It was based on code that others supplied in this thread:

http://groups.google.co.uk/group/mi...464fb843011?lnk=st&q=&rnum=1#52e0a464fb843011

or
http://snipurl.com/1d05u

 

[merjet]

Vergel Adriano is on the right track, except you need 2 arguments, the
2nd for the .Name property. I simply put a loop in your code, like the
following, and it worked.

Dim str1 As String
Dim str2 As String
Dim c As Range

For Each c In Sheets("Sheet1").Range("D13")
Sheets(c.Value).Select
Cells.Select
Range("A1:IJ50000").ClearContents
str1 = "URL;http://finance.yahoo.com/q/hp?s=" & _
c.Value & "&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"
str2 = "hp?s=" & c.Value & "a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"
With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))
.Name = str2
'
'
'include rest of your code here
'
'
Next iCt

Hth,
Merjet

 

[ryguy7272]

Vergel, thanks for taking the time to look at this. I kind of follow the
logic, but not completely. Could you possibly restructure the code for me so
your segment is compiled with my segment (i.e., make it so that everything is
structured in the order that the Macro will execute the commands)?


Also, what is the "strSymbol as string" in Sub HistoricalData(strSymbol as
string)? What does this do? When I copy and paste this into a Module and
try to link it to a Control Button, I can't find the module. If I delete the
"strSymbol as string" so the sub only reads "Sub HistoricalData()" then the
code can be linked to a Control Button.

Thanks!!
RyGuy

 

[ryguy7272]

This is UNREAL!!!! I got it working, thanks to merjet's suggestions!!
I am wondering if I can do one more thing now...I would like to take the
values in Column D (this column is where all my stock symbols reside) and add
a worksheet with a name that corresponds to each value in Column D. For
instance, KFT (Kraft Foods Inc.) is in Cell D2; I would like to tell Excel to
take this value in D2, create a new worksheet named KFT, and then populate
the worksheet with the historical stock prices for KFT (I have this part
working now thanks to merjet's code). Basically, how can I create a loop to
check the value in D2, add a worksheet, assign the value D2 to the name of
the worksheet, then populate that worksheet with historical stock prices,
then loop down to D3 and repeat? I may actually have a solution; I have to
dig deep in my bag of tricks though... If I get it working I will post the
solution, in its entirety, on this Discussion Group for the benefit of
others. If someone else finds a solution before me, please post for my
benefit.

Thanks again!!
RyGuy----

 

[merjet]

Insert this after the "For Each" line:

bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws
If bFound = False Then
Worksheets.Add.Name = c.Value
End If

Add to the Dim list:

Dim bFound As Boolean
Dim ws As Worksheet


Merjet

 

[ryguy7272]

I’ve been busy the past few days trying to figure out one more thing...which
has to do with something called ‘WebTables’ I believe. Between recording a
macro and creating a couple of loops (thanks to Merjet from this Discussion
Group), I came up with a way of querying the web site finance.yahoo.com. I
set up to loop to import historical stock prices into Excel as well as
certain information on the ‘Summary’ screen. The only problem is that the
information on the Summary screen is not consistent. Deep within my code, I
tell Excel to look for the following:

..WebTables = "48,53"

Usually this yields the desired results, but sometimes, I get the wrong
information because the wrong WebTables are being queried for certain stocks.
When I get these occasional errors, I can turn on the macro recorder and
identify the WebTables that I really need (and they could be something like
..WebTables = "46,51" or .WebTables = "47,52") but this defeats the purpose of
using a looping macro. Does anyone know why this occurs? More importantly,
does anyone know who to resolve this issue? The Yahoo finance people are
getting the right data into the right statistical categories, but they are
not reporting these in the same “WebTablesâ€.

I can post the code, if necessary, but I was trying to get everything
straightened out before I post it..

Regards,
RyGuy—

 

[ryguy7272]

My intention is not to duplicate work, or confuse people in any way, so
rather than post the entire solution here, I’ll just post the link to the
solution (I had a few different threads going in the past few weeks):

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Look for title “Import from finance.yahoo.comâ€
Look in the top most post.

Merjet and Jim Thomlinson offered a tremendous amount of help (I couldn’t
have done it without them). Hopefully the final solution helps others too!!!

 

[William]

You can find your answer in the website below, it show you how to download
historical / daily stock quotes from a list of stock.

There are some ready-to-use script which you can download, view and modify
to suit to your own need.

http://excelstock.googlepages.com

cheers,