xlsxwriter bloomberg formula

Question

I'm trying to get xlsxwriter to put a =BDP formula in a cell, but it results in an error when I try to open the excel output and the result is 0.0 Does anyone know how to fix this? Thanks!

sheet.write_formula('B3','=BDP("id","field")')

Answer 1

There are a few changes required to get this working:

1. The formula needs to be prefixed with _xll. since it isn't a standard Excel formula and is only available via a plugin.
2. It should be an array formula.
3. The strings in the formula should be double quoted and not single quoted.

The following should work if you have the Bloomberg plugin installed:

import xlsxwriter
    
workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_array_formula('B4', '_xll.BDP(LEFT(C3,LEN(C3)-1)&" Corp", "Issuer")')

workbook.close()