Question about database and Bloomberg

[Yan He]

Does anyone happend to know is Acess can run real time function or download real-time data directly from Bloomberg ? I am using Excel to do this right now, but seems Excel has limit on the capacity. In thinking about moving the data to Acess...I couldn't figure out how to run all the functions now on Excel on Acess??

Thanks a lot.:smt100:smt100

[maciek]

I don't think this is possible, perhaps you could use some vba to get around this...
Call the BB technical support at 212 318 2000, they should be able to help

[Yan He]

Thanks, Maciek. I've tried "HELP""HELP"...but didn't getting what I want.

[rholowczak]

As far as I can tell, BB's "integration" functions are directed only at Excel.
I can take another look through in more details tomorrow but I seem to recall
doing something similar in the past and Excel was offered as the only solution.

So it sounds as if you are running up against the 65,556 row limit in a spreadsheet?

If so, perhaps consider breaking up the data collection into one hour chunks
and placing that on separate sheets.

Or, if possible, upgrade to Excel 2007 which I understand now has 1 million (or
possibly higher) max rows.

Cheers,

Prof .H.

[alain]

Hi Maggie,

I have never worked with the Bloomberg API but check if Bloomberg offers a COM object that you can interface with. If that is the case, you can instantiate that COM object from inside VBA. If not, you can wrap around the Bloomberg C++ API in a COM object and call it from Access. The latter is a task in itself.

[alain]

Maggie,

Check this link

http://www.wilmott.com/messageview.cfm?catid=10&threadid=29231

[rholowczak]

OK - Looks like it is not such a big deal. There is a tutorial (via WAPI <GO>) on adding the control to MS Excel. I basically followed that only using a form in MS Access and it seems to work OK. One main difference is in Access, to add a control to a form (not from the tool box) you have to pull down the "Insert" menu and choose "ActiveX Control". Once you do that you will see the Bloomberg Data Control.

From there just make a button to kick off the data request:

Private Sub Command1_Click()
BlpData1.Subscribe "EUR Curncy", 1, "Last_Price"
End Sub

Then you need a callback routine to "catch" the data as it comes in. This is done via the
BlpData1 control itself and the "Data" event it needs to handle:

Private Sub BlpData1_Data(Security As Variant, cookie As Long, Fields As Variant, Data As Variant, Status As Long)
' Do what you need to do with the "Data" such as inserting this into a table or saving to a file, etc.
End Sub

Let me know if you need more details

Cheers,

Prof. H.

[Yan He]

This is really great, Prof H. and Alain! Let me figure this out then....Thanks a lot!

[cofibreak]

Does anyone know if Bloomberg provides interest accrual data/factor for MBS etc types? Does this have something to do with NumeriX? We have a terminal and API access. This is for audit/verification purposes. Thanks.

[DominiConnor]

One solution is to find out which golf course your IT manager is spending his time, and get him to install a less obsolete version of Excel. You've got a **** old version, Excel 2007 has been out for a while now (the hint is in the name), and it can do million row series.

[cofibreak]

You may be right there, we have a golf course nearby :)
Maybe my question should have had a little more details...
We would like to use Bloomberg API within a .NET Windows application to make per security request and compare the accrual factor provided by Bloomberg to what is calculated in another system for the same security.
The user can decide what to do about the difference if any.
In this specific application, we are not looking to use Excel, though it is used elsewhere.
I am sure it is not Excel 2007, though. Any ideas, while I will go looking for my IT manager in the driving range ?! Thanks.

[MikeK]

There is plenty of sample .NET code that gets installed with the Bloomberg API. If you have it installed, check under "C:\blp\API\dotnet\v1.4.2.0\Samples\WinForm\C#"...it will show you how to make a request by security and more.

[cofibreak]

Thanks, MikeK, that really helps on the coding side.
I am also trying to get some metadata info from Bloomberg and things are looking brighter.
Great community!

[Andy]

If you find anything from bloomberg, please share. They are infamous for being very mysterious about their system and their documentation is nonexistent.
In order to get their api working, do you need a Bloomberg terminal running?

[alain]

be careful with bloomberg and requesting data through code. You might be violating their license or you might get a bill that you don't expect. They are also famous for that.

[Andy]

I read that fine print in their API documentation too. It says that if you request too much data via API, they will bill your firm extra fee. So, if you have some code that gets historical data for every single stock in the SP 100 going back 10 years just for the sake of testing, you may be shocked. Bloomberg has a data limit that you can request before getting charged but it does not explicitly state what the limit is.
And keep in mind that a lot of result from BB are just plain wrong but everyone is using it so it becomes the industry standard.

[theVerveNY]

Hi, Andy.

How are you? I just wanted to let you know that Bloomberg will not charge your firm extra money if you exceed your data limit. However, you will hit their radar and if you hit it too often, you will most likely receive a call from a member of the programming support team to find out if there may be a more optimal way to request the data you need without hitting your limit. Let me know if you have any further questions about this.

Take care.
David

[charlesdwright]

Excel 2003 is a rock.

How is 2007 shaping up in the real world?

One solution is to find out which golf course your IT manager is spending his time, and get him to install a less obsolete version of Excel. You've got a **** old version, Excel 2007 has been out for a while now (the hint is in the name), and it can do million row series.

[Andy]

However, you will hit their radar and if you hit it too often, you will most likely receive a call from a member of the programming support team to find out if there may be a more optimal way to request the data you need without hitting your limit. Let me know if you have any further questions about this.
Hi David,
Thanks for clarifying.
Without a declared data limit, how would the users know if they hit the radar. I think as BB users of big firms, people are not that much concerned with the bill but rather with how to efficiently acquire the data.
Do you have a "good practice" guidelines as to how each type of data should be acquired via Excel and API?

[theVerveNY]

Hi, Andy.

If you hit their monthly or daily limits, you will receive an error stating that in place of the data that would normally be returned via the API. Keep in mind that a very small percentage of Bloomberg API users actually even come close to hitting the limits, let alone hit it. Therefore, with normal usage (i.e. 2-3 thousand unique securities), you should come nowhere close to hitting the limits.

Currently, there is no option to purchase additional data for Desktop clients, but there is such an option for the Server API users to purchase additional bands of data. If a particular client needs more data than is allowed via the Desktop/Server API, Bloomberg does have other products available that were built to handle much larger volumes of data. It is B-Pipe for real-time data and Data License for static data.

I know that Bloomberg does make available best practices for optimizing your application and managing your data limits. There's a few links on WAPI's home page. One great way to limit your data usage hits is to make real-time subscriptions in place of static ones.

Dave

[Andy]

Dave,
If you have any good information about Bloomberg's RunManager product, let me know. We probably will bring in our BB sale guy but I want to hear something different from the sale pitch.
The preliminary info I have is that RunManager is a Bloomberg message quote parser like CMA's QuoteVision. Bloomberg is infamous when it comes to documentation so we want to have the right info before we purchase.

[oconnohd]

Hi All,

I, too, have a question concerning Bloomberg's data limits. I am currently creating a database that tracks certain stocks. For each stock, I am requesting numerous mneumonics. So, after opening and closing the database numerous times throughout the day, I get curious as to how much of my allotted "static" data is left.

Does anyone know if there is a tracker that updates how much data you can still use/how much you already have used?

Thanks!

KC

[satyag]

I know a little bit about how this system works. Let me put it this way, these limits are mainly to identify people misusing or abusing the api and effecting the servers and other clients. If you are requesting static fields again and again, bloomberg wants you to optimize that. To my knowledge, there is nothing like "alloted" usage. But there is a limit beyond which your usage will attract bloomberg's attention. This is not official but I believe anything under a million mnemonics per day is reasonable use.

Hi All,

I, too, have a question concerning Bloomberg's data limits. I am currently creating a database that tracks certain stocks. For each stock, I am requesting numerous mneumonics. So, after opening and closing the database numerous times throughout the day, I get curious as to how much of my allotted "static" data is left.

Does anyone know if there is a tracker that updates how much data you can still use/how much you already have used?

Thanks!

KC

[doug reich]

I don't know if you can pull the number automatically. We were getting a weird error during some development, and one of their helpdesk people read off some monthly and daily quota numbers and our current usage, although they didn't really explain what they meant. If you really want to know where you stand on these quantities, and you want to brave the "helpless" desk, you just might find out.

As I said, they didn't explain what the numbers meant nor what the consequences for exceeding them were.

Hi All,

I, too, have a question concerning Bloomberg's data limits. I am currently creating a database that tracks certain stocks. For each stock, I am requesting numerous mneumonics. So, after opening and closing the database numerous times throughout the day, I get curious as to how much of my allotted "static" data is left.

Does anyone know if there is a tracker that updates how much data you can still use/how much you already have used?

Thanks!

KC

[cofibreak]

I can see two things we could do to address some of the uncertainty from a data consumer point:

1. Keep track of the number of server hits you are making and catch spikes proactively.
2. In some cases, it is possible to send a single request to get data for multiple securities. This will probably count as a single hit.

BB also has a FTP solution, where you create a request file and put it in a designated location. Though the response is quite fast, this may not work for realtime scenarios.

[theVerveNY]

Dave,
If you have any good information about Bloomberg's RunManager product, let me know. We probably will bring in our BB sale guy but I want to hear something different from the sale pitch.
The preliminary info I have is that RunManager is a Bloomberg message quote parser like CMA's QuoteVision. Bloomberg is infamous when it comes to documentation so we want to have the right info before we purchase.

Hi, Andy.

Unfortunately, I am not quite familiar with Bloomberg RunManager. Sorry, I can't help you there. I guess the BB sales guy will be your best bet.

Dave

[theVerveNY]

I know a little bit about how this system works. Let me put it this way, these limits are mainly to identify people misusing or abusing the api and effecting the servers and other clients. If you are requesting static fields again and again, bloomberg wants you to optimize that. To my knowledge, there is nothing like "alloted" usage. But there is a limit beyond which your usage will attract bloomberg's attention. This is not official but I believe anything under a million mnemonics per day is reasonable use.


Please be aware that the limits for the desktop are much lower than those for the server license. However, the objective of Bloomberg is the same for both and the tips for managing your API limits are also the same. Here are a few of the important ones:
Real-time requests are always preferred to repeated static requests. The reason for that is that a real-time subscription only entails a single header request (for the initial paint of real-time data) and a tick monitor request. After that point, tick updates are sent from the Bloomberg backend until you either desubscribe or disconnect. This is far much less taxing on the Bloomberg backend. To you this means less hits, as your daily hit total will only be increased by the total number of securities in the real-time request. The number of fields does not impact your hit total on a real-time request. However, for a static request, your hit total will increase by the aggregate of each security/field pairing. For instance, if you make a static request that contains 10 securities and 5 fields, then that would be a total of 50 hits. Obviously, if you then continue to make this same static request (also called polling), it will be 50 hits each time it is sent out.
If you hit your daily limit you will receive a -4001 error code (if using C API), or the error message "#N/A Dly Lmt". If you hit your monthly limit, the error code will be -4002 or an error message of "#N/A Mth Lmt" (ActiveX Data Control or BDx() functions in Excel). In the API for .NET, a DataLimitExceededException will be thrown. There is also a limit for the number of concurrent real-time subscriptions that can be made. It is measured in numbers of unique securities being subscribed to at any one time. If you surpass this, you will receive a -3001 error code.

The limits are not based on the number of function calls you make. Therefore, if you make s historical or subscribe type call that requests 10 securities and five fields at once, it will still be counted by the Bloomberg backend as 50 hits.
To address the FTP solution, as a side note. Cofibreak (great user name) is referring to Bloomberg's Data License product, which provides customers a way to obtain large volumes of static data within a file via FTP.

One final note concerning the data limits, I am not sure of the actual limit numbers for each product, but I am very sure that 1,000,000 mnemonics requested in one day would be far too many and put you over the daily limits for both products. To put it into perspective, there are approx. 25K valid mnemonics in the Bloomberg API data dictionary.

I hope this helps.

Dave

[bsteeves]

I have been using Bloomberg with excel for many years now and there is a quota to the number of times you can populate cells. I believe the monthly limit is 1,000,000 with the option to upgrade twice every month if need be. Once you exceed 1,000,000 you will receive the #n/A limit

[doug reich]

Since this thread first came up, I spent much more time looking at WAPI -- if you look around there, it explains what the different limits are on data use, and how you can minimize your hits by using the API calls wisely.

EDIT: They describe the limits and how to stay below them in the "Extended Rules and Usage Limits of the Bloomberg API" page in WAPI.

[Wallstyouth]

Microsoft has worked diligently to improve Excel 2007 for financial professionals to use to trade with and thru
"Processing power: Prior to the release of Microsoft Excel 2007, all spreadsheets were single threaded and could not take advantage of today’s multi-core technology.

Spreadsheet size and memory limitations: Excel 2003 can have 256 columns by 64K rows and can use 1GB of memory. Excel 2007 can have 16K columns by 1M rows, but is still constrained by a 32-bit address space.

Microsoft Office Excel 2007: Microsoft has made significant improvements in Excel 2007 guaranteed to excite spreadsheets enthusiasts. Users can now create spreadsheets with up to 16,384 columns and 1,048,576 rows and benefit from expanded memory capabilities up to the maximum amount of memory addressable within 32-bits. Finally, Excel now includes a multi-threaded calculation engine. This means, that spreadsheets can maximize computational power. When running multi-core, multi-CPU, or hyper-threaded processes, linear improvements can be seen in performance, assuming parallelization of calculations.

Excel Services: Excel Services, part of Microsoft Office SharePoint Server 2007 (MOSS), extends the capabilities of Excel 2007 by providing a server-side complement. Excel services provides the necessary components to share and maintain spreadsheets in a secure manner.

We use the Equis / QuoteCenter / Reuters DDE feed and pull 927 stocks into one Excel 2007 Spreadsheet in array format that uses only 12% to 20% cpu usage all day and as far as i can tell we don't get dropped quotes and get a lot of custom fields that help us create many different custom indicators (Beta, MoneyFlow, Volatility, MAvgs, Hi Lo Ranges, TradeCounts, Block Counts, Block Sums, etc...

Other shops; eSig, DTN, etc... killed our cpu with only 500 stocks !!...

We pull 927 unique stock records with 9 columns each into SQL Server every 1 minute during the day using a VBA windows timer and SQL Server DTS package and then allow SQL Server to do the Stored Procs analysis to feed data to us about what is going on in equity indexes, sectors, etc to better understand how to trade the equity futures and in showing the weakness or strengths in groups of tradeable Correlated Stock Baskets