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

Showing page 2 out of 2. View the first page

  1. 31 Posted by Stuart Beesley ... on 22 Oct, 2020 09:43 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    Hi, the link below is fine, but note it’s the beta version (as it’s in dev). It’s fine to use this - it works - but great to have ’testers’ providing updates. I plan to load (working) functional updates here before the main release. You should see 4d now. I think I’ve fixed the non-ascii character issue. Let me know?

    Beta test link:
    https://github.com/yogi1967/MoneyDancePythonScripts/blob/dev/StockGlance2020.py <https://github.com/yogi1967/MoneyDancePythonScripts/blob/dev/StockGlance2020.py>

    SCB

  2. 32 Posted by derekkent23 on 22 Oct, 2020 09:54 AM

    derekkent23's Avatar

    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

  3. 33 Posted by Stuart Beesley ... on 22 Oct, 2020 10:29 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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?

  4. 34 Posted by Stuart Beesley ... on 22 Oct, 2020 01:00 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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

  5. 35 Posted by Cyril Couten on 23 Oct, 2020 07:35 AM

    Cyril Couten's Avatar

    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

  6. 36 Posted by Stuart Beesley ... on 23 Oct, 2020 07:41 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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

  7. 37 Posted by Cyril Couten on 23 Oct, 2020 07:54 AM

    Cyril Couten's Avatar

    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

  8. 38 Posted by Stuart Beesley ... on 23 Oct, 2020 07:59 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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

  9. 39 Posted by Cyril Couten on 23 Oct, 2020 08:10 AM

    Cyril Couten's Avatar

    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

  10. 40 Posted by Stuart Beesley ... on 23 Oct, 2020 08:17 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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)

  11. 41 Posted by Cyril Couten on 23 Oct, 2020 08:22 AM

    Cyril Couten's Avatar

    sure : [email blocked]
    _______________________________
    Cyril COUTEN
    Mobile : +33 6 18 40 80 65

  12. 42 Posted by Stuart Beesley ... on 23 Oct, 2020 05:07 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    V4e (beta) of script with many enhancements

  13. 43 Posted by Stuart Beesley ... on 26 Oct, 2020 09:41 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    For completeness version 5 of this script released - attached. The latest can always be found here:
    https://github.com/TheInfiniteKind/moneydance_open/tree/main/python_scripts

    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.

  14. 44 Posted by Cyril Couten on 26 Oct, 2020 06:31 PM

    Cyril Couten's Avatar

    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

  15. 45 Posted by Stuart Beesley ... on 31 Oct, 2020 12:46 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    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

  16. 46 Posted by Cyril Couten on 01 Nov, 2020 08:35 AM

    Cyril Couten's Avatar

    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

  17. 47 Posted by Stuart Beesley ... on 02 Nov, 2020 08:24 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    As discussed with Cyril, that problem is not reproducible, so we are assuming all good for now.

  18. 48 Posted by Stuart Beesley ... on 05 Nov, 2020 04:09 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    StockGlance2020 version 5c - hopefully the final version. Added parameter to allow user not to round prices... (optional)

  19. 49 Posted by Stuart Beesley ... on 11 Nov, 2020 05:43 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    Latest update (v5e)

  20. Stuart Beesley - JUST A FELLOW USER closed this discussion on 11 Nov, 2020 05:45 PM.

  21. Stuart Beesley - JUST A FELLOW USER re-opened this discussion on 27 Nov, 2020 10:31 AM

  22. 50 Posted by Stuart Beesley ... on 27 Nov, 2020 10:31 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    All, if you use my StockGlance2020 script or others from me, please update them all to the latest version. ZIP of all attached.

  23. Stuart Beesley - JUST A FELLOW USER closed this discussion on 27 Nov, 2020 10:31 AM.

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