How to produce a reconciliation report for financial documents.

Financial documents are easy enough to transport when the data has been cleansed and formatted properly. We talk about that elsewhere on the site. Once the documents are loaded, the next step is to reconcile the documents. This report makes it easy to see how each document got into SAP by giving the SAP document number that relates to the legacy document.

The reconciliation

Reconciliation seldom means more than spot-checking a few of the documents and then comparing the reconciliation accounts against the balances in the legacy system. With this properly formatted report the FI/CO group can see line-by-line how each document was transported to SAP and it even has amounts, document types and even document numbers!

Excel as a reporting tool

Any FICO functional resource worth his salt is already comfortable with Excel. Handling data in Excel is ill-advised and can lead to catastrophic problems with features like auto-removal of leading zeros in numbers, converting dates from one country's format to another and conversion of what appears to be long numeric values into scientific notation. Reporting in Excel is usually welcome in the business world and analysts almost always prefer it to raw text.

Getting the data into Excel

Here are 23 easy steps to bring the log data and the source data in a meaningful way for the purpose of financial document reconciliation. This is the way that I have been handing off reports to the FI/CO team for 8 years with great success.

Step 1. Start by opening the batch log in SAP. From transaction SM35 click on the block next to your batch job and then clicking on the Log button. This will get us into the logs specific for this job. It isn't enough to double-click on the job and then click the Log tab; this will not lead to a screen with the print option--a necessary part of this process.

Step 2. Double-click the log for this batch job. I did a tester where I allowed the first few screens of the batch to execute. I didn't allow any documents to be created when I ran it in foreground, so there are no documents in the first log--just a few inconsequential lines.

Step 3. Click on the print icon. Don't worry! We will not need to print the document which is probably big enough to choke your printer for an hour.

Step 4. Tell SAP that you want to save the log as a file. This will get us the raw text from SAP in a malleable format. Since SAP doesn't have a way to export log files from SM35, we have to rely on this step.

Step 5. Select "In the clipboard" as the place to save the log. This may take a long while depending on how long the log is. If the log is too long, you may be required to export to text file and then import to Excel. This step is unnecessary on modern computer, so I leave it out here.

Step 6. Open a new Excel spreadsheet. Click on the cell B1 in Sheet2. It's very important as the formula from a latter step will depend on the pasted data being in this cell.

Step 7. Paste the data into the Excel spreadsheet. It will look something like this. This garbled mess of dashes and pipes is what SAP exports to by default. We have to clean it up to make it usable.

Step 8. Click on the Data tab and then on Text to Columns.

Step 9. SAP manages to slip a pipe (the vertical line on your keyboard above the Enter key) between each field when it exports. We'll use this as our delimiter.

Step 10. Here is where we tell Excel what the delimiter to use is. Click on the Other check box and type a pipe in the field.

Step 11. This is a tricky step. We want the data to be added to Excel as text. If we don't use text, then a lot of data can be lost. We want to preserve the leading zeros and we want to prevent Excel from converting numbers that look like dates into dates. In the bottom of this window, slide the preview slider all the way to the right so that the rightmost column is visible. Hold the shift key and then click the last column so that all of the columns are selected. You'll see them turn black. When they're all selected click on the Text radio button under Data column format. Click the Finish button.

Step 12. The data will spread out and look like this. Click on the B4 cell and paste in the formula below. Then press Enter and select the cell again. Double click on the small square at the bottom-right corner of the B4 cell.

=IF(OR(TRIM(J4)="E",TRIM(L4)="312",TRIM(L4)="349"),TRIM(F4),"")

Step 13. Now the data should look like this. Only the rows with significant information will have a number representing the line item in the source file. We will relate these numbers to the source file later.

Step 14. Bring Sheet1 into focus. Click on the Data tab and then on From Text. Select the source file used to create the batch and start the import process.

Step 15. My source file is tab-delimited, but the next three steps should be customized for your file. Most likely it is tab-delimited, but sometimes it will be delimited by colons, semicolons or pipes.

Step 16. Again, I have chosen settings for the most common source file. Please ensure that you use the correct settings for your source file here.

Step 17. Here again is the tricky bit where we want to import everything as text. Make sure that everything here is imported as text.

Step 18. The text must be imported into C1. The formula that relates the batch log in Sheet2 to the source file in Sheet1 will not work correctly if the data is place in any other cell.

Step 19. Type "Messages" in cell A1 and "RecNo" in cell B1. These are the headers that will describe the information from the batch log and indicate what row from the data we are working with. Type '1 in cell B2. This is a text-version of the number 1. We want it to be text just like the text-version numbers from Sheet2.

Step 20. Type this formula in cell B3. This will produce the text-version value of the 1 more than the cell above.

=Trim(B2+1)

Step 21. Double-click this small square in order to expand and reproduce this cell all the way to the bottom of the source data. This will put a reference number on each record from the source file.

Step 22. Type this formula into cell A2 and then double-click the small square in the bottom-right corner to expand and duplicate the formula all the way to the bottom row of the source file.

=vlookup(B2,Sheet2!B:D,3,0)

Step 23. Viola! The data is ready to hand off to the FI/CO team for reconciliation. Don't bother applying any pivot tables or adding sums to the bottom of the data; the functional consultants know what they're doing. Simple clean-up like auto-sizing the width of the A column is optional, but nice. Just double-click the line between the A and the B to automatically adjust the width of column A. Save the file with a meaningful name like SummaryTurkey.xls and the task is complete.