Is there a way to generate a 1 line per transaction report, including splits?

Captain Moderate's Avatar

Captain Moderate

22 Mar, 2019 05:08 PM

I'm realizing that I can use Excel tools a lot more efficiently that MD reports to extract summary information I need for tax preparations and other tasks. All I need is a big list of all transactions for a time period (e.g. last year), one line per transaction, in a form that is easy to translate to excel. The export function doesn't seem to achieve this (2 lines per transaction) but the Transactions or Transaction Filter reports seem perfect! -- except for one small item: Split transactions. With "show splits" off, I get a bunch of transactions with the category "split" -- not what I am looking for. With "show splits" on, each split transaction is provided first as a single transaction with no category and then as a list of the sub-transactions, with no account or date information. This effectively leads to a double-counting of all the split transactions.

Can anyone suggest a way around this? I have a work-around for the double-counting issue (deleting all lines with category "split"), but putting the account, date, and description information back into teach sub-split transaction is much more complicated.

Is there some other way to get from MD a report or tab-delimited file for a specified time period that shows all the data for each transaction -- including splits, one transaction per line?

  1. 1 Posted by Captain Moderat... on 22 Mar, 2019 05:10 PM

    Captain Moderate's Avatar

    Note: I'm open here to Excel-implemented pre-processing solutions as well as options within MoneyDance.

  2. 2 Posted by -Kevin N. on 22 Mar, 2019 05:19 PM

    -Kevin N.'s Avatar

    Hi Captain,

    Try the Find & Replace extension.
    Leave both of the 'split' options unchecked.
    Once you generated the desired data, the 'Copy' button can be used to C&P the data into Excel.

    -Kevin N. (not a member of MD support)

  3. 3 Posted by Captain Moderat... on 22 Mar, 2019 05:33 PM

    Captain Moderate's Avatar

    x

  4. 4 Posted by -Kevin N. on 22 Mar, 2019 07:40 PM

    -Kevin N.'s Avatar

    Hi Captain,

    MD 2017.10 (1706)
    MD 2019.2 (1860)
    Windows 10 Home (1809)
    Excel 2000

    I can not reproduce the behavior of the Date column in your screenshot using either version of MD listed above in Excel 2000.

    Have you tried selecting column B and formatting it as a Date?

    The Find & Replace Users Guide explains the various 'Amounts'.
    http://www.mennesoft.com/findandreplace/index.html#usage

    -Kevin N. (not a member of MD support)

  5. 5 Posted by Captain Moderat... on 22 Mar, 2019 08:04 PM

    Captain Moderate's Avatar

    Thanks for sticking with me on this, Kevin. The information on the Amounts is very helpful.

    The dates situation baffles me too. Yes, I have tried reformatting the cells Date, without success. I mis-reported above. The dates that appear with spaces are interpreted correctly, the dates with /s (e.g. "12/18/2018") have not been interpreted as anything but text.

    There are text conversion tools in Excel that can be used. Excel's text to columns tool, for example, splits "12/18/2018" into 3 columns of month, day and year. I also wonder if I could export the worksheet into a CSV file and re-import it, specifying the date format for column B. But ugh! There must be a more convenient way!

    I have encountered this inconsistent date interpretation after saving an MD file as a Tab-Delimited Variables file. The text file seems to consistently show the dates with "/" separators, but.... When imported into Excel, some dates are properly interpreted (showing with spaces in the Excel file) and some not (appearing with slashes). I have yet to find the clues to explain why. (This is frustrating, as commas sometimes appear in my notes for transactions, so I like using tab-delineated files instead of CSV.

    If you think of anything else, please let me know. I very much appreciate your help and guidance.

  6. 6 Posted by Captain Moderat... on 22 Mar, 2019 08:15 PM

    Captain Moderate's Avatar

    I just tried this workaround for fixing the date misinterpretation. Not satisfying, but it does work:
    1 - Use Find and Replace Extension to find all transactions desired.
    2 - Click the copy button
    3 - Open a new Excel file, and select paste.
    4 - Check for date interpretation problems. If they are present....
    5 - Save the next excel file as a CSV file.
    6 - Close the excel file (no need to save), and open a new, empty excel file. (This is so the import won't confuse the importing file with the previous file from which it was created.)
    7 - In excel, select File > Import, specify the CSV file, and specify that column B should be imported as a MDY date.

    Again, if you can come up with a less convoluted solution, I would love to know about it.

  7. 7 Posted by -Kevin N. on 22 Mar, 2019 08:24 PM

    -Kevin N.'s Avatar

    Hi Captain,

    Something that you could try is to Paste the data from the F&R extension into a simple word processor (e.g. Notepad) and see if there is some anomaly in the Date column data that may explain the odd results that you're getting in Excel.

    -Kevin N. (not a member of MD support)

  8. 8 Posted by Captain Moderat... on 22 Mar, 2019 08:42 PM

    Captain Moderate's Avatar

    Good suggestion.

     I think I figured it out. When I just paste the data into an Excel spreadsheet, Excel attempt to interpret the dates as Day/Month/Year, but the copied data from MD is in Month/Day/Year format. So Excel sees 12/01/2018 as 12 January 2018, when it is actually intended from MD as 1 December 2018. And when MD sends 12/18/2018 (meaning 18 December 2018), Excel sees a string which just cannot be a date (as there is no 18th month).

    So perhaps this challenge is a matter of default computer or Excel settings. I'll look into it. Thank you for all the help!!

  9. 9 Posted by -Kevin N. on 22 Mar, 2019 08:47 PM

    -Kevin N.'s Avatar

    Hi Captain,

    Please post back with your findings if you are able to resolve this. OK?

    I'm curious to find out what is the root cause.

    -Kevin N. (not a member of MD support)

  10. 10 Posted by -Kevin N. on 22 Mar, 2019 09:33 PM

    -Kevin N.'s Avatar

    Hi Captain,

    Something that you could try which is less convoluted...
    1 - Use Find and Replace Extension to find all transactions desired.
    2 - Click the copy button
    3 - Open a new Excel file, and select paste.
    4 - Check for date interpretation problems. If they are present....
    5 - Select column B.
    6 - Menu bar > Data > Text to Columns. Click Next, Click Next, in step 3 of the wizard, select MDY from the 'Date' drop-down list. Click Finish.

    -Kevin N. (not a member of MD support)

  11. 11 Posted by Captain Moderat... on 23 Mar, 2019 02:38 AM

    Captain Moderate's Avatar

    Yep! That is an improvement. I especially appreciate you mentioning the Text To Columns function, as I had explored that before and only found ways to generate 3 new columns from the single date column. On your instigation I have discovered the key: select Delimeted, but then no Delimiter!

    Thanks for the advice on both MD and Excel!

  12. Captain Moderate closed this discussion on 23 Mar, 2019 02:41 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