Search code examples
pythonexcelexcel-formulaexport-to-excelbloomberg

xlsxwriter bloomberg formula

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")')
Solution

  • 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()