|
Subject:
|
Update links in excel file
|
|
Posted By:
|
Wall st Guru
|
Post Date:
|
6/10/2004 10:38:07 AM
|
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
|
|
Reply By:
|
Friend2Pitbulls
|
Reply Date:
|
12/9/2006 7:06:44 PM
|
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
|
|
Reply By:
|
jrogers
|
Reply Date:
|
12/11/2006 4:09:42 AM
|
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
|
