Trying to modify values on an Excel by using Pandas. Screenshot of current Excel: [1]: https://i.stack.imgur.com/Lg0Mb.png
When trying to modify values in the last row, I want to access values of its different columns using a combination of loc[] and iloc[]. However, it does not work. I do not understand why can't I access the value, as it only works when a value is repeated in the index. For example, if TSLA was twice in the Excel. Apparently, loc[] does not let me look for a value.
tradeStopLoss = 5
def sellWriter(tradeStopLoss):
import pandas as pd
import datetime as dt
import pytz
import numpy as np
now = dt.datetime.now(pytz.timezone('US/Eastern'))
ExcelPD = pd.read_excel(r"xxx.xlsx")
ExcelPD.set_index('TICKER', inplace=True)
TICKER = 'TSLA'
DIAYHORAVENTA = now.strftime("%m/%d/%Y %H:%M:%S")
PRECIOVENTA = 5466
RETORNO = round((PRECIOVENTA - (ExcelPD.loc[TICKER].iloc[-1].loc['BUY PRICE'])
/ ExcelPD.loc[TICKER].iloc[-1].loc['BUY PRICE']),2)
MULTIPLORIESGO = round((RETORNO / ExcelPD.loc[TICKER].iloc[-1].loc['RISK']),2)
PROFITORLOSS = (PRECIOVENTA - ExcelPD.loc[TICKER].iloc[-1].loc['BUY PRICE'])\
* ExcelPD.loc[TICKER].iloc[-1].loc['QUANTITY']
idx = np.arange(ExcelPD.shape[0])[ExcelPD.index == TICKER][-1]
col1 = ExcelPD.columns.get_loc('SELL DATE')
col2 = ExcelPD.columns.get_loc('SELL PRICE')
col3 = ExcelPD.columns.get_loc('RETURN')
col4 = ExcelPD.columns.get_loc('RISK MULTIPLE')
col5 = ExcelPD.columns.get_loc('P/L')
ExcelPD.iloc[idx, col1] = DIAYHORAVENTA
ExcelPD.iloc[idx, col2] = PRECIOVENTA
ExcelPD.iloc[idx, col3] = RETORNO
ExcelPD.iloc[idx, col4] = MULTIPLORIESGO
ExcelPD.iloc[idx, col5] = PROFITORLOSS
ExcelPD.to_excel(r"xxx.xlsx")
Output:
Traceback (most recent call last): File "xxxx", line 92, in sellWriter(tradeStopLoss) File "xxxxx", line 67, in sellWriter RETORNO = round((PRECIOVENTA - (ExcelPD.loc[TICKER].iloc[-1].loc['BUY PRICE']) AttributeError: 'str' object has no attribute 'loc'