One of the core functionalities of a close/consolidation software solution is the ability to post and track journal entries. If you have been working with FCCS, you probably agree that creating journals manually in FCCS can be time consuming. The graphical interface that comes out-of-the-box works well for posting a single journal, and is easy for a user to navigate, as it allows for selection of members from each dimension where the journal should be posted. However, for companies that need many journals created in FCCS each month, this can be a painstaking process. Luckily, it’s possible to import a long list of journals simultaneously by importing a simple comma delimited text file.
Creating a Journal File
An important item to note with this process is the comma delimited text file must be formatted with a particular layout and syntax so that FCCS can process and import the journals. The layout required is depicted below:
Note: If journal work flow is enabled, you cannot import posted journals. You can only import working journals (W).
The first line of the file indicates the dimension order for the journal that is being loaded. You can define the dimensions in whatever order that is desired, just make sure the order of your members in the last two lines matches the !DIMENSION_ORDER in the first line. Please note that it is crucial that this text file is formatted exactly as shown above. For example, a missing exclamation point before DIMENSION_ORDER will cause a journal import error.
The second line of the file defines the Point-of-View, or POV of the journal. This line requires a single member from the Scenario, Year and Period dimensions. The third line defines properties of the journal, including journal title, status, and journal type. In this line, the journal can be added to a journal group, but this field is not required. The fourth line beginning with “!DESC=” is optional. In this line a text description for the journal can be added. The final two lines define the dimensionality of each line of the journal and the amount debited or credited. A description for each line of the journal entry can be added as an optional field.
Importing Journals
To import multiple journals in one text file, simply replicate the format above for as many journals that are required. Once journals are in this format, importing to FCCS is simple. First we are going to demonstrate how to import a correctly formatted Journal file and then show an example of how to create the file. To illustrate the import functionality, we will use the sample file below.
Click the Journals icon from the FCCS home page.
To manually create a journal, you could click Create, but we will import the text file shown above.
Click Actions → Import.
Click Browse and select the text file for import. Then click Import.
You should get a message stating: “Journal successfully imported.” If there was an error importing the journal, you will see: “Error: Failed to import journals.” If you get an error, you can click Download Log and read the error messages.
The imported journals will appear in the Point of View (POV) specified in the imported text file. You can see the POVs for the imported journals were: Actual, FY18, Jun and Actual, FY18, Jul.
We can now click on one of the imported journals and see that it exists in FCCS in the same format as if the journal had been manually created.
The journal can now be submitted, approved and posted. Notice that in the screenshot above, aliases appear instead of member names. In the example ImportJournals.txt file, we used member names for all the dimensions, but aliases can be used as an alternative. Either way, once the journal is in FCCS the aliases will be displayed.
Formatting Tips/Tricks for Journal Files
As mentioned earlier, to use this import journals functionality the journals must be in the correct file format. It does not matter how you convert your existing journals into the correct format, but that the journals are in the exact format laid out above…FCCS is very particular about this format! While you could leverage some fancy Excel macros or VBA, the process outlined below is a pretty simple method to create the journal file.
Below is a sample journal template file that we created in Excel. Note that members from each dimension are specified, and Excel formulas are utilized to convert the dimension selections into the correct format for journal input. Once your Excel formulas or VBA scripts are prepared, you can reuse them by ensuring that your journal template file is consistently in the same format.
We created the required lines for the sample ImportJournals text file by using a series of concatenation formulas:
After creating the lines needed for the import file, we made sure they were in the correct order. The order of the lines is important. For example, the !DIMENSION_ORDER line needs to be above the !POV line.
Now we can simply paste all of the journals into a text editor, such as Text Pad or Notepad++, and the journal file is ready for import. The above example shows importing two journals at the same time, but you can import as many journals as you want in the same file.
Setting up the process to convert journals into the correct format can be time consuming, but for companies that need to create many journals in FCCS, importing a long list of journals will be quicker than creating each of the journals manually in FCCS. Hopefully by a little extra work up front will save you a lot of time in the long run!
Stay tuned for more blogs on FCCS functionality!
