Hey folks,
in this blog post I will share most likely a hidden gem of how you can quite easily enter data from an excel file to a manually created bank statement.
All you need for this will be the Manage Bank Statements application.
Let's say a colleague sends us an excel file containing bank statement line items for a bank statement which we should import.
We have this excel which contains two bank statement line items. So let's select these two statement line items which contain the data for (in that order) External Transaction, Value Date, Amount, Currency and the Memo Line.
Now simply select all your bank statement line items, and copy them by using the shortcut ctrl+C (Windows) or command+C (Mac).
Now switch to the browser and open the already prepared bank statement in the Manage Bank Statement application.
Click on the header of the Bank Statement Items table as shown in the screenshot above. You will see that the system now has focused the table with a thin blue line indicating what has been selected.
And now the magic and fun part happens, all you need to do is paste the data by using the shortcut ctrl+V (Windows) or command+V (Mac).
Is there anything else you need to consider? Yes, so basically the order of the columns in your excel will be copied 1:1 to whatever order you have defined in your Manage Bank Statement application. Here we propose to really create your own variant. It's just a one-time effort as you will always be able to use that variant or even set it as default variant.
First of all you select whatever columns are relevant for your business or in other words select those columns that you want to populate with your excel files. So let's click on the settings wheel above the Bank Statement Items table:
In this case I deselected the Line Item Number and the Manual Transaction. If you want to keep a certain order of your line items you should of course keep the Line Item Number, but then keep in mind to also provide or have that data in your excel.
In this example I am using external transactions so I add that column to my table.
Remember, the sequence of your columns in your Bank Statement Items table has to match the sequence of the data from your excel. So in this example I move the External Transaction up by hovering over it with my mouse and changing the position in the list.
Click on “OK” once you rearranged your columns and double check if the sequence on the UI is aligned to your excel.
Now we can create the variant, so that these columns are always ordered like that whenever we enter this application.
Give it a name and select “Set as Defaut”. In this example I pretended that my name is John Miller, so I named it John Miller. Obviously you do not have to use your name, you can also call it "ImportExcel" or whatever you wish.
Now you can whenever you create a new bank statement directly copy and paste the line items data from excel.
So for the conclusion:
There are two options how you can import data from excel. Either you use this lean way of simply copying the data right into the application or by going the "old fashioned" way which means more manual input from you:
- Click on „Generate Template“ button
- Fill the information (most likely you copy and paste it from a different excel file) into the generated template
- Save the template excel with your data on your machine
- Click on „Import“ button in the application
- You will now see a warning dialog, that all data already entered will be deleted, you click on “Yes”
- Now click on „Upload“ (or drag the file from your machine)
- Select the file and click on „Ok/Open” (drop the file into the dialog)
If you are interested in further shortcuts you can use on the Manage Bank Statements or the Manage Processing Rules - For Bank Statements or any other Fiori Elements application, you can have a look into this blog post.
Feel free to leave any comments or question below, I am happy to help you.
Best,
Edrilan Berisha
SAP S/4HANA Cloud Financials Development
- SAP Managed Tags:
5 Comments
