Bloomberg formula refresh

S

sd266

New Member
Joined
May 24, 2017
Messages
6
Hi Fellow Excel Mates,

Hope you are doing good. I am trying to automate a Bloomberg refresh process but seem to get stuck with a event i.e Bloomberg data refresh. the whole process is broken into 3 parts which runs in loop.
a. copies ticker from database
b. refresh Bloomberg
c. run a analysis and copy back the results in database and loop to step a until the last record

Now the problem what I got to from this forum and other Bloomberg related discussion is that Bloomberg formulas does not refresh while a macro is running and I am unsure as to I can I break my code so that Bloomberg update happens.

Thanks in advance for you help. I am working on this project for quite a long time and seems clueless with this roadblock.

Code: 
Sub Run_SplinterSequence()

'
'This macro automates the processing of a list of records.
'When executed the list of records is splintered into individual rows which are then run through an analysis process.
'The output results are then appended to the end of each record in the list.
'The process will loop through each row of data in the list until all rows are processed.
'
'Macro by SD.
'

Dim ClipSnip As String

Debug.Print "Running initial data preparation sequence..." & vbNewLine

'Copy 'Raw_Data' data to 'Process_Data' tab
Debug.Print "Transferring applicable data from 'Raw_Data' to 'Process_Data' tab..."
    Application.ScreenUpdating = False
    Sheets("Raw_Data").Select
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$P$110").AutoFilter Field:=11, Criteria1:="GO"
    Columns("B:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Process_Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Debug.Print "Data transfer completed." & vbNewLine


'Calculate total number of rows for splinter sequence
Debug.Print "Calculating total volume for splinter sequence..."

    Sheets("Process_Data").Select
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(C[-12])"
    Range("P2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Dim maxRowNum As Long
    maxRowNum = Range("Q2").Value
    
Debug.Print "-Data rows: " & maxRowNum & ", Record splinters: " & maxRowNum - 1 & "." & vbNewLine
    
Debug.Print "Initiating Splinter Sequence..." & vbNewLine

Call SplinterSplash

'Transfer record splinter values + execute data analysis process.

    Dim i As Long
    Dim timeval As Date
    
    Sheets("Splinter").Select
    timeval = Range("K3").Value
    
    
    For i = 2 To maxRowNum
     
Debug.Print "'Control Panel' data transfer sequence initiated..."
Debug.Print "Transferring data splinter: " & i - 1 & "..."
     
    '1st Currency (Base currency (currency 1))
    Sheets("Process_Data").Select
    Range("D" & i).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("D" & i).Value)
    Debug.Print "Process_Data Cell D" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("control panel").Select
        Range("D5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        ClipSnip = CStr(Range("D5").Value)
        Debug.Print "control panel Cell D5 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
        
    '2nd Currency (Accounting currency (currency 2))
    Sheets("Process_Data").Select
    Range("E" & i).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("E" & i).Value)
    Debug.Print "Process_Data Cell E" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("control panel").Select
        Range("D7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        ClipSnip = CStr(Range("D7").Value)
        Debug.Print "control panel Cell D7 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
        
    'Currency of the Principal Amount
    Sheets("Process_Data").Select
    Range("F" & i).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("F" & i).Value)
    Debug.Print "Process_Data Cell F" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("control panel").Select
        Range("D13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        ClipSnip = CStr(Range("D13").Value)
        Debug.Print "control panel Cell D13 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
    
    'Tenor
    Sheets("Process_Data").Select
    Range("H" & i).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("H" & i).Value)
    Debug.Print "Process_Data Cell H" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("control panel").Select
        Range("D15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        ClipSnip = CStr(Range("D15").Value)
        Debug.Print "control panel Cell D15 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
            

    'Strike % (Set OTM strike x% away from Spot)
    Sheets("Process_Data").Select
    Range("I" & i).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("I" & i).Value)
    Debug.Print "Process_Data Cell I" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("control panel").Select
        Range("D17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        ClipSnip = CStr(Range("D17").Value)
        Debug.Print "control panel Cell D17 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard" & vbNewLine
    
    Sheets("bloomberg link").Select
    Rows("39:39").Select
        
    Application.Run "RefreshEntireWorksheet"
            
    Debug.Print "Splinter transfer complete." & vbNewLine
        
    Debug.Print "Initiating time delay sequence..."
    Debug.Print "Pause delay set to " & timeval & ", Pause initiation time: " & Now()
            
    Application.Wait (Now + TimeValue(timeval))
        
    Debug.Print "Pause terminated @: " & Now() & vbNewLine
    
    'Tweaked here
    
    Sheets("control panel").Select
    Range("P16").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("P16").Value)
    Debug.Print "Control Panel Cell P16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("Process_Data").Select
        Range("M" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         
        ClipSnip = CStr(Range("M" & i).Value)
        Debug.Print "Process_Data Cell M" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
    
    Debug.Print "Initiating splinter analysis sequence..."
    
    Call RunAnalysis
    
    Calculate
    
    'Auto calculation off
    'Application.Calculation = xlManual
    
    Debug.Print "Splinter analysis complete." & vbNewLine
    
    Debug.Print "Transferring splinter: " & i - 1 & " output values to 'Process_Data' tab..."
'output data fields
    Sheets("control panel").Select
    Range("P13").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("P13").Value)
    Debug.Print "Control Panel Cell P13 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("Process_Data").Select
        Range("K" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         
        ClipSnip = CStr(Range("K" & i).Value)
        Debug.Print "Process_Data Cell K" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
    
    
    Sheets("control panel").Select
    Range("Q13").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("Q13").Value)
    Debug.Print "Control Panel Cell Q13 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
    
        Sheets("Process_Data").Select
        Range("L" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
    
        ClipSnip = CStr(Range("L" & i).Value)
        Debug.Print "Process_Data Cell L" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
            
            
'    Sheets("control panel").Select
'    Range("P16").Select
'    Application.CutCopyMode = False
'    Selection.Copy
'
'    ClipSnip = CStr(Range("P16").Value)
'    Debug.Print "Control Panel Cell P16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
'
'        Sheets("Process_Data").Select
'        Range("M" & i).Select
'        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'         :=False, Transpose:=False
'
'        ClipSnip = CStr(Range("M" & i).Value)
'        Debug.Print "Process_Data Cell M" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
        

    Sheets("control panel").Select
    Range("Q16").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ClipSnip = CStr(Range("Q16").Value)
    Debug.Print "Control Panel Cell Q16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("N" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("N" & i).Value)
        Debug.Print "Process_Data Cell N" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"
        
    
    Sheets("control panel").Select
    Range("P18").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("P18").Value)
    Debug.Print "Control Panel Cell P18 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
        
        Sheets("Process_Data").Select
        Range("O" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         
        ClipSnip = CStr(Range("O" & i).Value)
        Debug.Print "Process_Data Cell O" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard" & vbNewLine
        
         
    'Auto calculation On
    'Application.Calculation = xlAutomatic
    
    Debug.Print "Splinter output transfer complete!" & vbNewLine
    
    
    If i < maxRowNum Then
        Debug.Print "Splinter sequence: " & i - 1 & " of: " & maxRowNum - 1 & " complete, looping to next splinter sequence..." & vbNewLine
    Else
        Debug.Print "Splinter sequence: " & i - 1 & " of: " & maxRowNum - 1 & " complete." & vbNewLine
        Debug.Print "All splinter sequences completed successfully! ;D" & vbNewLine
                
    End If
        
    Next i

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top