import xlwings as xw
import pandas as pd
from yahoofinancials import YahooFinancials
import time, os, sys
from enum import Enum
print(
"""
==============================
Dividend & Portfolio Overview
==============================
"""
)
class Column(Enum):
long_name = 1
ticker = 2
current_price = 5
currency = 6
conversion_rate = 7
open_price = 8
daily_low = 9
daily_high = 10
yearly_low = 11
yearly_high = 12
fifty_day_moving_avg = 13
twohundred_day_moving_avg = 14
payout_ratio = 17
exdividend_date = 18
yield_rel = 19
dividend_rate = 20
def timestamp():
t = time.localtime()
timestamp = time.strftime("%b-%d-%Y_%H:%M:%S", t)
return timestamp
def clear_content_in_excel():
if LAST_ROW > START_ROW:
print(f"Clear Contents from row {START_ROW} to {LAST_ROW}")
for data in Column:
if not data.name == "ticker":
sht.range((START_ROW, data.value), (LAST_ROW, data.value)).options(
expand="down"
).clear_contents()
return None
def convert_to_target_currency(yf_retrieve_data, conversion_rate):
if yf_retrieve_data is None:
return None
return yf_retrieve_data * conversion_rate
def get_coversion_rate(ticker_currency):
if TARGET_CURRENCY == "TICKER CURRENCY":
print(f"Display values in {ticker_currency}")
conversion_rate = 1
return conversion_rate
conversion_rate = YahooFinancials(
f"{ticker_currency}{TARGET_CURRENCY}=X"
).get_current_price()
print(
f"Conversion Rate from {ticker_currency} to {TARGET_CURRENCY}: {conversion_rate}"
)
return conversion_rate
def pull_stock_data():
if tickers:
print(f"Iterating over the following tickers: {tickers}")
df = pd.DataFrame()
for ticker in tickers:
print(f"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(f"Pulling financial data for: {ticker} ...")
data = YahooFinancials(ticker)
open_price = data.get_open_price()
if open_price is None:
print(f"Ticker: {ticker} not found on Yahoo Finance. Please check")
df = df.append(pd.Series(dtype=str), ignore_index=True)
else:
try:
try:
long_name = data.get_stock_quote_type_data()[ticker]["longName"]
except (TypeError, KeyError):
long_name = None
try:
yield_rel = data.get_summary_data()[ticker]["yield"]
except (TypeError, KeyError):
yield_rel = None
ticker_currency = data.get_currency()
conversion_rate = get_coversion_rate(ticker_currency)
new_row = {
"ticker": ticker,
"currency": ticker_currency,
"long_name": long_name,
"conversion_rate": conversion_rate,
"yield_rel": yield_rel,
"exdividend_date": data.get_exdividend_date(),
"payout_ratio": data.get_payout_ratio(),
"open_price": convert_to_target_currency(
open_price, conversion_rate
),
"current_price": convert_to_target_currency(
data.get_current_price(), conversion_rate
),
"daily_low": convert_to_target_currency(
data.get_daily_low(), conversion_rate
),
"daily_high": convert_to_target_currency(
data.get_daily_high(), conversion_rate
),
"yearly_low": convert_to_target_currency(
data.get_yearly_low(), conversion_rate
),
"yearly_high": convert_to_target_currency(
data.get_yearly_high(), conversion_rate
),
"fifty_day_moving_avg": convert_to_target_currency(
data.get_50day_moving_avg(), conversion_rate
),
"twohundred_day_moving_avg": convert_to_target_currency(
data.get_200day_moving_avg(), conversion_rate
),
"dividend_rate": convert_to_target_currency(
data.get_dividend_rate(), conversion_rate
),
}
df = df.append(new_row, ignore_index=True)
print(f"Successfully pulled financial data for: {ticker}")
except Exception as e:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
print(exc_type, fname, exc_tb.tb_lineno)
df = df.append(pd.Series(dtype=str), ignore_index=True)
return df
return pd.DataFrame()
def write_value_to_excel(df):
if not df.empty:
print(f"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print(f"Writing data to Excel...")
options = dict(index=False, header=False)
for data in Column:
if not data.name == "ticker":
sht.range(START_ROW, data.value).options(**options).value = df[
data.name
]
return None
def main():
print(f"Please wait. The program is running ...")
clear_content_in_excel()
df = pull_stock_data()
write_value_to_excel(df)
print(f"Program ran successfully!")
show_msgbox("DONE!")
wb = xw.Book.caller()
sht = wb.sheets("Portfolio")
show_msgbox = wb.macro("modMsgBox.ShowMsgBox")
TARGET_CURRENCY = sht.range("TARGET_CURRENCY").value
START_ROW = sht.range("TICKER").row + 1 # Plus one row after the heading
LAST_ROW = sht.range(sht.cells.last_cell.row, Column.ticker.value).end("up").row
sht.range("TIMESTAMP").value = timestamp()
tickers = (
sht.range(START_ROW, Column.ticker.value).options(expand="down", numbers=str).value
)
所以,这段代码通过xlwings模块连接python和excel,当我运行它时,我会收到这个错误,我不知道该怎么办:
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\users\komp\exam\exam.py", line 178, in <module>
sht = wb.sheets("Portfolio")
^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\Komp\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\xlwings\main.py", line 5109, in __call__
return Sheet(impl=self.impl(name_or_index))
^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\Komp\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\xlwings\_xlwindows.py", line 943, in __call__
return Sheet(xl=self.xl(name_or_index))
^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\Komp\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\xlwings\_xlwindows.py", line 236, in __call__
v = self._inner(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\Komp\AppData\Local\Temp\gen_py\3.11\00020813-0000-0000-C000-000000000046x0x1x8.py", line 34531, in __call__
ret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pywintypes.com_error: (-2147352567, 'ÐÑибка.', (0, None, None, None, 0, -2147352565), None)
我试着重写代码,重新安装模块,希望是模块或拼写错误,但它仍然不起作用
