#### Stclements

##### Board Regular
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?

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?

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:

Code:
``````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

.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.

Replies
0
Views
298
Replies
1
Views
431
Replies
12
Views
760
Replies
2
Views
269
Replies
1
Views
248

1,211,803
Messages
6,104,073
Members
447,892
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back