Virtual Automation Bootcamp with StudioX - The Reconciliation of Two Reports

Learn Academy Feedback
,
1

This automation has given you a glimpse into a Finance and Accounting related automation. Regardless of working or not in the field, you might need to manipulate or compile data for various reasons: reporting, budgeting, invoicing, data analysis, these only being some examples.

Would you consider automating such tasks? Think of a use case of your own, would automation help you complete it faster?

For any technical question , please create a new topic in the StudioX category here .

2

Hello @Bianca.Dragu I have automated this process for finance company.
It saves their times as they took 8-10 hrs to to complete 1 week reconciliation and after automation our robot did it in 15 min(For a month it takes 25-30min).

5 Likes
3

That is a very cool example! Thank you for sharing :smiley:

4

Did you use the exercise template ?

Nice experience you had

5

Our process is shown by Eudureka for example :blush:

6

As French, I took a different solution for the month selection because with a French Excel the names of the months are in French and not in English as in the example.
So my solution is more international :wink:

i made a two column table, in the Scratchpad sheet with the month nr on the first column and the month name in english in the second, then a simple vlookup finds me the correct month name.

Pleasant exercise to do

7

Hi,
Had similar experience with a Swedish Excel installed…

But there is a solution:
=TEXT(A1*29; "[$-0809]MMMM")
Where “$-0809” is the languageID for english.
https://www.myonlinetraininghub.com/excel-dates-displayed-in-different-languages
//Markus

2 Likes
8

A challenging exercise, sometimes frustrating but I guess it comes with the territory, but overall I had fun putting it together, iterate it, and running it.

9

Pretty straight forward. However, I still do not understand the activity in which I file becomes a template. Each time I try to create the template, it does not work. I keep the file in its original state and it works. I need more understanding.

1 Like
10

This was challenging and took more than a few hours. I was having a hard time converting the reconciliation file to text. Somewhat frustrating.

11

It works for me but I don’t really understand what happens exactly when I use template instead of the original file. Can anyone help with this?

12

I’m Brasilian and have problem with the names of the months too.
@Markus_Anding your solution do not work for me!
I copy @Jean-Marc_Nahon solution… :grinning:

13

Hi,
For anyone using v.2020 studioX, check the following screenshot tips:

For this task:

Screenshot 2021-01-11 224036
Screenshot 2021-01-11 2240361105Ă—520 31 KB

Do not use:

Screenshot 2021-01-11 223620
Screenshot 2021-01-11 2236201056Ă—619 19.7 KB

Rather use:

Screenshot 2021-01-11 223749
Screenshot 2021-01-11 2237491063Ă—694 25.1 KB

Good luck

1 Like
14

Oh, that´s what I was looking for! Thanks for sharing it! I solve Czech language.

15

Hi Kiss.
Remember, before executing the bot for the first time, if the bot doesn’t have a model file with the same structure as a file that will be manipulated, the bot has no way of knowing the name of the columns and sheets prior to the moment of development. In this situation you, as developer ,would need to use indexes to name sheet and columns, which makes the work less intuitive.

1 Like
16

I managed to develop the bot, but I came across a problem in excel that I still don’t know how to solve. The “Write Cell” function that adds the value of the invoices is recording the correct formula, however when the BOT executes the macroexecution of the excel formula, excel returns the referential error below:

image
image810Ă—363 22.5 KB

There is no execution error, but the reconciliation value at the end is wrong because Excel tries to make calculations between Value and String.

FY: To work, I need to hit the enter key on the cell H2.

Any suggestion?

17

I see the value of this type of automation via bot. I had to find how to turn off the protected sheet function in Excel so that the bot had permission to use these files.

It took me several hours to complete this exercise with multiple runs to get it right.

18

Great Exercise!! It has the potential to replace some of our reconciliation work that we perform (usually takes multiples hours). However, I have a few questions:

  1. When my automation completed, there were some discrepancies within the reconciliation. For Ex. DE763212-7 has -9960 reconciliation balance. I understand this is an example but I wanted to check whether this value is correct? If not, then it means my process might have some flaws that I need to look into :slight_smile:
  2. I used the VLOOKUP function within the Scratchpad to iterate through the months. Can someone clarify within the solution why did we multiple the month number with 29? How did you know that multiplying with 29 will give up the right month?

Thanks!

19

I like this project but it was a bit frustrating. I’m starting to see a common theme in all of these projects in that they don’t seem to work for quite a few folks; even when using the solution. It’s a bit confusing. It took me a few hours trying to work through a solution and even using the solution provided I was not able to get it to work. There was an issue with several Excel files that opened and remained open during the run and it finally errored out with a file in use error. I’ll go through it again later, too exhausted to try again right now. :woozy_face: :smiley: I’m really new at all of this and trying not to get frustrated - here’s hoping I can get one of these practice automations to work. :nerd_face:

20
  1. It uses the fact, that Excel stores the dates as numeric values, 1 being January 1st, 1900. So if you type in 29 and format it as a date, it will be January 29th, 1900. Multiplying 29 by 2 would give 58, which formatted as a date is 27th February, 1900. So if 29 is multiplied by 1 to 12, you’ll get 12 different dates each in the following month.
    The TEXT([NumberOfMonth]*29, “mmmm”) formula formats the generated dates as the full month name. yyyymmdd would be the usual 20210901 like date, mmm would give the short jan, feb etc. names, and mmmm shows the complete month name. So it’s still a date, but with the specific format only the full month name is shown.

BR,
Daniel

1 Like