Bloomberg Array Formula

[theprincipal78]

Hi all</SPAN></SPAN>

1. I have the below values in cell (“A1:A31”) of Sheet1 </SPAN></SPAN></SPAN>
2. and the below macro in a standard module.</SPAN></SPAN></SPAN>

Everything works fine so far.</SPAN></SPAN>


I would like to:</SPAN></SPAN>
make it more dynamic and have the user determine the below Fields 0 to 15 in a worksheet.</SPAN></SPAN>
Then run the macro based on the fields that the user entered.</SPAN></SPAN>

Appreciate the help.</SPAN></SPAN>


Values in cell (“A1:A31”) of Sheet1</SPAN></SPAN>

BBG Security</SPAN></SPAN>
AXP US Equity</SPAN></SPAN>
BA US Equity</SPAN></SPAN>
CAT US Equity</SPAN></SPAN>
CSCO US Equity</SPAN></SPAN>
CVX US Equity</SPAN></SPAN>
DD US Equity</SPAN></SPAN>
DIS US Equity</SPAN></SPAN>
GE US Equity</SPAN></SPAN>
GS US Equity</SPAN></SPAN>
HD US Equity</SPAN></SPAN>
IBM US Equity</SPAN></SPAN>
INTC US Equity</SPAN></SPAN>
JNJ US Equity</SPAN></SPAN>
JPM US Equity</SPAN></SPAN>
KO US Equity</SPAN></SPAN>
MCD US Equity</SPAN></SPAN>
MMM US Equity</SPAN></SPAN>
MRK US Equity</SPAN></SPAN>
MSFT US Equity</SPAN></SPAN>
NKE US Equity</SPAN></SPAN>
PFE US Equity</SPAN></SPAN>
PG US Equity</SPAN></SPAN>
T US Equity</SPAN></SPAN>
TRV US Equity</SPAN></SPAN>
UNH US Equity</SPAN></SPAN>
UTX US Equity</SPAN></SPAN>
V US Equity</SPAN></SPAN>
VZ US Equity</SPAN></SPAN>
WMT US Equity</SPAN></SPAN>
XOM US Equity</SPAN></SPAN>


Standard Module</SPAN></SPAN>

Sub Test_BloombergFields()</SPAN></SPAN>
Dim ws As Worksheet</SPAN></SPAN>
Dim rngTicker As Range</SPAN></SPAN>
Dim strTicker As String</SPAN></SPAN>
Dim c As Range</SPAN></SPAN>
Set ws = ThisWorkbook.Sheets("Sheet1")</SPAN></SPAN>

With ws</SPAN></SPAN>
Set rngTicker = .Range("A2:A31") ' ticker range</SPAN></SPAN>
i = 1</SPAN></SPAN>
For Each c In rngTicker</SPAN></SPAN>

strTicker = c.Value</SPAN></SPAN>
Dim strFld(0 To 15) As Variant</SPAN></SPAN>

strFld(0) = "NEWS_HEAT_STORY_FLOW"</SPAN></SPAN>
strFld(1) = "NAME"</SPAN></SPAN>
strFld(1) = Range("B1")</SPAN></SPAN>
strFld(2) = "LAST_PRICE"</SPAN></SPAN>
strFld(3) = "TIME"</SPAN></SPAN>
strFld(4) = "RT_PX_CHG_NET_1D"</SPAN></SPAN>
strFld(5) = "RT_PX_CHG_PCT_1D"</SPAN></SPAN>
strFld(6) = "HIGH"</SPAN></SPAN>
strFld(7) = "LOW"</SPAN></SPAN>
strFld(8) = "VOLUME"</SPAN></SPAN>
strFld(9) = "OPEN"</SPAN></SPAN>
strFld(10) = "PX_YEST_CLOSE"</SPAN></SPAN>
strFld(11) = "CHG_PCT_YTD"</SPAN></SPAN>
strFld(12) = "CHG_PCT_1YR"</SPAN></SPAN>
strFld(13) = "VWAP"</SPAN></SPAN>
strFld(14) = "VOLUME_AVG_30D"</SPAN></SPAN>
strFld(15) = "VOLUME_AVG_6M"</SPAN></SPAN>

.Range("A1").Offset(i, 1).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(0) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 2).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(1) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 3).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(2) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 4).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(3) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 5).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(4) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 6).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(5) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 7).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(6) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 8).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(7) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 9).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(8) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 10).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(9) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 11).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(10) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 12).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(11) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 13).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(12) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 14).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(13) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 15).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(14) & """" & ")"</SPAN></SPAN>
.Range("A1").Offset(i, 16).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(15) & """" & ")"</SPAN></SPAN>

i = i + 1</SPAN></SPAN>

Next</SPAN></SPAN>
End With</SPAN></SPAN>

End Sub</SPAN></SPAN>

 

[npvrader]

I'm not 100% sure if you are trying to allow the user to select the BBG instrument names (A1 through 31) or the BBG Field names in the strFld array (there are 16). I'm going to assume you want the strFld ( or the field names) selected. Since the fields appear to be static, my approach would be to allow the user to select on the worksheet via a drop down validation list and read the values into the array or as 16 variables. I would input the BDP formulas using a loop if I didn't want to populate unrequested fields or change my formula to accommodate a null/blank/don't use me field in the drop down validation list.

 

[theprincipal78]

Hi npvrader

I'm at the point where the user has entered his strFld in a worksheet column (lets assume Sheet1 column A) i'm just not able to "read the values into the array".

 

[npvrader]

Okay, I must confess that I can't think of an excel vba application where I have utilized arrays. From what I've read, they are great and very useful, but I haven't found an application that needs them. So, I would be inclined to create 16 variables and read the values in cell at a time. So,
strFld1 = Range("A2").Value and do it for 16 variables - maybe even loop through it.

I remember reading somewhere that you can read the cells into an array, and I would guess that you would need to do this in a for each loop (for each cell in b2:b17) so something like (you will need to declare your array):

For Each Cell in range("B2:B17")
strFld(Cell) = Cell.value
next Cell

 

[theprincipal78]

hi there

got it. took me some time but
fixed the first row and turned it into a table.
so as I add row by row by row the range configured as table drags down all the formulas across the columns.

but thanks anyway.


Sub BloombergFieldsAndFormulas()
Dim ws As Worksheet
Dim rngTkr As Range
Dim strTkr As String
Dim c As Range
Dim strFld As String
Dim i As Long

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Insert New Table")

With ws
Set rngTkr = .Range("MyNewFieldRange") ' formulas range
i = 3
For Each c In rngTkr

'good - without apostrophs
.Range("A1").Offset(1, i).Value = "=BDP(" & "A2" & "," & c.Value & ")"

.Range("A1").Offset(0, i).Value = c.Value
i = i + 1
Next
End With
End Sub

 

[Blade Hunter]

This will show you how to read a range into an array:

Sub TransposingStuff()
Dim MyArr As Variant, NumRows As Long
MyArr = Application.Transpose(Selection)
NumRows = (UBound(MyArr))
MsgBox NumRows
End Sub

This is a potential version of your code using a hard coded array:

Sub Test_BloombergFields()
Dim ws As Worksheet, rngTicker As Range, strTicker As String, c As Range, strFld As Variant, X As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
strFld = Array("NEWS_HEAT_STORY_FLOW", "NAME", Range("B1"), "LAST_PRICE", "TIME", "RT_PX_CHG_NET_1D", "RT_PX_CHG_PCT_1D", "HIGH", "LOW", "VOLUME", "OPEN", "PX_YEST_CLOSE", "CHG_PCT_YTD", "CHG_PCT_1YR", "VWAP", "VOLUME_AVG_30D", "VOLUME_AVG_6M")
With ws
    Set rngTicker = .Range("A2:A31") ' ticker range
    i = 1
    For Each c In rngTicker
        strTicker = c.Value
        For X = LBound(strFld) To UBound(strFld)
            .Range("A1").Offset(i, X).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(X) & """" & ")"
        Next
        i = i + 1
    Next
End With
End Sub

and with the range instead

Sub Test_BloombergFields()
Dim ws As Worksheet, rngTicker As Range, strTicker As String, c As Range, strFld As Variant, X As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
strFld = Application.Transpose(Range("A1:A15"))
With ws
    Set rngTicker = .Range("A2:A31") ' ticker range
    i = 1
    For Each c In rngTicker
        strTicker = c.Value
        For X = LBound(strFld) To UBound(strFld)
            .Range("A1").Offset(i, X).Value = "=BDP(" & """" & strTicker & """" & "," & """" & strFld(X) & """" & ")"
        Next
        i = i + 1
    Next
End With
End Sub

Cheers

Dan

 

[Blade Hunter]

Okay, I must confess that I can't think of an excel vba application where I have utilized arrays. From what I've read, they are great and very useful, but I haven't found an application that needs them. So, I would be inclined to create 16 variables and read the values in cell at a time. So,
strFld1 = Range("A2").Value and do it for 16 variables - maybe even loop through it.

I remember reading somewhere that you can read the cells into an array, and I would guess that you would need to do this in a for each loop (for each cell in b2:b17) so something like (you will need to declare your array):

For Each Cell in range("B2:B17")
strFld(Cell) = Cell.value
next Cell

Here is an example of one (of many) uses for them

ColumnTitles = Array("", "Artist", "Title", "ARIA Chart", "Expected Chart", "Notes", "Media Release", "Airplay Chart", "Spins", "Physical Sales", "iTunes", "Vevo", "Spotify", "Social Media Sentiment", "Weeks Since Release", "$")
ColumnTitles2 = Array("", "", "", "TW (HP)", "", "", "Week", "TW   LW", "TW   LW", "Ship(SR) - OTC(TW)", "TW   LW", "TW - SR (global)", "TW - LW", "", "", "Retail $")
ColumnWidths = Array(10.57, 24.57, 23.43, 13.14, 13.14, 30.86, 10.71, 15.14, 13#, 17.29, 20.14, 21.57, 21.57, 40.14, 13.71, 15.57)
For X = LBound(ColumnWidths) To UBound(ColumnWidths)
    Columns(X + 1).ColumnWidth = ColumnWidths(X)
Next
For X = LBound(ColumnTitles) To UBound(ColumnTitles)
    With Cells(2, X + 1)
        .Formula = ColumnTitles(X)
        If X + 1 <> 5 And X + 1 <> 13 And X + 1 <> 14 And X + 1 <> 15 Then
            .VerticalAlignment = xlBottom
        Else
            .VerticalAlignment = xlCenter
        End If
        .WrapText = True
    End With
Next
For X = LBound(ColumnTitles2) To UBound(ColumnTitles2)
    With Cells(3, X + 1)
        .Formula = ColumnTitles2(X)
        .WrapText = True
        .Font.Size = 14
        .Font.Bold = False
    End With
Next

Sets up the headings on a report that runs every week:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2		Artist	Title	ARIA Chart	Expected Chart	Notes	Media Release	Airplay Chart	Spins	Physical Sales	iTunes	Vevo	Spotify	Social Media Sentiment	Weeks Since Release	$
3				TW (HP)			Week	TW LW	TW LW	Ship(SR) - OTC(TW)	TW LW	TW - SR (global)	TW - LW			Retail $
Priorities

Hope that helps

Dan