通过Excel从Bloomberg API获取数据 [英] get data from bloomberg api with python via excel

问题描述

我编写了一些代码，该代码通过使用python在excel中编写查询来成功地从Bloomberg api中提取数据，然后启动excel通过Bloomberg插件获取数据，将数据传输到csv中(以便于在python和其他下游需求)并返回.

I've written some code which successfully pulls data from the bloomberg api by using python to write a query in excel, it then launches excel gets the data via the bloomberg plugin, transfers the data into a csv (for easier use in python and other downstream needs) and returns.

唯一的问题是，要将数据提取到excel中，我必须先设置time.sleep(40)，然后再保存文件，因为通过excel进行的连接需要足够的时间来提取数据.我想知道是否有一种方法可以自动检测何时提取数据? -计划是扩大规模以在一个循环中或在多个线程中进行许多查询，并且为了提高效率，我需要检测何时提取数据以开始下一步.

The only issue is that for the data to be pulled into excel I have to set time.sleep(40) before I save the file as the connection through excel needs enough time to pull the data. I am wondering if there is a way to automatically detect when the data has been pulled? - The plan is to scale this up to do many queries in a loop or across multiple threads and for this to be efficient I need to detect when the data has been pulled to start the next step.

任何想法都会真正有用吗?参见功能run_VBA到目前为止，这是我的代码:

Any ideas would be really helpful? See the function run_VBA Here is my code so far:

import xlsxwriter
import pandas as pd
import xlwings as xl
import glob
import openpyxl
import time
import os
import win32com.client

def write_bloomberg_query_in_excel():
    """main function, make an excel workbook containing an api query, open the file and allow the data to 
        be pulled from the api, save and close, then transfer the data into a pandas df and csv"""

    WB = 'C:/python_workspace/bloomberg_api_data_pull/excel_queries/daily_wind_temp_precip.xlsx'
    location = "EGLL"

    make_workbook(WB, location)

    run_VWA(WB, location)

    df = df_from_excel(WB, location)  # sheetname is optional
    df.to_csv(WB.split('.')[0]+'.csv', index=False)

    return

def run_VWA(WB, location):
    """open the excel file, allow enough time to pull the data, then close and save"""

    bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
    xl=win32com.client.DispatchEx("Excel.Application")  
    xl.Workbooks.Open(bb)
    xl.AddIns("Bloomberg Excel Tools").Installed = True
    wb = xl.Workbooks.Open(Filename=WB) #opens workbook in readonly mode.

    xl.Visible = False
    # need help here!! this time works for this query but I need to scale
    # the api calls and need a way to select time or detect when 
    # the download has happened
    time.sleep(40)         

    wb.Close(SaveChanges=1)

    xl.Quit()
    #Cleanup the com reference. 
    del xl   

    return

def make_workbook(WB, location):
    """write a bloomberg api query into an an excel workbook """

    # Create a workbook and add a worksheet.
    workbook = xlsxwriter.Workbook(WB)
    worksheet = workbook.add_worksheet(location)

    # Some data we want to write to the worksheet.
    W = """=BSRCH("comdty:weather","provider=wsi","location={}",
        "model=ACTUALS","frequency=DAILY","target_start_date=2018-08-01",
        "target_end_date=2018-12-31", 
        "fields=WIND_SPEED|TEMPERATURE|PRECIPITATION_24HR")""".format(location)

    # write to worksheet using formula
    worksheet.write(0, 0, W)
    # close
    workbook.close()
    return

def df_from_excel(path, SN):
    """read the contents of an excel file into a pandas dataframe"""
    app = xl.App(visible=False)
    book = app.books.open(path)
    sheet = book.sheets(SN)
    book.save()
    df = pd.read_excel(path, sheet_name=SN)
    app.kill()
    return df


if __name__=="__main__":
    write_bloomberg_query_in_excel()

我知道只从python api获取数据会更有意义，但是我在这里不支持'bsrch'类型的查询.

I know it would make more sense to just get this data from the python api, however there is no support for the 'bsrch' type of query which I am doing here.

但是我愿意接受更好的方法吗?最好是python，或者可能是R

However I'm open to better approaches? Preferably python, or possibly R

推荐答案

我建议使用 Rb中的Rblpapi 软件包，因为它具有bsrch函数.

I would suggest using the Rblpapi package in R, as it has a bsrch function.

这篇关于通过Excel从Bloomberg API获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！