Hi,

I have an excel file which contains a financial model which I would like to use for all the stocks on the S&P 500. So, I have a masterlist of all the stocks on the S&P 500. I am trying to program excel to go through the list one-by-one, open the file with the model and insert the stock name in one of the cells and let the file update itself from Bloomberg and then close it and save it automatically. My problem is I dont know how to autoupdate and automatically close the file once the update is complete.

This is what I have so far:
==================================

Sub UpdateBloomberg()
Dim Temp, TempFile As String
Dim a, I As Integer
Range("C6").Select 'this selects the column with all the stocks
Range(Selection, Selection.End(xlDown)).Select

    For a = 1 To 500
            Temp = Cells(a, 3).Value
            I = 1
            Do While Mid(Temp, I, 1) <> " "
                TempFile = TempFile + (Mid(Temp, I, 1))
                I = I + 1
            Loop
            TempFile = "C:\Documents and Settings\Administrator\Desktop\Test\" + TempFile + ".xls"
            MsgBox TempFile
        Workbooks.Open Filename:="FINANCIAL MODEL.xls"
        Application.Run "BLPLinkReset"
        Application.Run "RefireBLP"
        ActiveWorkbook.SaveAs Filename:=TempFile
        ActiveWorkbook.Close
        TempFile = ""
    Next a
End Sub
===========================

I would appreciate any help.

Thanks

Ihsan

Ihsan

I find myself with the same question you posed 30 months ago. Did you ever uncover a solution?

I too am trying to run an analysis on each of the S&P 500. Mine uses daily compounding the determing the compounded annualized total investment rate over a specifided period of time. The SPX_Companies tab uses a BLPB() function to get the ticker symbols on the S&P 500. the CAGR tab uses a BLPH() function to pull the stock prices and a BLPB() function to pull in the dividend data. From this the total return is calculated.

I have a macro, pasted in below, that goes between the two sheets so that the total return of each company is iteratively calculated. Unfortunately, it takes so long for the data to load that I needed to add a MSG box to pause macro termination long enough for the macro to load. This means that I have to click "okay" 500 time to complete the analysis. The Wait and senseless looping don't help because they dont idle the computer's execution. Any help would be appreciated. Here is what I have.

' CheckCAGR Macro
' Macro recorded 10/31/2006
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
For i = 500 To 1 Step -1
    Selection.Copy
    Sheets("CAGR").Select
    Application.Run "BLPLinkReset"
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("SPX_Companies").Select
    Msg = i
    Application.Run "BLPLinkReset"
    Application.Run "RefireBLP"

    'exittime = Now() + 2.31481481481481E-04
    'currenttime = Now()
    'Do Until currenttime > exittime
    ' currenttime = Now()
    ' Loop
    MsgBox Msg
    ActiveCell.Offset(0, 2).Range("A1:D1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(1, -2).Range("A1").Select
Next i
End Sub

Thanks

If you do not need the links to update dynamically, you can use DDE to update the values and this will loop through.. if you have access to Bloomberg type WAPI <go> this will show you how to programatcaly download data using DDE or an activeX component.. much quicker than using the BLP formula.. hope this helps.. let me know if you need any more info