Report by Security (instead of by Account)

CanSaver's Avatar

CanSaver

15 Feb, 2021 09:50 PM

Now that I've got a bunch of data entered, I'm looking at the reporting capabilities. All the reports appear to group by Account: showing me data for each security within the account.

As I have several instances where I have the same security in more than one account, I want to be able to generate a report that gives me a totals for each holding regardless of what account it is in.

I sure hope this is possible as that's one of the reasons I want to use MD: to get a portfolio view of my holdings, vs a per account view. Try as I might, I can't figure out how to get a report by security.

  1. 1 Posted by Stuart Beesley ... on 15 Feb, 2021 10:01 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    Hi @CanSaver... Guess what:

    a) Yes, MD does NOT do this - it's about the one thing it doesn't do. It's a much requested feature

    b) BUT - You can download / install my extract_data extension:
    https://infinitekind.com/app/md/extensions/extract_data.mxt

    (https://yogi1967.github.io/MoneydancePythonScripts/)

    On the list of options is StockGlance2020... It will display to screen and/or extract to file.. It should roughly do what you want (perhaps)..

    Let me know?

    (not support, just a fellow user)

  2. 2 Posted by derekkent23 on 15 Feb, 2021 10:20 PM

    derekkent23's Avatar

    I am not support staff, just a user
    Hi CanSaver

    Stuart has already answered your question, but I have already typed this so I will post.

    Another user, Stuart, has created an extension called Extract Data which has been signed by Moneydance as safe. It includes a number of programs one called stockglance2020.
    Goto https://yogi1967.github.io/MoneydancePythonScripts/ and download extract_data (signed) by clicking on its link in the Signed Versions section. Then install the Extension extract_data.mxt by dragging and dropping it into the side bar list on the left. Click Install Extension.
    Now run the extension under EXTENSIONS – Extract Data and select stockglance2020 and click PROCEED. Make your selection and you are good to go.

    Hope this helps.

  3. 3 Posted by CanSaver on 15 Feb, 2021 10:36 PM

    CanSaver's Avatar

    Thanks to both @Stuart and @derekkent23.

    Wow. That really sucks. After moving one data file from Quicken over to MD, I was so impressed with it that I started a second data file and spent the last couple of days entering a bunch of investment history I was really excited about having all of my investment data in it and having access to easy to generate reports. After all that work to find out that it can't report by security is next to useless to me :(

    I tried Stuart's export tool and it doesn't look like it will give me what I want. One of the things I like about apps like Quicken and MD is the reporting capabilities that let you easily slice and dice, selecting time periods you want, categories, subcategories etc. But if I can't get a portfolio view, that's a non-starter for me.

    Do you know if they are looking into adding that capability? Seems like a pretty basic need and something that wouldn't be that hard for them to implement.

  4. 4 Posted by Stuart Beesley ... on 15 Feb, 2021 10:53 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    I think the IK developer needs to answer that one, I don’t know if they plan to do this (report by security) anytime soon.

    I wouldn’t give up just yet... if you want to slice / dice by time period then you probably need to be looking at transactions. You can report on txns in MD and perhaps export the data. Or again, in the same extract_data extension you can export investment txns to csv and slice and dice in excel.

    One last thought is that Mike Bray has written a report writer tool. It’s currently in beta but you can access here:
    https://infinitekind.tenderapp.com/discussions/general-questions/10...

    (Not support, just a fellow user)

  5. 5 Posted by Mike Bray on 16 Feb, 2021 12:28 PM

    Mike Bray 's Avatar

    Reporting is a weakness of MD, which led me to write a Report Writer and Data Extractor extension (have a look at my Wiki https://bitbucket.org/mikerb/moneydance-2019/wiki/Home). This might not be what you want but you can extract all of the security buys and sells into a spreadsheet and then create a report from this.

    If you are comfortable with SQL you can use a tool called Jasper Studio to create bespoke reports from MD. There is a sample report of Securities by Accounts. This could be changed to by Security.

    A generalised report writer is not a simple task and most people use the security by account report.

    Sorry, no silver bullet

    Mike(a fellow user)

  6. 6 Posted by CanSaver on 16 Feb, 2021 03:28 PM

    CanSaver's Avatar

    @Mike. Thanks for this. If I understand correctly, I'd need to install your report writer extension which would extract data that could be used by Jasper Studio? I don't know anything about Jasper Studio, but at a quick glance it looks like setting everything up would have a fairly high learning curve.

    My background is in IT, but I'm very rusty, not having programmed for the most part for 20+ years. Never got into SQL, but have worked with databases.

    It's really too bad that MD doesn't report better. It's everything I need except the reporting shortcomings in this one area, and without that, it's nothing :(.

    I've got an elaborate spreadsheet to track investments, but it's weak on reporting as well, so I was looking for a solution where all I need to do is enter my data and not have to fight with something to get the data out that I've entered. Quicken does an excellent job. Sadly, I may have to revert to it.

  7. 7 Posted by Stuart Beesley ... on 16 Feb, 2021 03:39 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    FYI. You don’t need to use Jasper with Mike’s extension. If you just want excel, it can output to that too.

  8. 8 Posted by CanSaver on 16 Feb, 2021 11:13 PM

    CanSaver's Avatar

    @Stuart and Mike, Thanks for all the help. I looked into the Report Writer but I really don't think MD is going to work for my investment tracking reporting.

    It's still a great tool for my other needs. But for investment tracking I think I'll move on to my Plan C.

  9. 9 Posted by dwg on 17 Feb, 2021 12:16 AM

    dwg's Avatar

    Moneydance Investment handling has always been a weak spot, not as weak as some products but still very much incomplete.

    It is as if Investment handling was started, with stock splits, Lot transactions etc implemented but it has never been finished. Reporting is even further behind, with some reports just being plain wrong.

    I cannot explain why the effort has not been put in, the weaknesses go back many years.

    Most of us use a combination of tools. For myself I maintain high level data in Moneydance but a spreadsheet for each security for the nitty gritty stuff.

  10. 10 Posted by Stuart Beesley ... on 17 Feb, 2021 07:32 AM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    I too maintain a SS but thats why I built the investment extract and also sg2020... I’m at a point where (now that I can get the cost basis right too) between these tools I don’t need my own SS now.... (just).

  11. 11 Posted by dwg on 17 Feb, 2021 07:50 AM

    dwg's Avatar

    Alas with Australian rules I have to use lot matching and many of my shares have returns of capital, one even has a couple of capital calls. I also have a couple of stapled securities which at a Macro level Moneydance could handle today (unlike many programs) but without Returns of Capital it would just mean bulk work - think 14 lots often 2 capital returns a year and the staple security includes two stocks.

  12. 12 Posted by Mike Bray on 17 Feb, 2021 07:50 AM

    Mike Bray 's Avatar

    Hi @CanSaver, you are correct in your assessment of Jasper Studio and my
    extension.  It is quite a learning curve.  If you know databases then
    SQL is not too much of a learn, it is basically picking data out of a 2
    dimensional table, i.e. a worksheet and there are loads of tutorials on
    the net.  The easier route is to use my extension to export the raw data
    to a spreadsheet.  If you export investment transactions and limit the
    export to buys and sells the data can be sorted into
    Security/Transaction type sequence and subtotalled on Transaction Type
    and then security. It will give you all of the buys and sells from which
    you can get your total holding.

    I agree on MD weakness in reporting.  At some stage I may extend the
    Report Writer to create a Reporting Designer, but that is in the future.
    Regards
    Mike

  13. 13 Posted by Stuart Beesley ... on 17 Feb, 2021 09:18 AM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    @dwg. I also need ROC and I worked with Sean a while ago to fix this. So I do a sell zero shares to generate the ROC. The cost basis for me seems correct. NOTE - there is one report where he didn’t fix the cost basis, but the portfolio screen is correct. Have you tried this more recently (over the last year)?

  14. 14 Posted by dwg on 17 Feb, 2021 10:13 AM

    dwg's Avatar

    The last time I tried it it was ok if I used average cost but I could not sell 0 shares if I was using Lot Matching. I was not surprised by this as I do not think Moneydance has the smarts to understand it has to split the RoC over the lots and how this needs to be done.

    I do use it with Average cost to give me the high level view that is correct.

  15. 15 Posted by Stuart Beesley ... on 17 Feb, 2021 10:25 AM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    I think you are, of course, correct on that avg cost point.

  16. System closed this discussion on 19 May, 2021 10:30 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