- Open bloomberg site. This is where we are going to get out data from.
- On Excel, Go to Data -> Get External Data -> From Web
- A New Web Query box will appear. It is like a browser so we input in the url of bloomberg site we want to copy
- Make sure that the Show Icons button is checked. It is beside the Refresh button, then click Import. A text format of the site will be imported in an Excel Sheet.
Sheet2 is where I put my answers. Sheet3 contain all excess codes. It can be written as one Sub, but the challenge of this exercise is to create multiple sub or functions, instead of just creating loops.
How the code works
When the refresh button is pressed, the code gets data from bloomberg and imported it into a new sheet I called ws. It also assumes that the index name is on the first row and its values are in the second row when imported. Output is on Sheet2 and the ws worksheet will be deleted.
- Sub Calling(ws) - imports data from bloomberg
- Sub Refresh() - works like a Sub test(), it create a new worksheet, calls Calling(ws) that imports data from bloomberg, find the data needed and put it on the second column.
- Function GetData(inFind, ws)- looks for data in ws and get the appropriate value next to it
New to the Code
- I actually learned how to import data from bloomberg! Sub Calling contains such code
- Doesnt show alert box - Application.DisplayAlerts = False
- Delete the worksheet ws - ws.Delete
- Show alerts box - Application.DisplayAlerts = True
- To stop updating the screen - Application.ScreenUpdating = False
- Gets the index of the last row - Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
- Gets the index of the last column - Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
The Code
Sub Calling(ws)
'
' Getting data from bloomberg,
With ws.QueryTables.Add(Connection:= _
"URL;http://www.bloomberg.com/markets/stocks/world-indexes/americas", _
Destination:=ws.Range("$A$1"))
.Name = "americas"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Sub Refresh()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets.Add
Call Calling(ws)
Sheets("Sheet2").Range("B1") = Evaluate("Now()")
For x = 3 To Sheets("Sheet2").Range("A1").End(xlDown).Row
inFind = Sheets("Sheet2").Range("A" & x).Value
Sheets("Sheet2").Range("B" & x).Value = GetData(inFind, ws)
Next x
Sheets("Sheet2").Activate
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub
Function GetData(inFind, ws)
'This assumes that the first row has the Index Names and it second row contains the values.
Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
For i = 1 To Lastrow
IndexName = ws.Range("A" & i)
If IndexName = inFind Then
GetData = ws.Range("B" & i)
End If
Next
End Function
No comments:
Post a Comment