Loading Bloomberg data within Macro Loop

I

IUcM

New Member
Joined
Jan 14, 2011
Messages
4
Hi,

I've been trying to do this for days now with no success. I have a spreadsheet where I need to load several BDHs (historical data points from a Bloomberg terminal). These are based off of 2 cells, one is the user supplied date (which is then manipulated in the plain spreadsheet to supply the other dates) and the other is the Security ticker.

What I am trying to do is have a macro that will take these cells and write in the BDH formula when these things are labeled "Ready" in a third cell. The values of those BDH calls then need to be pasted into another sheet. This is not the problem.

The problem is the loading data. I've been reading this forum for a couple of days and have yet to find an answer that works but, I need the macro to wait for the data to load before it continues on to paste things. If I use a Application.Wait or a Do While Loop with an arbitrary counter, the data does not finish loading from Bloomberg.

I've read some solutions that break this operation in half so that you have 2 macros so you can run the second once the data has loaded, however this is all part of a much bigger loop that runs through 1000+ securities.

The loop needs to wait for the data from one security to load, copy it and then pull the next security and re-loop. My question is how do I get Excel to pause in such a way that allows this?

I am using the 2010 Edition of Excel.
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Consider using Application.OnTime to schedule a macro to check if the data has refreshed:

Code: 
'code to plonk in formulas
 
'check if last cell in formula range has updated
 
If Activesheet.Range("B2000").Text Like "#N/A Updating Security" Then  'amend this to suit - both cell and message
   Application.OnTime Now+TimeSerial(0,0,4), "Check_Calcs"    'waiting 4 seconds - maybe modify to earlier/later?
Else
  'do your copy?
  'eg Call Copy_Routine
End If
End Sub
 
 
Sub Check_Calcs()
If Activesheet.Range("B2000").Text Like "#N/A Updating Security" Then  'amend this to suit - both cell and message
   Application.OnTime Now+TimeSerial(0,0,4), "Check_Calcs"   'waiting 4 seconds - maybe modify to earlier/later?
Else
  'do your copy?
  'eg Call Copy_Routine
End If
End Sub
 
Upvote 0
It still seems to be copying over the data that hasn't been able to load yet. Could you walk me through how the Check_Calcs sub is working? I'm not really seeing how the two bits of code below differ.
 
Upvote 0
Code: 
    'BDH #20
    Range("DS9").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(R9C3,'Trading Dates 1993-2013'!R1C1:R5300C21,21,0)"
        

    'Supply First Security from list
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=R2C2"
    
    'Security Ticker to Data Sheet
    Range("D3").Select
    Selection.Copy
    Sheets("All ETF Spreads").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
       
    'Check if data has loaded
    Sheets("All ETF Data").Select
    If Range("H3").Text Like "Ready" Then
           Range("H12:H51").Select
           Selection.Copy
           Sheets("All ETF Spreads").Select
           Range("B5:B44").Select
             Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
              xlNone, SkipBlanks:=False, Transpose:=False
        Else
        
    If ActiveSheet.Range("D12:DW12").Text Like "#N/A Requesting Data" Then
    Application.OnTime Now + TimeSerial(0, 0, 30), "Check_Calcs" 'waiting 30 seconds
       Else
           Range("H12:H51").Select
           Selection.Copy
           Sheets("All ETF Spreads").Select
           Range("B5:B44").Select
             Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
              xlNone, SkipBlanks:=False, Transpose:=False
 
        End If
     End If

Ok so here's my code with your Check_Calcs sub in it. It doesn't seem to be working how I'd like (ie waiting for the data to load and then going onto copy and paste the values). Am I doing it wrong?

Thank you for your help, I welcome any and all suggestions at this point.
 
Upvote 0
Have you included a separate Check_Calcs sub anywhere?

You need to check the Text property of only a single cell eg the last cell in the range with a formula in it. The idea being that once this has returned data, all the rest will have too.
 
Upvote 0
I do, it looks like this:
Code: 
    If ActiveSheet.Range("H3").Text Like "Please Wait Processing" Then
    Application.OnTime Now + TimeSerial(0, 0, 30) 'waiting 30 seconds
       Else
           Range("H12:H51").Select
           Selection.Copy
           Sheets("All ETF Spreads").Select
           Range("B5:B44").Select
             Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
              xlNone, SkipBlanks:=False, Transpose:=False
 
        End If
     End Sub


There's 20 columns that will updates, but the data pull does not always pull them in order. The Cell H3 checks to see if any of them are still processing
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
web scraping help
Replies
0
Views
200
dwatters
D
F
  • Solved
Webscraping a site with no element ids and returning data
Replies
6
Views
1K
fizzydrink
F
C
  • Question
Import data based other workbook's data and filter
Replies
0
Views
251
CamBF
C
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
198
Nick Loughton
N
R
  • Question
Loop Through Folder and Take Specific Actions Based on Filename
2 3
Replies
21
Views
1K
reberryjr
R

Forum statistics

Threads
1,214,951
Messages
6,122,442
Members
449,083
Latest member
Ava19

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