+ Reply to Thread
Results 1 to 5 of 5

Array grouping

  1. #1
    Registered User
    Join Date
    05-19-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Array grouping

    I'm using a Bloomberg terminal to get information about compaines and am strugling with how to set up an array. Here is what I'm attempting to do:

    The data will come in like this from theBbloomberg terminal since it is running on a loop for each ticker symbol:

    Ticker Symbol GICS Sector
    A Health Care
    AA Materials
    AAPL Information Technology
    ABBV Health Care
    ABC Health Care
    ABT Health Care


    As the data comes in, I would like to "basket" each one by sector, like this:
    Health Care Materials Information Technology
    A AA AAPL
    ABBV
    ABC
    ABT


    The problem is, I want to try this (seperately) not only for GICS sectors (which there are 10) but also by sub sector, sub industry, ect. That means there will be both an unknown number of "baskets" and an unknown number of ticker's in each basket. The only thing that will be known is the total number of ticker symbols.

    Since I'm not that great at coding here is an example:

    Infomation is recieved from Bloomberg => If there is already a "basket" then put the ticker into the basket. If there isn't a "basket" create a new basket and put the ticker into the new basket. Next ticker.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Array grouping

    Assuming the data starts from A1.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-19-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Array grouping

    Thank you for the help, I was able to use pieces of that in another part of my project! I am still having trouble with this issue though. I want to do this all in memory and then manipulate the data, the result of which will be put into the spreadsheet. I'm not sure about the rules for posting links (please delete if it is against the rules), but here is a link to the Bloomberg developers guide. The way the data is coming into excel is on page 112:
    http://www.openbloomberg.com/files/2...pers-guide.pdf

    securityData[ ] is an array of the tickers I requested data for, this will be dynamic because I will enter different lists of tickers on sheet1.
    fieldData[ ] an array of fields which contains the sector, sub industry, sub group, etc. and all the other information I need (like Price/Earnings ratio, Price/Book ratio, etc.) for each ticker.

    What I want it to create baskets for each of the lowest level sub groups in an array in memory (there is an unknown value of these baskets because it depends on the list of securities I use). Think of like a family tree where sector is the highest then it goes down to sub sector etc.

    I'm sorry, I know this is really easy stuff for you guys but I just started learning how to program two weeks ago so I'm a complete beginner

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Array grouping

    Better upload an Excel file with before and after format.

  5. #5
    Registered User
    Join Date
    05-19-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Array grouping

    Actually, I decided I only need the Min and Max values in the array. I am uploading all of my workbook and including the code in the post: Example.xlsx As you can see in the Security List tab, I have sorted the table by Sub Industry Name by "Metal & Glass Containers". For each day, I want to find the min and max value for PE_RATIO, PX_TO_BOOK_RATIO etc. of each sub industry. The way Bloomberg sends the data: For each ticker (in my code it is k) it will get each day's data in each row (which is b in my code) and each column (a in my code) for the row. Then it will move to the next row (the next day). At the end it adds one k (the next ticker) and repeats the process. I need to find the tickers (k) with the max and min values for each day and display the ticker along with the price (PX_LAST) for the corresponding day for the securities which are the min and max. I need to do this for every sub-industry in the table on the Security List.

    The Module:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is DataControl2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1