|
Our workbook options model has two worksheets, BLBOptions which has the bulk and real-time
data formulas. Since we are dealing with large amounts of data to improve system performance
we retrieved the information as array formulas to cut down on the number of links. Since array
formulas prevent you from inserting columns to insert formulas for calculations, we decided to
add second worksheet, OptionAnalytics, which references the cells which contain data on the
BLBOptions worksheet. This allows us to insert formulas to better display and analyze the
real-time data.
For example, since the Bloomberg’s Expiration Date field is retrieved as text, you would need to
convert the text to a date value before you could perform a calculation on the data or apply a
specific date format. To convert the text to a date value we used Excel’s VALUE formula:
You then copy the formula down to apply it to the whole column.
|
