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

[mjansen]

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

 

[new2waix]

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

I found this on Google Groups.

http://groups.google.com.au/groups?...=#jZ7BlTrDHA.1928@TK2MSFTNGP12.phx.gbl&rnum=8

    Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
    Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
    Private Const msPE_RATIO As String = "P/E Ratio"
    Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

    Sub GetStockValues()
        Dim ie As Object
        Dim s As String
        Dim nStart As Integer
        Dim nEnd As Integer

        Set ie = CreateObject("InternetExplorer.Application")

        With ie
            .Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
             &
"etailedView=DetailedPrices&Language=en&QuoteSymbol_1=bce&x=18&y=7"
            Do Until Not .Busy And .ReadyState = 4
                DoEvents
            Loop

            s = ie.Document.body.innertext
            .Quit
        End With
        Set ie = Nothing

        '/ get rolling 52-wk high
        nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
        If nStart Then
            nStart = nStart + Len(msROLLING_52_HIGH)
            nEnd = InStr(nStart, s, vbCrLf)
            Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
             nEnd - nStart)
        End If

        '/ get rolling 52-wk low
        nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
        If nStart Then
            nStart = nStart + Len(msROLLING_52_LOW)
            nEnd = InStr(nStart, s, vbCrLf)
            Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
             nEnd - nStart)
        End If

        '/ get p/e ratio
        nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
        If nStart Then
            nStart = nStart + Len(msPE_RATIO)
            nEnd = InStr(nStart, s, vbCrLf)
            Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
             nEnd - nStart)
        End If

        '/ get dividend rate
        nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
        If nStart Then
            nStart = nStart + Len(msDIVIDEND_RATE)
            nEnd = InStr(nStart, s, vbCrLf)
            Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
             nEnd - nStart)
        End If
    End Sub

Looks promising...

 

[NateO]

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

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

It pulls in the data you want, yes-no?

Why not set up a dummy, hidden sheet, and pull what you want from it (just link the cells)? Then you'll have a straight-forward handle on formatting, etc...

 

[new2waix]

It does pull in the data. However, it would be great if the data came it without all the formatting and other stuff.

I am also having issues with VLOOKUP.

What is the best way to pull the price from the imported data into the new sheet?

I think I am having issues because the imported data may be a different format?

 

[ianccy]

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

http://chart.yahoo.com/d?a=5&b=01&c=02&d=12&e=31&f=02&g=d&s=amcc

Hi NateO,

can your code do the same for historical data, which line of your code should be replaced? I am not good in coding.

 

[new2waix]

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

Okay, I've found a better source of data.

http://www.float.com.au/download/20040611.csv

I want to have a sheet that lets me put in a date then I click a button and it imports the data file into an existing data sheet.

Sheet 'Update':

Insert Date of Data Required --> Cell A3 = Date
Update Button -->
When clicked downloads data from http://www.float.com.au/download/yyyymmdd.csv
Deletes existing data from sheet 'DATA' and inserts new data from cell A2.

Sheet 'DATA'

Row A --> Contains Headers
Row B onwards --> Contains price information

What's the best way to do this?

Thanks for all the help to date!