function that returns a series of TRUE answers in vector frm

[PearTrader]

I need a function (preferably NOT VBA) that will look at a database (like DGET) and return a series of answers, in the form of a vector or seperated by a comma, or something similar. DGET will only extract a single value.

For example - we have a portfolio of stocks...different industries, different sectors. I want a dynamic list of all stocks in the 'energy' sector, or 'telecom' simply by looking at the list of stocks, and their industries.

Thank you for your help.
PT

 

[PaddyD]

Hi - welcome to the board.

If "I want a dynamic list of all stocks in the 'energy' sector, or 'telecom' simply by looking at the list of stocks, and their industries" is what you want, then "I need a function (preferably NOT VBA) that will look at a database (like DGET) and return a series of answers, in the form of a vector or seperated by a comma" is probably not the way to go about getting it.

post back with more info about what data you've got, how it's generated, where it is on the sheet etc & what output you need. an example would help.

 

[PearTrader]

Hi PaddyD, thanks for responding.
The data comes via a DDE link into excel from Bloomberg. I'm trying to design a spreadsheet that will be as userfriendly as possible. Enter stock, and number of shares, and refresh the DDE links from bloomberg, and it will (hopefully) generate a number of reports. One, being a list of sectors (already done), the percent of our portfolio in each sector (already done), and a list of stocks in each sector (not done). Auto Filter really isnt an option for several reasons (its not as user-friendly for all those who will be using it, among other reasons). Ideally, I would like a function or series of functions that will look down the 'sector' column, and if its sees that XOM, BP, CVX (oil stocks) are all in a certain sector, it will place those in a row according to its sector. DGET and VLOOKUP only return single values, whereas in this situation, there will be multiple occurances where the result is TRUE...that is why I was hoping to find a function that would export the multiple results in vector form.

If you have any other suggestions or ideas, please share them. Thanks!

 

[Aladin Akyurek]

Hi PaddyD, thanks for responding.
The data comes via a DDE link into excel from Bloomberg. I'm trying to design a spreadsheet that will be as userfriendly as possible. Enter stock, and number of shares, and refresh the DDE links from bloomberg, and it will (hopefully) generate a number of reports. One, being a list of sectors (already done), the percent of our portfolio in each sector (already done), and a list of stocks in each sector (not done). Auto Filter really isnt an option for several reasons (its not as user-friendly for all those who will be using it, among other reasons). Ideally, I would like a function or series of functions that will look down the 'sector' column, and if its sees that XOM, BP, CVX (oil stocks) are all in a certain sector, it will place those in a row according to its sector. DGET and VLOOKUP only return single values, whereas in this situation, there will be multiple occurances where the result is TRUE...that is why I was hoping to find a function that would export the multiple results in vector form.

If you have any other suggestions or ideas, please share them. Thanks!

Post a sample with expected results. BTW, when you talk about a vectors in the context of a spreadsheet software, one would think that you mean an array like {1,2,3} or {"a";"B","x"} which will then be fed directly to other formulas, while your description above seems to apply a range.

 

[PearTrader]

The spreadsheet would appear something like this...(input Symbol, Bloomberg DDE outputs Industry)

Symbol Industry
ALA Telecomunications
CPN Electric
TWX Media
DYN Pipelines
AMR Airlines
MEE Coal
AES Electric (second occurance)
RIG Oil & Gas
OSTK Internet
CNX Coal (second occurance)
PAAS Mining
EP Pipelines (second occurance)
SIL Mining (second occuranace)
EVG Oil & Gas (second occurance)
BTU Coal (third occurance)

Output - A consolidated list of each Industry with portfolio weighting - this is already done...

Telecom (10%)
Electric (8%)
Media (5%)
Pipelines (5%)
Airlines (12%)
Coal (8%)
Oil & Gas ....
Internet ....
Mining ....

Now, I want an array or vector (preferably in a single cell) that will tell me which stocks appear in each industry

Telecom (10%) ALA
Electric (8%) CPN, AES
Media (5%) TWX
Pipelines (5%) DYN
Airlines (12%) AMR
Coal (8%) CNX, MEE, BTU ....
Oil & Gas .... RIG, EVG ....
Internet .... OSTK
Mining .... SIL, PAAS .....

I'd rather these be placed in an array or vector format because it is used as a report only....no other cells will be linked to them. Additionally, there may be a large number of stocks in one industry or another. Copy and pasting formulas across the lenght of the spreadsheet may evenually lead to error. It really doesnt matter to me how the results come out - seperated by comma, space, curly bracket, etc...whatever works.

Thanks!