Page 1 of 1
The problem of Winrats reading Xlsx generated by Python
Posted: Mon Apr 14, 2025 6:57 am
by hardmann
Dear Tom:
I use python fetch data from Web and save it to XLSX data file. However, Winrats can not open it. The Xlsx data file is opened by MS Excel and resaved. Then Winrats can open it. I don't know why? Most of it is due to Python, but can winrats solve it?
Best Regard
Hardmann
Re: The problem of Winrats reading Xlsx generated by Python
Posted: Mon Apr 14, 2025 1:11 pm
by TomDoan
You would have to attach a copy of the problem file. The XLSX format is quite complicated and there may be features that Python is using that aren't commonly employed.
Re: The problem of Winrats reading Xlsx generated by Python
Posted: Mon Apr 14, 2025 7:42 pm
by hardmann
Dear Tom:
Python code
Code: Select all
from pandas_datareader import DataReader
from datetime import datetime
start_date = datetime(1990, 1, 1)
end_date = datetime.today()
ipman = DataReader('IPMAN', 'fred', start_date, end_date)
ipman.index = ipman.index.strftime('%Y/%m/%d')
output_file = 'US_IP.xlsx'
ipman.to_excel(output_file, sheet_name='IPMAN', index=True, index_label='Date')
Winrats Code
Code: Select all
open data "US_ip.xlsx"
calendar(m) 1990:01
data(format=xlsx,org=columns) 1990:01 2024:12 ipman
Best Regard
Hardmann
Re: The problem of Winrats reading Xlsx generated by Python
Posted: Tue Apr 15, 2025 10:21 pm
by TomDoan
Is there a reason you're not just doing:
cal(m) 1990:1
data(format=fred) / ipman
Re: The problem of Winrats reading Xlsx generated by Python
Posted: Wed Apr 16, 2025 9:30 am
by hardmann
Dear Tom:
My original intention was how Python and Winrats can exchange or read the xlsx files generated by each other. For example, the date format of the monthly xlsx file generated by winrats is YYYY: mm, which cannot be directly read by Python. We need to perform a conversion first.
Code: Select all
df = pd.read_excel('data_file.xlsx', sheet_name='Sheet1', index_col=0)
df.index = pd.to_datetime(df.index.str.replace(":", "-"))
Winrats reads the xlsx file generated by Python, and this is how I handle it. After generating xlsx in Python, call MS Excel to open it and then save it. Then Winrats can open it. I achieved my goal using a clumsy method, but I don't understand the reasons behind it, otherwise I could have executed it more conveniently and effectively.
Code: Select all
import win32com.client as win32
def open_and_save_excel(file_path):
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
workbook.Save()
workbook.Close()
excel.Quit()
del excel
Best Regard
Hardmann
Re: The problem of Winrats reading Xlsx generated by Python
Posted: Wed Apr 23, 2025 2:09 pm
by TomDoan
That seems to be an issue with the way the Python XLSX spreadsheet enumerates the worksheets. (This has only one, but there is still a "list").