📨Journal Entry with Excel

Một công việc kế toán viên thường phải làm thủ công là lấy số liệu từ một hệ thống khác và nhập vào sổ sách kế toán. Ví dụ, công ty bạn có một hệ thống bán hàng online, mỗi ngày bạn phải trích xuất số liệu bán hàng ra file Excel và hạch toán thủ công từng giao dịch.

Một số phần mềm kế toán cho phép import trực tiếp số liệu từ file Excel vào sổ cái. Tuy nhiên, do đặc thù khác nhau của mỗi công ty, định dạng số liệu trong file Excel rất khác nhau từ tên cho đến thứ tự cột dữ liệu. Hay tệ hơn, bạn chỉ có các số liệu trung gian, cần phải được xử lý trước khi import.

KNOWINS có thể giúp giải quyết tất cả các rắc rối này, giúp bạn tiết kiệm thời gian và tâm trí xử lý các công việc khác quan trọng hơn.

Let's do it.

Export Data

Excel Parameter

Collect Data

Journal Entry Import Control

KNOWINS allows users to place controls on a presentation sheet. Such controls can be used to collect data showing on screen to a journal entry import, drill-down into further data details, etc…

You create a user control in 2 simple steps:

• Select a cell to be a user control and insert a cell comment

• Apply a user control function into the comment

Right click on cell I5 of the presentation sheet and select Edit Comment

Then add the following comment:

IMPORT(JNE, data, transactionref, description, journaltype, period, transactiondate,
       accountcode, accountname, amount, d_c)

User Control Function: Import

Refer to the Report Design Reference chapter for a full description.

Syntax.

Import(form, data_tag, field1, [field2]...)

Parameters

• form: A form that receives data, such as JNE for Journal Entry

• data_tag: Placeholder cell for data. The data should have been already available in a certain cell. Data tag links that data to an input of this function by matching the cell’s comment with data_tag value. For example, if data is in cell A1, and cell A1 has MyData as its comment, then the data_tag should be MyData.

• Fields…: The list of field names. Data has to be a list of values separated with each other by the character | (straight slash). Each of them are mapped to the field names in order. When it reaches the last field in the list, it would roll over to the first field,

Description

This functions calls a form and feed it with the collected data:

Example

Import(JNE, data, period, transactiondate, accountcode, amount)

This user function calls JNE form, which is Journal Entry, and collects data from a cell having data as a value of its comment. This data would be transformed into a data table with 4 column period, transactiondate, accountcode, amount.

Let’s use cell A1 as a placeholder cell for data. Right click on cell A1 and add this comment:

To collect data on screen, we are going to use the excel function TEXTJOIN. Enter this formula =TEXTJOIN("|",FALSE,B10:J15) to the cell A1. Note that, the data range is from B10 to J15, which would be automatically expanded to the full data range when running a report.

Excel Function: TEXTJOIN

Please refer to TEXTJOIN function - Office Support for detailed instructions

Syntax.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Parameters

• delimiter: A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text. Note: Always use this character | (straight slash) for our reports.

• ignore_empty: If TRUE, ignores empty cells.

• text1: Text item to be joined. A text string, or array of strings, such as a range of cells,

• [text2, ...]: Additional text items to be joined.,

Description

This function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

We are going to test this report. Click on the button the journal entry form should pops up showing the first of 16 closing journal lines. Click on the grid view icon to see all journal lines.

Run and Test

Congratulations! You have made really good progress so far.