Using excel with Bloomberg BLP functions

Bloomberg provides a DDE interface you can use to do the work. Using these API calls you can make synchronous calls to the service and have complete control over the timing of subsequent actions. I can't help much other than to give you some sample code. You will need to dig up the documentation. Here is the sample code:

   ' Establish DDE connection
   #If BloombergInstalled Then
      ChannelNumber = DDEInitiate("Blp", "H")
   #End If
   
   ' TickerIndex is used to count tickers
   TickerIndex = 1
   ' Determine number of tickers for the status
   Count = WorksheetFunction.CountA(wksTickers.Ticker)
   
   FieldCodesString.Delimiters = ","
   
   ReDim CompleteResults(1 To 100, 1 To wksResults.FieldCodes.Cells.Count + 1)
   
   ' For each ticker...
   For Each TickerCell In wksTickers.Ticker
   
      PeriodCount = 0
      For FieldIndex = 1 To wksResults.FieldCodes.Cells.Count + 1
         For Period = 1 To UBound(CompleteResults, 1)
            CompleteResults(Period, FieldIndex) = "#N/A Not Retr"
         Next
      Next
      ErrorOccurred = False
   
      ' Stop if a blank ticker (end of list)
      If Len(TickerCell.Value) = 0 Then Exit For
     
      ' Display the status
      Environment.Status = "Procesing ticker " & TickerIndex & " out of " & Count
     
      ' Set up the DDE call command line parts
      CommandLine1 = TickerCell.Value & " Equity, ["
      CommandLine2 = "], PT=4Y END=" & Format(EndDate.Value, "YYYYMMDD") & " PD=" & Periodicity
     
      ' Build results
      FieldCodeIndex = 1
      Do
      'For FieldCodeIndex = 1 To wksResults.FieldCodes.Cells.Count
         ' Execute the DDE command
         FirstFieldIndex = FieldCodeIndex
         FieldCodesString.Value = ""
         #If BloombergInstalled Then
            Do
               If Len(FieldCodesString.Value) + Len(CommandLine1) + Len(CommandLine2) + Len(wksResults.FieldCodes(FieldCodeIndex).Value) > 256 Then
                  Exit Do
               Else
                  FieldCodesString.Append wksResults.FieldCodes(FieldCodeIndex).Value
               End If
               FieldCodeIndex = FieldCodeIndex + 1
            Loop Until FieldCodeIndex > wksResults.FieldCodes.Cells.Count
            On Error Resume Next
            Results = DDERequest(ChannelNumber, CommandLine1 & FieldCodesString.Value & CommandLine2)
            If Not Err.Number = 0 Then ' Or Left(Results, 5) = "Error" Then
               ErrorOccurred = True
               Exit For
            End If
         #Else
            ReDim Results(2, 2)
            Results(1, 1) = 123
            Results(1, 2) = FieldCodeIndex + 100
            Results(2, 1) = 456
            Results(2, 2) = FieldCodeIndex + 200
         #End If
         LastFieldIndex = FieldCodeIndex - 1
         PeriodCount = Max(PeriodCount, UBound(Results, 1))
         ' Add results to complete results
         For Period = 1 To UBound(Results, 1)
            For FieldIndex = FirstFieldIndex To LastFieldIndex
               If FieldIndex = 1 Then
                  CompleteResults(Period, 1) = Results(Period, 1)
               End If
               CompleteResults(Period, FieldIndex + 1) = Results(Period, FieldIndex - FirstFieldIndex + 2)
            Next
         Next
      'Next
      Loop Until FieldCodeIndex > wksResults.FieldCodes.Cells.Count
     
      If ErrorOccurred Then
         wksTickers.Error(TickerIndex).Value = "Error"
      Else
         ' Define destination ranges
         Set ResultTickerPasteArea = wksResults.TickerPasteArea.Resize(PeriodCount).Offset(WorksheetFunction.CountA(wksResults.Ticker))
         Set ResultValuesPasteArea = wksResults.ValuesPasteArea.Resize(PeriodCount).Offset(WorksheetFunction.CountA(wksResults.Ticker))
         ' Paste the values
         ResultTickerPasteArea.Value = TickerCell.Value
         ResultValuesPasteArea.Value = CompleteResults
      End If
     
      ' Bump the TickerIndex used to display the status
      TickerIndex = TickerIndex + 1
     
   Next

   ' End DDE conversation
   #If BloombergInstalled Then
      DDETerminate ChannelNumber
   #End If

Kevin

You MIGHT be able to intercept the Worksheet_Calculate event which is triggered at the end of every calculation - and since the Bloomberg worksheet functions are only evaluated as part of a worksheet calculation request, this might work.

Private Sub Worksheet_Calculate()

   ' Check to see if all Bloomberg results are in for the day
   
   ' If so then saev and close the workbook

End Sub

Kevin

The above code is foreign to me. Lets use my example:

Run Macro: 'Formulas' which adds all of the BLP formulas

Run Macro: 'Finish' copies and pastes values of all of the cells, and then saves and closes the file

Would I just copy the code you gave me in between the two macros  'Formulas' and 'Finish'?

>Would I just copy the code you gave me in between the two macros  'Formulas' and 'Finish'?
No. That was just some sample code illustrating how to use the DDE API calls.

Try this:

   Application.Calculation = xlCalculationManual
   Formulas
   Sheets("BloombergPullSheet").Calculate
   Finish

Kevin

Kevin, I tried the code above and it did not work. All of the values were NAs because it didn't wait to finish calculating before it copied and pasted the values.  You mentioned trying:

Private Sub Worksheet_Calculate()
   ' Check to see if all Bloomberg results are in for the day
   ' If so then saev and close the workbook
End Sub

How do I check to see if all Bloomberf results are in?