HELP! VBA Rookie, average excel user Help me save some time

mjansen · Jan 23, 2004

Maybe someone can help me. I ahve a list of 156 companies and their stock tickers, and what I would like to do is have a script that will take the stickticker symbols and use those to query yahoo finance for it's data tables, specifically those found on the key statics page, create a new sheet labeled with the ticker (so a sheet for every ticker) and then paste the table data into the newly created sheet.

Any suggestions? WHere should I start.... or is this impossible?

Thanks for all your hel pso far,

Marc

NateO · Jan 23, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

Hello Marc, I had a list of valid tickers in Column A and ran the following with some success:

Sub YippieYahoo()
Dim ie As Object, cTables As Object, Table As Object
Dim MyArr As Variant, cl As Range, y As Worksheet, Tick As String
Set ie = CreateObject("InternetExplorer.Application")
With Application
 .ScreenUpdating = False
 .DisplayAlerts = False
End With
With ie
 For Each cl In Range([a1], [a65536].End(3))
 Tick = cl.Value
 .Navigate "http://finance.yahoo.com/q?s=" & Tick
 Do While .Busy And .ReadyState <> 4
 DoEvents
 Loop
 With .document
 Set cTables = .all.tags("table")
 Set y = ThisWorkbook.Worksheets.Add(after:=Sheets(ThisWorkbook.Sheets.Count))
 y.Name = Tick: y.[a1] = Tick
 For Each Table In cTables
 If Left$(Table.Rows(0).Cells(0).innerText, 10) = "Last Trade" And _
 Len(Table.Rows(0).Cells(0).innerText) > 11 Then
 MyArr = Evaluate("{""" & _
 Application.Substitute(Table.Rows(0).Cells(0).innerText, _
 vbNewLine, """;""") & """}")
 y.[a2].Resize(UBound(MyArr)) = MyArr
 y.[a2:a65536].TextToColumns Destination:=y.[a2], DataType:=xlDelimited, _
 Other:=True, OtherChar:=":", _
 FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
 y.[b3] = y.[b3] & y.[c3]
 End If
 If Left$(Table.Rows(0).Cells(0).innerText, 11) = "Day's Range" Then
 MyArr = Evaluate("{""" & _
 Application.Substitute(Table.Rows(0).Cells(0).innerText, _
 vbNewLine, """;""") & """}")
 y.[c2].Resize(UBound(MyArr)) = MyArr
 y.[c2:c65536].TextToColumns Destination:=y.[c2], DataType:=xlDelimited, _
 Other:=True, OtherChar:=":", FieldInfo:=Array(1, 1)
 Exit For
 End If
 Next
 End With
 Set y = Nothing: Set cTables = Nothing
 Next
 .Quit
End With
Set ie = Nothing
With Application
 .ScreenUpdating = True
 .DisplayAlerts = True
End With
End Sub

At first I was simply thinking of grabbing CTables(17), but with some limited testing, it seemed that the number of tables early in the document was changing with the ticker, so it would work for some issues and not for others, in any case, hence the table loop.

Again, not very much testing, and virtually no error handling, use at your own risk. And I wouldn't run it for 100+ at one time, it will take a very long time...

Hope this helps.

mjansen · Jan 23, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

Thanks for the help Nate,

This gives me something to start with. I'm also looking to grab information from the Key Statistics page so for instance for Microsoft the url is http://finance.yahoo.com/q/ks?s=msft . I'll play with what you have so far, although I really don't know what I'm doing, but's there nothing betetr to learn through doing.

Thanks again, and I'm up for any other help out there,

Marc

ray:

new2waix · Jun 9, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

NateO,

The code you posted works an absolute treat!

I am hoping I can borrow your expertise on a variation to the script.

I have an Excel file with the following sheets:

Sheet 1 - Codes
Column A contains a list of stock codes
Column B contains prices accessed from Sheet 2

Sheet 2 - Data
Contains table manually pasted from: http://au.finance.yahoo.com/q?m=a&s=aln+amp+asx&d=v1

I would love to automate the data importing into Excel (sheet 2).

Any assistance in getting this up and running will be greatly appreciated. I have had a go at modifying your code but am a little stuck on what it all means.

Thanks again!

Von Pookie · Jun 9, 2004

new2waix made his own thread for this question here: http://www.mrexcel.com/board2/viewtopic.php?t=91919

NateO · Jun 9, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

Hello,

Which table? Are you looping like the original request? This page looks like it concatenates tickers.

This may not be the best block of code to get your feet wet with... Have you tried a web query via MS Query?

Also, duplicate threads convolute the discussion, I will delete the 2nd thread.

new2waix · Jun 9, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

I was thinking it could be done like follows:

1) Sheet 1, Column A contains list of codes

2) Macro runs

Macro puts Column A into the URL

http://au.finance.yahoo.com/q?m=a&s=A1:A1000&d=v1

3) URL runs and spits out the below table

http://au.finance.yahoo.com/q?m=a&s=amp+aln+tls&d=v1

Symbol Name Last Trade Change Volume More Info
AMP.AX AMP 11:29am 5.840 +0.010 +0.17% 1,880,132 Chart, News, Profile, Research
ALN.AX ALINTA 11:29am 6.170 +0.040 +0.65% 488,227 Chart, News, Profile, Research
TLS.AX TELSTRA CORP FPO 11:30am 4.600 -0.040 -0.86% 7,791,060 Chart, News, Profile, Research

4) Macro then copies table into Sheet 2

Some how copy from "Symbol" to "Add to my Portfolio". Column D will always hold the last price data.

5) Then Sheet 1 Column B

Column B uses a Vlookup to match Column A with the price from sheet 2

I guess my whole objective if to try and import the table of data neatly into Excel. I have tried a web query, but the cells tend to move around a lot and pull in a lot of garbage as well.

Hoping you can help.

new2waix · Jun 10, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

I have made some developments in my project but have run into some problems.

I have created a web query called "Yahoo Finance.iqy":

Rich (BB code):

WEB
1
http://au.finance.yahoo.com/q?m=a&d=v1&s=["QUOTE","Enter Codes"]

Selection=10
Formatting=False
PreFormattedTextToColumns=False
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False

I run the query and the stock price table is imported into a new sheet. I just have some questions in getting everything to run smoothly:

1)

How can ensure that the query always pulls in the right table. "Selection=10" assumes that the 10th table is the table that contains quotes. Is there a way to search for the right table (ie the table with the prices)?

2)

How can I remove all fortmatting from the imported data?

3)

Is there a website that lists the variables available for web queries.

Selection=
Formatting=
PreFormattedTextToColumns=
ConsecutiveDelimitersAsOne=
SingleBlockTextImport=

4)

I am using a VLOOKUP function to pull the price from the imported data into a new sheet but Excel spits out the data in the cell above the 'matched cell':

Cell A2 contains ALN
Cell B2 contains =VLOOKUP(A2,IMPORTED_DATA!A1:K14,4,1)

Returns the price from the cell above?

If any of the above needs clarification feel free to let me know. Not to sure what I am doing

NateO · Jun 10, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

How can ensure that the query always pulls in the right table. "Selection=10" assumes that the 10th table is the table that contains quotes. Is there a way to search for the right table (ie the table with the prices)?

Yahoo seems to be a moving target. If this is the case, I think a loop with OLE automation is requried.

I don't see a table name in the source, does 10 not always work?

Also, when you say a list of tickers do you mean like:

Book3

A

B

C

D

1

ALN.AX

2

AMP.AX

3

ASX.AX

Sheet2

Where the tickers are concatenated for the resulting table?

Also, have you seen these:

http://www.mrexcel.com/board2/viewtopic.php?t=56809&start=2

new2waix · Jun 10, 2004

Re: HELP! VBA Rookie, average excel user Help me save some

>>> Yahoo seems to be a moving target. If this is the case, I think a loop
>>> with OLE automation is requried.
>>>
>>> I don't see a table name in the source, does 10 not always work?

Sometimes 10 doesn't work. How can I name the table? How do I create this loop?

>>> Also, when you say a list of tickers do you mean like....

Yep, exactly.

>>> Where the tickers are concatenated for the resulting table?

Each value was in a separate cell but same values fell into the same columns.

http://au.finance.yahoo.com/q?m=a&s=aln+amp&d=v1

>>> Also, have you seen these:
>>> http://www.mrexcel.com/board2/viewtopic.php?t=56809&start=2

I had a look, but the codes seem to pull the whole page into Excel. I am trying to grab the table only.

HELP! VBA Rookie, average excel user Help me save some time

mjansen

New Member

Excel Facts

NateO

Legend

mjansen

New Member

new2waix

Board Regular

Von Pookie

MrExcel MVP

NateO

Legend

new2waix

Board Regular

new2waix

Board Regular

NateO

Legend

new2waix

Board Regular

Similar threads

Forum statistics

Share this page

HELP! VBA Rookie, average excel user Help me save some time

New Member

Excel Facts

Legend

New Member

Board Regular

MrExcel MVP

Legend

Board Regular

Board Regular

Legend

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock