Write and retain Bloomberg formula using absolute column and relative row

Question

Hello,

I would like to write Bloomberg formulas as-is to excel spreadsheet to use Bloomberg Excel Add-In to update data.  I created a text input with with the formula to use, where the desired output is based on absolute column and relative row.  However, when I execute the workflow it overwrites the same output on each row.

Current Output

=IFERROR(@IF(Bloomberg!$A4="Private","-",BDH(Bloomberg!$A4,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

Desired Output

=IFERROR(@IF(Bloomberg!$A4="Private","-",BDH(Bloomberg!$A4,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

=IFERROR(@IF(Bloomberg!$A5="Private","-",BDH(Bloomberg!$A5,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

=IFERROR(@IF(Bloomberg!$A6="Private","-",BDH(Bloomberg!$A6,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

Sample data attached.

Appreciate any suggestions.

Thanks in advance!

bbg_formulas.xlsx

makingitwork · Answer

Thanks caltang for the links below. Little more information, for this example, I do not have access/connection to bloomberg api nor data. The end result is to write the formulas to excel spreadsheet using an absolute column and relative row method (www.ablebits.com/office-addins-blog/relative-absolute-reference-excel/)  send the excel output file to team members that have the BBG Excel Add-In on their systems to run the formulas embedded from an Alteryx workflow.

I need the formulas to iterate through the rows based on a column value

Current method results :

=IFERROR(@IF(Bloomberg!$A4="Private","-",BDH(Bloomberg!$A4,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

Desired results:

=IFERROR(@IF(Bloomberg!$A4="Private","-",BDH(Bloomberg!$A4,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

=IFERROR(@IF(Bloomberg!$A5="Private","-",BDH(Bloomberg!$A5,"PX LAST",Bloomberg!$E$2,Bloomberg!$E$2)),"-")

caltang · Answer

I think this is quite apt:

I do not think it is directly replicable in Alteryx to call in those formulas similar to Excel. Unless Excel, as your input, has already called in the raw data, then you can manipulate it with Alteryx. But judging from your request, your IF statement seems to be calling again via different sheets depending on True/False.

Maybe these links can help:

* https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Adding-Bloomberg-API-to-Alteryx-Workflow/td-p/962391
* https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-Pull-Data-from-Bloomberg/td-p/19192 
* https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/DOWNLOAD-DATA-FROM-BLOOMBERG-AND-IMPORT-THEM-INTO-ALTERYX/td-p/790748

Beyond that, I'm not able to proceed further as I do not have connection to Bloomberg on my personal computer.