multiple repetitive tasks.

[Stclements]

Each morning I do a series of tasks on an excel spreadsheet and was hoping that someone may know some excel tricks that may speed the process up. The tasks are as follows:

I type in a stock epic (i.e TSCO) Tesco in to the yahoo stock quotes on the yahoo financial page.
Once Displayed, to the left of the quote I click on Historical Prices which brings up a table and with the last working day date on it and the opportunity for me to manipulate the start date
I then type in the start date which is 30 working days before the already given last working day date, i.e yesterday, on a normal working week.
I then press enter and the dates come up in table format.
At the bottom of the page is a link that allows me to export the data to Excel
This opens up in another excel sheet
I then copy the date and paste it into my main excel sheet.
Once doing this I then have to close the exported data sheet down and begin the process all over again.
The data I import into my spreadsheets allows me to define averages and standard deviation of the given stock over the given period (30 working days in this instance).
I repeat this process a minimum of 30 times each morning, so if, with the information of my processes I have given someone feels that there are some nifty excel tricks that can help reduce the time i spend on these procedures I would be very grateful.

 

[b.downey]

As I understand it, you have 30+ stock symbols and you need to get the last 30 days of stats for each from Yahoo. The Data for each stock needs to be stored in on different "tabs" in the same workbook.

Is this accurate?

 

[Stclements]

On any one day I have at least 30 stocks whose price's

Open, High, Low,Close,Volume,Adjusted close. I get for the last 30 trading days. I get these from Yahoo.com/finance.

Then I have to import that data from their spreadsheet into my spreadsheet and complete the process as per my original message.

Any help you might be able to provide in ways as to reduce the monotony of the task would be greatly appreciated.

As I understand it, you have 30+ stock symbols and you need to get the last 30 days of stats for each from Yahoo. The Data for each stock needs to be stored in on different "tabs" in the same workbook.

Is this accurate?

 

[b.downey]

Thanks for the clarification. I have designed some code that will get you started. The Code requires that you have two Worksheets (Tabs). One called "Master" and the other called results. The "Master" Sheet will have the input paramenters and the "Results" tab will have the daily stock states you require.

The Master Sheete needs the Following Information

Cell B1: Stock Symbol
Cell B2: First Date
Cell B3: End Date.

Once this information is populated, you can run the Macro "GetYahooQuotes". A dialog box will appear when the prococes is complete. The result will be in the "Results" spread sheet.

Here is the Code. Just place it in a VB Module:

Option Explicit
Dim Wb As Workbook
Sub GetYahooQuotes()
    Dim WsMaster As Worksheet
    Dim WsResults As Worksheet
    
    Set Wb = ThisWorkbook
    
    Set WsMaster = Wb.Sheets("Master")
    Set WsResults = Wb.Sheets("Results")
    
    Call CreateTableQuery(WsResults, _
        WsMaster.Cells(1, "b"), _
        CDate(WsMaster.Cells(2, "b")), _
        CDate(WsMaster.Cells(3, "b")))
        
    WsResults.Activate
    MsgBox "Complete", vbInformation
End Sub
Function CreateTableQuery(WsSheet As Worksheet, ByVal Symbol, ByVal StartDate As Date, ByVal EndDate As Date)
    
    Dim strConStr  As String
    
    Call DeleteAllTableQuerys(WsSheet)
    
    '*****   Set up Parameters for the Query
    'http://finance.yahoo.com/q/hp?s=VZ&a=10&b=21&c=1983&d=05&e=21&f=2012&g=d
    
    strConStr = "[URL]http://finance.yahoo.com/q/hp[/URL]?"
    strConStr = strConStr & "s=" & Symbol
    
    strConStr = strConStr & "&a=" & Format(Month(StartDate), "00")
    strConStr = strConStr & "&b=" & Format(Day(StartDate), "00")
    strConStr = strConStr & "&c=" & Year(StartDate)
    
    strConStr = strConStr & "&d=" & Format(Month(EndDate), "00")
    strConStr = strConStr & "&e=" & Format(Day(EndDate), "00")
    strConStr = strConStr & "&f=" & Year(EndDate)
    strConStr = strConStr & "&g=d" 'd = Daily Quotes
    
    WsSheet.Cells.ClearContents
    
    With WsSheet.QueryTables.Add("URL;" & strConStr, WsSheet.Range("A1"))
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "15"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Function
 
Function DeleteAllTableQuerys(Ws As Worksheet)
    Dim I
    If Ws.QueryTables.Count = 0 Then
        Exit Function
    End If
    
    For I = Ws.QueryTables.Count To 1
        Ws.QueryTables(I).Delete
    Next I
End Function

Clearly this is just a start... It is possble to modify the code to eatract all of the 30+ stocks every time the macro is execute. It is also possible to calculate the dates automatically.