VBA: Getting Data From Bloomberg

This is what we did last time:

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

On GetData.xlsm, Sheet1.5, This is the data from sir. The exercise involves creating a macro that shows the recent price now, importing from bloomberg. And we want the refreshed data to be put in column B. Sheet1 would be useful because that is where we are going to find our data

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