The problem of Winrats reading Xlsx generated by Python

[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

[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.

[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

[TomDoan]

Is there a reason you're not just doing:

cal(m) 1990:1
data(format=fred) / ipman

[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

[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").