Is anyone using GitHub - pfalcon/ppxml2db: Scripts to import PortfolioPerformance (https://github.com/portfolio-performance/portfolio) XML into a SQLite DB and export back? In Windows?
Relevant discussion in anybody interested in PP with a database? · Issue #2216 · portfolio-performance/portfolio · GitHub. @pfalcon comment summarises the approach.
This functionality would address a lot of PP issues but given there is no reply I assume no active forum members are using it. Although the Makefile requires a POSIX-like operating system the functionality can be done easily in Windows. The python scripts should be operating system independent.
My issue is the python script under Windows 10 fails at the start, parsing the xml. Can anyone get it to work in a POSIX-like operating system? Commits show the repo is actively maintained.
Hi @flywire,
I remember bumping accidentally into this coding quite a while ago, but never spent time digging in.
Let me go through and come back.
Cheers
Facing the same issue on MacOS, but did not spent any time with the source code.
Have you try to use python3?
~/TEST/ppxml2db$ python3 ppxml2db.py kommer.xml kommer.db
2024-06-24 17:48:19 INFO Handling <security>
2024-06-24 17:48:20 INFO Handling <watchlist>
2024-06-24 17:48:20 INFO Handling <account>
2024-06-24 17:48:20 INFO Handling <portfolio>
2024-06-24 17:48:20 INFO Handling <account-transaction>
2024-06-24 17:48:20 INFO Handling <portfolio-transaction>
2024-06-24 17:48:20 INFO Handling <crossEntry>
2024-06-24 17:48:20 INFO Handling <taxonomy>
2024-06-24 17:48:20 INFO Handling <dashboard>
2024-06-24 17:48:20 INFO Handling <properties>
2024-06-24 17:48:20 INFO Handling <settings>
seems to work for me
my python3 is exactly
~/TEST$ python3 --version
Python 3.10.12
Thank you for confirming it is working in linux and showing the output, and thanks to @sn1kk3r5 for reporting the Windows error also occurs in MacOS.
I will start working through the code. I wonder if parsing the line ending is an issue?
I’m on another system now: Windows 11 Pro Version 23H2 OS build 22631.3737
Microsoft Windows [Version 10.0.22631.3737]
(c) Microsoft Corporation. All rights reserved.
E:\sqlite>python
Python 3.12.4 (tags/v3.12.4:8e8a4ba, Jun 6 2024, 19:30:16) [MSC v.1940 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> ^Z
E:\sqlite>pip install lxml
...
Successfully installed lxml-5.2.2
E:\sqlite>python ppxml2db.py kommer.xml kommer.db
2024-06-25 12:09:04 INFO Handling <security>
2024-06-25 12:09:04 INFO Handling <watchlist>
Traceback (most recent call last):
File "E:\sqlite\ppxml2db.py", line 378, in <module>
conv = PortfolioPerformanceXML2DB(root)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "E:\sqlite\ppxml2db.py", line 241, in __init__
self.handle_watchlist(w)
File "E:\sqlite\ppxml2db.py", line 168, in handle_watchlist
fields = {"list": id, "security": self.uuid(sec)}
^^^^^^^^^^^^^^
File "E:\sqlite\ppxml2db.py", line 55, in uuid
el = self.resolve(el)
^^^^^^^^^^^^^^^^
File "E:\sqlite\ppxml2db.py", line 45, in resolve
ref = el.get("reference")
^^^^^^
AttributeError: 'NoneType' object has no attribute 'get'
E:\sqlite>
Driven by a desire for better access to classification data I’ve recently been looking into this as well.
I wasn’t able to ‘make’ dax.dab in a Windows environment, but once I had installed Sqlite3 this was easily performed in Linux (Mint).
The developer of ppxml2db sets out a requirement to use the DAX sample file, but I see you @flywire used kommer.xml. Given the DAX sample file is no longer available from the welcome page I think I understand your reasoning for opting for kommer.xml, but its highly likely there will be differences in the data structure (field names etc) of these two files. I very much suspect this has been the cause of the issue you’ve experienced.
I managed to obtain the DAX sample file from an earlier version of PP and so as to encourage the potential development of ppxml2db I’m attaching a zip file to this post containing that xml file and the DAX.db I created.
dax.zip (1.0 MB)
While I understand the POC, I guess it does make sense to run this with different portfolio combinations as well.
I mean, the POC already proofed it’s ability to transfer data into a db file.
There is not mutch more knowledge to gain and nothing to win compared when the script runs with your own portfolio.
In this case, you will have access to a wide variety of analyzing possibilities.
@ProgFriese https://sqlitebrowser.org/ is a good open source multi-OS app. Can you attach kommer.db?
The xml regeneration is pretty close:
No I mean the db file so I can test it rather than the png.
Sorry, I wasn’t really awake yet.
kommer.db.zip (438,6 KB)
db2ppxml.py works fine in Windows now.
https://github.com/pfalcon/ppxml2db is functional in Windows now (see test).
Kudos to @pfalcon for persisting with the development to fix Windows issues relying on user feedback for an OS he doesn’t use. I thank him for this great python tool to make PP data more accessible.
Also, thanks to the community for giving me the files to test this tool.
Can you test again?
Please note the caveat which hopefully @AndreasB can accommodate:
I’ve been using ppxml2db every day since the project was started, though “using” is a bit of misnomer, as I was suffering thru it. Due to known deficiencies in PP XML format, portfolio-performance/portfolio#3417, import is very resource-intensive and slow, so it’s a pain to run it any time. So, I’ve grown to make my experiments in portfolio-performance/portfolio#3417 into production-ready code and switch to use the new XML format afterwards. The specific plan is:
- Contribute the required changes to PortfolioPerformance. Well, post the pull request to be specific.
- Update ppxml2db for new format.
- Stop supporting old format (no changes / no bug reports processed).
Usefulness of this for other parties depends on p.1, and of course I don’t have control over whether my contribution will be accepted, in what way, and in which timeframe. So, just posting as a disclosure of future plans.
DB creation works. Pushing data into the db file doesn’t.
During the run I do see a journal file which is growing but gets deleted automatically and the db file, remains empty.
Console prints this:
Can you confirm you are not running a PP sample file?
- Cash/security transfers between accounts are currently not supported.
@Rich28 Any success?
It is the kommer sample file now.
Yes I did it with my own file and not the kommer. In addition the kommer file changed since the script was initially build.
Keep an eye on status-and-known-issues.
Not an issue, it worked when the developer ran it through the tool yesterday.
Sorry I got sidetracked with other stuff, so only just coming back to this now. I’ve got a few comments:-
Extract classifications.zip (487 Bytes)
kommer.xmltest kommertest.bat
@echo off
if %1!==! goto usage
if exist %1.db del %1.db
if exist tmp.txt del tmp.txt
rem for /f "tokens=*" %%f in (tables.txt) do echo .read %%f.sql >>tmp.txt
for %%f in (*.sql) do echo .read %%f >>tmp.txt
sqlite3 -init tmp.txt %1.db .quit
rem del tmp.txt
@echo on
@ver
@python --version
@python ppxml2db.py --version
python ppxml2db.py %1.xml %1.db
python db2ppxml.py %1.db %12.xml
goto end
:usage
@echo off
echo.
echo Usage: %0 filename(without extension)
echo.
:end
[If you add *.sql files to the folder from the repo then create tables.txt with a list of the repo sql files (without the extension) and enable line with in (tables.txt) instead of in (*.sql).]
Microsoft Windows [Version 10.0.22631.3880]
Python 3.12.4
ppxml2db.py 0.7.1
btw:
SQLite version 3.36.0 2021-06-18 18:36:39
DB Browser for SQLite Version 3.13.0