python - Final piece of code missing for fundamental data extract from TWS IB

Question

I took the below code from one of the answered queries in Stackoverflow (unfortunately i cannot give full credit as i cannot locate the page anymore). I changed it a bit to fit my purpose. I want to extract historical Reuters data (fundamentalData) for a list of tickers. The below code works fine but it only grabs the last ticker data. I know i need to build a while loop but i tried many times and none worked. I'm sure this is a quick fix but since I am new at coding and python in general I just can't find the solution. Any help would be appreciated!

#Import all libriaries 
from ib.opt import ibConnection, message
from time import sleep
import lxml.etree
from bs4 import BeautifulSoup
import pandas as pd
import numpy as np
from IPython.core.debugger import set_trace
from ibapi import wrapper
from ibapi.client import EClient
from ibapi.common import *  
from ibapi.contract import Contract 

#upload excel file of list of company tickers you want to review
us_list= pd.read_excel(r'C:TWS APIRequest Reuters dataQuant Project IB TWS_test.xlsx', engine='openpyxl')

stocksList = us_list[['TICKER']]
stocksList

def fundamentalData_handler(msg):
    global imported
    imported = msg.data

def error_handler(msg):
    pass

# processing of the lines in financial statements
def extractVauleFromLineItem(fiscalperiod, code): 
    stage1 = fiscalperiod.find(name='lineitem', coacode=code)
    if (not stage1 is None):
        stage2 = stage1.get_text()
        if (stage2 == stage2):
            stage3 = float(stage2)
            if (stage3 == stage3):
                return (stage3)
            else:
                return (0.0)
                        
result = pd.DataFrame(columns =['Year', 'Ticker','control','TotalRevenue', 'GrossProfit', 'CommonSharesOutstanding','DilutedNormalizedEPS', 'totalCash', 'TotalDebt','Dividends'])

outcomes = []

for i, row in stocksList.iterrows():
    contract = Contract()
    contract.symbol = row['TICKER']
    contract.secType = "STK"
    contract.currency = "USD"
    contract.exchange = "SMART"


    tws = ibConnection("127.0.0.1",port=7497, clientId=901)
    tws.register(error_handler, message.Error)
    tws.register(fundamentalData_handler, message.fundamentalData)
    tws.connect()
    tws.reqFundamentalData(1,contract,'ReportsFinStatements')
    sleep(1)
    tws.disconnect()
    print(contract.symbol) 
    
    soup = BeautifulSoup(imported) # library for processing of the obtained XML data
    data = []
    print(soup.find(name='issueid', type="Ticker").get_text())
    print(soup.find(name='coid', type="CompanyName").get_text()) 
    
    # I found that IB API is not very stable. 
    # Sometimes it returns data of the wrong company. 
    # So the control is important
    print('Control -',contract.symbol == soup.find(name='issueid', type="Ticker").get_text())
    print()
    
    for fiscalperiod in soup.find_all(name="fiscalperiod", type="Annual"):
        year = fiscalperiod['fiscalyear']
        TotalRevenue = extractVauleFromLineItem(fiscalperiod, 'RTLR')
        GrossProfit = extractVauleFromLineItem(fiscalperiod, 'SGRP')
        CommonSharesOutstanding = extractVauleFromLineItem(fiscalperiod, 'QTCO')
        DilutedNormalizedEPS = extractVauleFromLineItem(fiscalperiod, 'VDES')
        totalCash = extractVauleFromLineItem(fiscalperiod, 'OTLO') 
        TotalDebt = extractVauleFromLineItem(fiscalperiod, 'STLD')
        Dividends = extractVauleFromLineItem(fiscalperiod, 'FCDP')
        
    
        thisYearData = (year,contract.symbol, (contract.symbol == soup.find(name='issueid', type="Ticker").get_text()),TotalRevenue , GrossProfit, CommonSharesOutstanding, totalCash, TotalDebt, Dividends)    
        data.append(thisYearData)
        
    df_data = pd.DataFrame(data, columns =['Year','control','TotalRevenue', 'GrossProfit', 'CommonSharesOutstanding','DilutedNormalizedEPS', 'totalCash', 'TotalDebt','Dividends'])

    df_data = df_data.sort_values(by=['Year'])

question from:https://stackoverflow.com/questions/65861589/final-piece-of-code-missing-for-fundamental-data-extract-from-tws-ib

Answer 1

Waitting for answers