StockGlance2020 - Python script to display and extract the totals of stock/fund unit qty balances by security
For anyone that wants it, Python script to extract / total stock / unit qty balances by Security (as MD doesn’t have a report to do this). Original Credit to @jameslarus and waynelloydsmith as I took StockGlance as a base).
For example: if I have share/security "Company X" in 3 different ac counts - e.g. 100, 300, 500, then this script will tell you that you have 900 of this security. MD reports do not do this. The report also shows you a lot more, deals with currency, totals, and can show cash balances.
It's quite clever with lots of user parameters and filters. Let me know of any issues...
- StockGlance2020_v2.py 71.8 KB
Showing page 2 out of 2. View the first page
Comments are currently closed for this discussion. You can start a new one.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
31 Posted by Stuart Beesley ... on 22 Oct, 2020 09:43 AM
.
32 Posted by derekkent23 on 22 Oct, 2020 09:54 AM
Hi Stuart
I still get.
SyntaxError: Non-ASCII character in file 'C:\Users\derek\OneDrive\MONEYDANCE\Script to display and CSV Export Security Totals\StockGlance2020.py', but no encoding declared; see http://www.python.org/peps/pep-0263.html for details
33 Posted by Stuart Beesley ... on 22 Oct, 2020 10:29 AM
Hi, I can’t find any dodgy characters…? Sorry to ask. I wonder if the script copied properly for you? How did you get from GitHub? You should click RAW and then select all and copy/paste; or download that raw file…
By all means send it back to me so I can look inside the file?
34 Posted by Stuart Beesley ... on 22 Oct, 2020 01:00 PM
Resolved: FYI - in GitHub you either need to right click the file link and save link as, or click RAW and then copy/paste the script. (If you just save what GitHub displays then you are just saving an HTML web page and not the actual script).
Regards Stuart
35 Posted by Cyril Couten on 23 Oct, 2020 07:35 AM
The display works fine (I understand you will add the cost & gain at a
later stage).
However the filing seems to have problems, no file being filed ... Pls see
below ...
Beginning file C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py at Fri
Oct 23 09:31:59 CEST 2020
StuWareSoftSystems...
StockGlance2020.py....... Version: 4d
Parameters...
Hiding Hidden Securities...
Hiding Inactive Accounts...
Hiding Hidden Accounts...
Selecting ALL Currencies...
Selecting ALL Securities...
Selecting ALL Accounts...
Including Cash Balances - WARNING - this is per account!
Splitting Securities by account - WARNING, this will disable sorting....
Will strip non-ASCII characters - e.g. Currency symbols from output
file... Using Delimiter: ,
Will display Stock balances and then extract to file:
C:\CC_DATA\MONEYDANCE\extract_stock_balances.csv (NOTE: Should drop non
utf8 characters...)
!!!FILE WILL BE GENERATED AFTER YOU CLOSE THE TABLE VIEW WINDOW!!!
Error running script: Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: (13, 'Permission denied',
'C:\\CC_DATA\\MONEYDANCE\\extract_stock_balances.csv')
Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: [Errno 13] Permission denied:
'C:\\CC_DATA\\MONEYDANCE\\extract_stock_balances.csv'
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
36 Posted by Stuart Beesley ... on 23 Oct, 2020 07:41 AM
Very strange. It’s a permission denied issue.. :
- Do you have permissions to the folder?
- Was the file already open somewhere else?
- Can you try again, choose a different file?
- can you run in debug and send me that output?
Thanks
37 Posted by Cyril Couten on 23 Oct, 2020 07:54 AM
Here is the debug narratives ... I have deleted all the details which is
not relevant ...
Beginning file C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py at Fri
Oct 23 09:47:42 CEST 2020
StuWareSoftSystems...
StockGlance2020.py....... Version: 4d
DEBUG turned on
Parameters...
Hiding Hidden Securities...
Hiding Inactive Accounts...
Hiding Hidden Accounts...
Selecting ALL Currencies...
Selecting ALL Securities...
Selecting ALL Accounts...
Including Cash Balances - WARNING - this is per account!
Splitting Securities by account - WARNING, this will disable sorting....
Will strip non-ASCII characters - e.g. Currency symbols from output
file... Using Delimiter: ,
In grabTheFile ()
Scriptpath: C:\Users\cycou\.moneydance\Documents
Non Mac platform detected: Therefore I will run JFileChooser() to get
filename....
Will display Stock balances and then extract to file:
C:\CC_DATA\PP\extract_stock_balances retry 948.csv (NOTE: Should drop non
utf8 characters...)
In StockGlance2020 ()
In myJTable ()
!!!FILE WILL BE GENERATED AFTER YOU CLOSE THE TABLE VIEW WINDOW!!!
In createAndShowGUI ()
In getTableModel ()
MD Book: CCFinances
Base Currency: EUR : Euro
getAllCurrencies(): [EDENRED, MONCEY, Israeli New Shekel, INTERPARFUMS,
WENDEL, FAIVELEY TRANSPORT, ALSTOM, TIEPOLO RDT, Icelandic Krona, Egyption
Pound, BARCLAYS
...............deleted..............
Skipping Security with 0 shares..: CARREFOUR Curr: EUR Price: 47.83
Qty: 0
In getFooterModel ()
Generating the footer table data....
getFooterModel: was not allOneCurrency..
Hiding unused Currency Column...
ScreenSize: java.awt.Dimension[width=1920,height=1080]
Main JTable heights....
Row Count: 372
RowHeight: 16
Intercell spacing: 1
Header height: 20
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 980
Scrollbar height: java.awt.Dimension[width=0,height=0] 0
Footer JTable heights....
Row Count: 19
RowHeight: 16
Intercell spacing: 1
Header height: 0
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 335
Scrollbar height: java.awt.Dimension[width=48,height=10] 10
Total frame height required: 980 + 335 + Intercells: 0 1 = 1317
Waiting for JFrame() to close... Wait number...: 1
Waiting for JFrame() to close... Wait number...: 2
Waiting for JFrame() to close... Wait number...: 3
Waiting for JFrame() to close... Wait number...: 4
Waiting for JFrame() to close... Wait number...: 5
Waiting for JFrame() to close... Wait number...: 6
Waiting for JFrame() to close... Wait number...: 7
Waiting for JFrame() to close... Wait number...: 8
Waiting for JFrame() to close... Wait number...: 9
Waiting for JFrame() to close... Wait number...: 10
Waiting for JFrame() to close... Wait number...: 11
Waiting for JFrame() to close... Wait number...: 12
Waiting for JFrame() to close... Wait number...: 13
In windowClosing ()
Waiting for JFrame() to close... Wait number...: 14
No longer waiting....
In ExportDataToFile ()
Opening file and writing 392 records
Error running script: Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: (13, 'Permission denied', 'C:\\CC_DATA\\PP\\extract_stock_balances
retry 948.csv')
Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: [Errno 13] Permission denied:
'C:\\CC_DATA\\PP\\extract_stock_balances retry 948.csv'
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
38 Posted by Stuart Beesley ... on 23 Oct, 2020 07:59 AM
Can you try a directory that’s not C:\CC_DATA\MONEYDANCE\ ? And or the previous version of script? I’ve not changed anything here….
Thanks
39 Posted by Cyril Couten on 23 Oct, 2020 08:10 AM
Same ... This time filing on c:\\ ...
To be honest, apart from the 1st time where it worked, I have skipped
checking the real filing ... So you must have changed something between the
very first version and the subsequent ones ?
No longer waiting....
In ExportDataToFile ()
Opening file and writing 392 records
Error running script: Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: (13, 'Permission denied', 'C:\\extract_stock_balances.csv')
Traceback (most recent call last):
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1904, in
<module>
ExportDataToFile()
File "C:\CC_DATA\MONEYDANCE\StockGlance2020_v21oct_4d.py", line 1845, in
ExportDataToFile
if debug: print "Opening file and writing ", len(rawDataTable),
"records"
IOError: [Errno 13] Permission denied: 'C:\\extract_stock_balances.csv'
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
40 Posted by Stuart Beesley ... on 23 Oct, 2020 08:17 AM
OK… Hang fire, I will send you a new script soon and we will see what that does… If you send me your email I can email you directly? (You will have to write it as email space at space domain)
41 Posted by Cyril Couten on 23 Oct, 2020 08:22 AM
sure : [email blocked]
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
42 Posted by Stuart Beesley ... on 23 Oct, 2020 05:07 PM
V4e (beta) of script with many enhancements
43 Posted by Stuart Beesley ... on 26 Oct, 2020 09:41 AM
For completeness version 5 of this script released - attached. The latest can always be found here:
https://yogi1967.github.io/MoneydancePythonScripts/
Rollup of v4a to v4h enhancements. Key changes: enhanced filters; enhanced parameters; Parameters now persist between sessions; better rounding; New option to split Securities by Account; Enhanced display, sort, highlighting, formatting, scrolling etc; added cost basis, gain and % fields; enhanced CSV creation, including ability to exclude totals (for Pivot tables). By Stuart Beesley - StuWareSoftSystems - October 2020.
44 Posted by Cyril Couten on 26 Oct, 2020 06:31 PM
The display is fine ; if it was possible to sort stocks when display the
detail by account ... or by total amount ...
The file saving is still the same problem : to a folder of my choice, does
not do it ; if to the user directory, as yesterday, it works ....
In the end, it works well ; now I can have the % by stock ober my total
portfolio irrespective of the accounts where they are held ... Great
progress.
Tks !
*Debug when it does not work ... interestingly the detail of the csv lines
appear in the debug report ...*
Generating the footer table data....
getFooterModel: was not allOneCurrency..
Hiding unused Currency Column...
ScreenSize: java.awt.Dimension[width=1920,height=1080]
Main JTable heights....
Row Count: 372
RowHeight: 16
Intercell spacing: 1
Header height: 20
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 780
Scrollbar height: java.awt.Dimension[width=0,height=0] 0
Footer JTable heights....
Row Count: 19
RowHeight: 16
Intercell spacing: 1
Header height: 0
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 220
Scrollbar height: java.awt.Dimension[width=48,height=10] 10
Total frame height required: 780 + 220 + Intercells: 0 1 = 1002
Waiting for JFrame() to close... Wait number...: 1
Waiting for JFrame() to close... Wait number...: 2
In windowClosing ()
Waiting for JFrame() to close... Wait number...: 3
No longer waiting....
In ExportDataToFile ()
Opening file and writing 392 records
Oh no - File IO Error!
[Errno 13] Permission denied: 'C:\\CC_DATA\\PP\\extract_stock_balances2.csv'
<type 'exceptions.IOError'>
Path: C:\CC_DATA\PP\extract_stock_balances2.csv
!!! ERROR - NO FILE WRITTEN - SORRY! (WAS FILE OPEN, PERMISSIONS ETC?)
As file write failed, writing to console.....:
*Debut when it works *
Main JTable heights....
Row Count: 372
RowHeight: 16
Intercell spacing: 1
Header height: 20
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 780
Scrollbar height: java.awt.Dimension[width=0,height=0] 0
Footer JTable heights....
Row Count: 19
RowHeight: 16
Intercell spacing: 1
Header height: 0
Insets, Top/Bot: java.awt.Insets[top=0,left=0,bottom=1,right=0] 0 1
Total scrollpane height: 220
Scrollbar height: java.awt.Dimension[width=48,height=10] 10
Total frame height required: 780 + 220 + Intercells: 0 1 = 1002
Waiting for JFrame() to close... Wait number...: 1
Waiting for JFrame() to close... Wait number...: 2
In windowClosing ()
Waiting for JFrame() to close... Wait number...: 3
No longer waiting....
In ExportDataToFile ()
Opening file and writing 392 records
CSV file C:\Users\cycou\extract_stock_balances2.csv created, records
written, and file closed..
In saveParameters ()
Will try to save parameter file: ..\StuWareSoftSystems.dict
myParameters now contains...:
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
45 Posted by Stuart Beesley ... on 31 Oct, 2020 12:46 PM
StockGlance2020 version 5a. Finally found and fixed issue where it fails to create file on some Windows 10 installations (due to system folder access restrictions) .. Attached
46 Posted by Cyril Couten on 01 Nov, 2020 08:35 AM
It works but ... if overwrite the previous csv file, for any reason it will
not file it.
Just tried to run it on a new file, and re-run it : the new file was not
created.
_______________________________
Cyril COUTEN
Mobile : +33 6 18 40 80 65
47 Posted by Stuart Beesley ... on 02 Nov, 2020 08:24 PM
As discussed with Cyril, that problem is not reproducible, so we are assuming all good for now.
48 Posted by Stuart Beesley ... on 05 Nov, 2020 04:09 PM
StockGlance2020 version 5c - hopefully the final version. Added parameter to allow user not to round prices... (optional)
49 Posted by Stuart Beesley ... on 11 Nov, 2020 05:43 PM
Latest update (v5e)
Stuart Beesley (Mr Toolbox) closed this discussion on 11 Nov, 2020 05:45 PM.
Stuart Beesley (Mr Toolbox) re-opened this discussion on 27 Nov, 2020 10:31 AM
50 Posted by Stuart Beesley ... on 27 Nov, 2020 10:31 AM
All, if you use my StockGlance2020 script or others from me, please update them all to the latest version. ZIP of all attached.
Stuart Beesley (Mr Toolbox) closed this discussion on 27 Nov, 2020 10:31 AM.
Stuart Beesley (Mr Toolbox) re-opened this discussion on 14 Dec, 2020 05:44 PM
51 Posted by Stuart Beesley ... on 14 Dec, 2020 05:44 PM
Now updated to both a script and/or an Extension version. Extension version only works from MD2021.2012 onwards. Attached.
Stuart Beesley (Mr Toolbox) closed this discussion on 14 Dec, 2020 05:44 PM.
Stuart Beesley (Mr Toolbox) re-opened this discussion on 02 Jan, 2021 10:20 AM
52 Posted by Stuart Beesley ... on 02 Jan, 2021 10:20 AM
Latest scripts always here:
https://yogi1967.github.io/MoneydancePythonScripts/
Stuart Beesley (Mr Toolbox) closed this discussion on 02 Jan, 2021 10:20 AM.
Stuart Beesley (Mr Toolbox) re-opened this discussion on 15 Feb, 2021 10:41 PM
53 Posted by Stuart Beesley ... on 15 Feb, 2021 10:41 PM
To StockGlance2020 users. All my extract scripts have now been consolidated into a single script/extension called extract_data. The old individual scripts have been decommissioned.
https://yogi1967.github.io/MoneydancePythonScripts/
Stuart Beesley (Mr Toolbox) closed this discussion on 15 Feb, 2021 10:41 PM.