I am trying to download the data from IB api and upload it onto a google sheet. If the data already exists in the sheet, then I want the new data to be added into the sheet without overwriting the previous data. This is what I have done so far:
from ibapi.client import EClient
from ibapi.wrapper import EWrapper
from ibapi.contract import Contract
from threading import Timer
import pandas as pd
from datetime import datetime
import os
import pygsheets
class TestApp(EWrapper, EClient):
def __init__(self):
EClient.__init__(self, self)
self.df = pd.DataFrame(columns=['accountName', 'contract', 'position','marketPrice', 'marketValue', 'averageCost', 'unrealizedPNL','realizedPNL'])
self.df1 = pd.DataFrame(columns=['key', 'value', 'currency'])
def error(self, reqId, errorCode, errorString):
print("Error: ", reqId, " ", errorCode, " ", errorString)
def nextValidId(self,orderId):
self.start()
def updatePortfolio(self, contract: Contract, position: float, marketPrice: float, marketValue: float,
averageCost: float, unrealizedPNL: float, realizedPNL: float, accountName: str):
self.df.loc[len(self.df)] = [accountName, contract.symbol,position,marketPrice, marketValue,averageCost, unrealizedPNL,realizedPNL]
# See unrealized P&L, which will be the total unrealized P&L since the position was open, the
# realized P&L which would be the realized profit loss for the current day
# If you’ve closed out any positions, as well as the account name, the current market value,
# the average cost used to open the position and of course the position size
print("UpdatePortfolio.", "Symbol:", contract.symbol, "SecType:", contract.secType, "Exchange:",
contract.exchange,
"Position:", position, "MarketPrice:", marketPrice, "MarketValue:", marketValue, "AverageCost:",
averageCost,
"UnrealizedPNL:", unrealizedPNL, "RealizedPNL:", realizedPNL, "AccountName:", accountName)
def updateAccountValue(self, key: str, val: str, currency: str, accountName: str):
# returns the cash balance, the required margin for the account, or the net liquidity
self.df1.loc[len(self.df)] = ['ExchangeRate', val, currency]
print("UpdateAccountValue. Key:", key, "Value:", val, "Currency:", currency, "AccountName:", accountName)
def updateAccountTime(self, timeStamp: str):
print("UpdateAccountTime. Time:", timeStamp)
def accountDownloadEnd(self, accountName: str):
print("AccountDownloadEnd. Account:", accountName)
def start(self):
# ReqAccountUpdates - This function causes both position and account information to be returned for a specified account
# Invoke ReqAccountUpdates with true to start a subscription
self.reqAccountUpdates(True, "U0000000") # <----- Change account number here
def stop(self):
self.reqAccountUpdates(False,"U00000") # <----- Change account number here
self.done = True
self.disconnect()
def main():
app = TestApp()
app.connect("127.0.0.1", 7496, 0)
Timer(5, app.stop).start()
app.run()
# service file is the ib_data json file key
service_file_path = "googlesheet_api_json"
gc = pygsheets.authorize(service_file= service_file_path )
spreadsheet_id = '00000'
sh = gc.open_by_key(spreadsheet_id)
# inserting date and time to the data
now = datetime.now()
dt_string = now.strftime("%d/%m/%Y %H:%M:%S")
app.df['Date_Time'] = dt_string
#check if the google sheet has previous data
wk_sheet_stock = gc.open('workbook').sheet1
cells = wk_sheet_stock.get_all_values(include_tailing_empty_rows=False, include_tailing_empty=False, returnas='matrix')
if len(cells) < 1:
print('no data in the file')
data_write = sh.worksheet_by_title('stocks')
data_write.clear('A1',None,'*')
data_write.set_dataframe(app.df, (1,1), encoding='utf-8', fit=True)
data_write.frozen_rows = 1
data_write = sh.worksheet_by_title('currency')
data_write.clear('A1', None, '*')
data_write.set_dataframe(app.df1, (1, 1), encoding='utf-8', fit=True)
data_write.frozen_rows = 1
else:
print('adding data to the file')
stock_df_values = app.df.values.tolist()
currency_df_values = app.df1.values.tolist()
sh.values_append('stocks', {'valueInputOption': 'RAW'}, {'values': stock_df_values})
sh.values_append('currency', {'valueInputOption': 'RAW'}, {'values': currency_df_values})
if __name__ == "__main__":
main()
I get the error: AttributeError: 'Spreadsheet' object has no attribute 'values_append'
I am confused as to how do I go about this. Thank you for your help!
CodePudding user response：
I believe your goal is as follows.
- You want to append the values using
values_append. - You want to remove your current issue of
I get the error: AttributeError: 'Spreadsheet' object has no attribute 'values_append'. - You want to achieve this using pygsheets for python.
Modification points:
When I saw the document of pygsheets, it seems that the method of
values_appendis forclass pygsheets.sheet.SheetAPIWrapper. In this case,shofsh = gc.open_by_key(spreadsheet_id)is not it. I think that this might be the reason for your current issue ofAttributeError: 'Spreadsheet' object has no attribute 'values_append'.And, I cannot know your values of
stock_df_valuesandcurrency_df_values. So, in this answer, I propose a modification point by guessing that those values are the 2-dimensional array. Please be careful about this.
When these points are reflected in your script, how about the following modification?
From:
sh.values_append('stocks', {'valueInputOption': 'RAW'}, {'values': stock_df_values})
sh.values_append('currency', {'valueInputOption': 'RAW'}, {'values': currency_df_values})
To:
gc.sheet.values_append(spreadsheet_id, stock_df_values, "ROWS", "stocks")
gc.sheet.values_append(spreadsheet_id, currency_df_values, "ROWS", "currency")
Note:
I guessed that the values of "stocks" and "currency" are the sheet names in the Spreadsheet of
sh = gc.open_by_key(spreadsheet_id). Please be careful about this.If you cannot know whether the values of
stock_df_valuesandcurrency_df_valuesare a 2-dimensional array, please modify them as follows.gc.sheet.values_append(spreadsheet_id, stock_df_values if isinstance(stock_df_values[0], list) else [stock_df_values], "ROWS", "stocks") gc.sheet.values_append(spreadsheet_id, currency_df_values if isinstance(currency_df_values[0], list) else [currency_df_values], "ROWS", "currency")In this answer, I suppose that other parts except for
sh.values_appendof your script work fine. Please be careful about this.