Excel Accounting Tool (EAT) - User Manual and Instructions

Version v5.2 - Target: Release (EAT) - Built on: 6/16/2024 3:36:41 PM - Released on: 6/16/2024 3:37:16 PM

Home


Table of Contents


clapse    expand

expand A.How to use the document
1.Navigation
2.Search
3.Samples and Figures
4.Version Icons
expand B.About EAT?
1.What is EAT?
2.System requirements
3.Purposes
4.How it works
5.Benefits
6.License Agreement
expand C.How to acquire EAT
1.Ownership Time-line
2.Trial Version
3.Product Version
expand D.Install EAT
1.Unzip file
2.Pin to Taskbar
expand E.Graphic user interface
1.Purpose
2.The Models box
3.Instruction page
4.Model page
5.Manual page
6.Auto page
7.Run page
8.Log page
9.Rpt page
10.Archive page
11.Backup page
12.Clean page
13.Doc page
14.Support page
15.Upgrade page
16.About page
expand F.Work flow chart
1.The Chart
expand G.The model
1.The model file
2.The input sheet files
3.The bank CSV files
expand H.The model commands
1.scan
2.scan
3.scanBank
4.setDebugMode
5.ExternalCsvMapFile
6.createOneBook
7.createAccountHierarchy
8.cutOffDate
9.useStyleTemplates
10.createNextCycle
11.numberOfFilesToKeep
expand I.The input sheets
1.General
2.Command dependencies
3.Arithmetic Expression
4.Conditional Expression
expand J.The input commands
1.type
2.account
3.AddHeader
4.AddHeader
5.addHeader
6.VirtualHeader
7.add
8.add
9.add
10.AddRecord
11.Repeat
12.RepeatRecord
13.AddByLink
14.groupSubtotalForChart
15.SetInitValue
16.setKeyField
17.setKeyField
18.setGroupField
19.changeOrderIndex
20.changeOrderIndex
21.discardRowWhen
22.hideTheField
23.startingColumnOfContenT
24.startingRowOfContent
25.writeIndexRow
26.writeNameRow
27.writeNumberRow
28.writeInitValueRow
29.writeNavigationColumn
30.startColumnForGSC
31.setFirstRowColor
32.copyAccount
33.copyAccount
34.copyAccount
35.joinAccounts
36.prepareCycle
37.EatDll
38.EatDll
39.EatDll
expand K.Credit card accounts
1.General
2.Supported Bank Websites
3.CSV Map file
4.Bank file names
expand L.Link a field
1.General
2.The GetFrom Link Command
3.The first scenario
4.The second scenario
5.Link Method 1
6.Link Method 2
7.Link Method 3
8.Link on 'good here'
9.Examples
expand M.Analyze the reports
1.General
2.The total
3.The style
4.The Archive
5.Web Page Report
expand N.Analyze the data
1.Group Transactions
2.Filter & Group Transactions
expand O.Prepare next cycle
1.General
expand P.Create one book
1.General
expand Q.Shorten the cycles
1.General
2.Step 1
3.Step 2
expand R.Back up
1.Purpose
2.Backup a Must
3.How to
4.Method
expand S.Update EAT
1.Download zip File
2.Unzip File
3.License File
expand T.Definitions & Practices
1.Color Names
2.Names Case
3.Word Search
expand U.History of changes
1.The changes
expand V.History of Epplus Changes
1.The changes

Presentation of Contents




A. How to use the document


1. Navigation

This document is well structured and easy to navigate through. Please navigate up and down through left panel Tabel of Contents.

2. Search

hit both 'Ctrl' + 'f', and type in the word and hit 'Enter' to search for. hit 'F3' to search for next occurrence.

3. Samples and Figures

Samples and figures provided in this document are not necessarily coherently in binding in design. It is up to the user to make his or her 'story' complete by applying the principles.

4. Version Icons

Icons like these     ... indicate the associated section is a new or revised section with a new version number.


B. About EAT?


1. What is EAT?

EAT is a software tool. It was built to run on MS Windows. The head program is called Eat Manager in Windows GUI. It takes input Excel file(s) and produces an output files either in Excel spreadsheet, text or in HTML, while the Excel Sheets are industrial standard and the most popular software tool in data processing worldwide. The tool is very convenient and versatile.

2. System requirements

It requires :
  1. a Windows 7, 8, 10 or 11 OS system
  2. a Windows Microsoft.NET Framework 4.6.1
  3. an Internet browser
  4. a Microsoft Office Excel program, Google Sheets On-line or other equivalents
  5. a license from Teknewell
  6. The download may be blocked by your browser. Choose 'Keep' or 'Keep Anyway'.
  7. The zip file may be blocked by Windows, goto the zip file's property, unblock it, and then extract to your desired disk location.
  8. The program executable may be blocked by Windows or Virus Protection. Choose 'Run' or 'Run Anyway' or Excludsion.

3. Purposes

The output file contains accounting reports of financial accounts primary and summary for families and organizations.

4. How it works

A family member or company employees add basic account ledger records. These records are basic financial records and are minimum in number and the least time consuming. Once the ledger Excel files are collected at the directories defined in parameter file, the Eat Manager can run EAT command line tool and produce the report Excel file flashing easy.

5. Benefits

  1. EAT is a economic, cost-effective to own and convenient and safe to operate. Once EAT is installed and a accounting model is configured for business, there is no need to change the model often. The model is also easy to adapt if such a need arises. The only task requiring a user to take routinely is to enter ledger transactions according to business activities and create reports for analysis of account health (audit) and purposes.
  2. No fancy installation process to run and no host PC environmental variables to set up. Just unzip the downloaded zip file to be ready for production.
  3. EAT Tool makes family and company financial data available right away for college financial aid or other applications.
  4. EAT Tool allows a parent or a manager forecast or predict next month's account balances by utilizing previous month's average data, pattern and unusual transactions.
  5. By changing credit card's due date to 7th of each month and setting up auto payment on statement balance, you basically are left nothing to do except making checking account fund available for next month by transferring fund from savings to checking on 5th of each month right before the due date, and except downloading CSV statement files and pulling 2 or 3 amounts from banks and updating the ledger records and in the meantime auditing and monitoring the account's health.
  6. This bill-pay activity only happens once in 1 day of a month and saves you a lot of time and allows you to attend to other family activities. This EAT Tool does not electronically and automatically pull the data from banks. You must do the small amount of work to see if your accounts match up your bank accounts. Knowing your accounts are healthy makes you sleep tight at night.
  7. EAT tool is for managers to create financial reports of all accounts. One license is good for one manager of an organization unless there is a need to divide business to acquire multiple licenses.
  8. Multiple Excel files enable company to have multiple employees working from different departments to contribute adding ledger records.
  9. Eat does not transmit user accounting data across the Internet. So it is safe to operate and user financial data is by no means to be leaked away to create security worries. Eat may use Internet to verify license validity though.

6. License Agreement

Users should have at least once read the User Purchase and License Agreement at time of using the Trial version. Subsequent decision to purchase the software indicates user agrees without preservation the provisions set forth in the document of User Purchase and License Agreement.

Teknewell is a registered legal company in Massachusetts USA, The product tools are safe. Please also read the readme.txt if available.
Here you can access User Purchase and License Agreement


C. How to acquire EAT


1. Ownership Time-line


2. Trial Version

To download a trial version, click a menu item. The trial version only lasts for a few months. Follow on-screen instructions, and you should have downloaded the file through the browser. The zip file is like the pattern 'eat*.zip'. The browser, virus defender and Windows OS may block the zip file and its exe files as suspects. User should choose trust, 'run anyway' or exclude from protections.

3. Product Version

If you felt EAT is a useful tool and can help you manage your family's or company's financial accounts and analysis, and decided to use, then click a menu item to purchase the real product. The process is similar to that of the trial version.


D. Install EAT


1. Unzip file

Unzip the zip file, normally in 'download' directory. Save the files into a directory in which you will work in the future. This directory is called Installation Full Path (IFP).

2. Pin to Taskbar

The tool's main executable is 'EatUI.exe'. Making a Run Icon in the task bar will help to run the program easily. Goto IFP, right click on 'EatUi.exe', then click on 'Pin to Taskbar', and a new Icon will show up on the task bar. Click the icon to run the program.


E. Graphic user interface


1. Purpose

This latest program 'EatUi.exe' encompasses all functions of the tool. Because it is a Windows Form program, it is easy and direct to carry out tasks important to businesses. It serves as a overall manager for user. The program was developed at a later stage and replaces the previous Console applications. It is also straight forward or self explanatory to navigate around the interfaces. In this document we will not provide more details of each functions. User should be able to familiarize one self without issue. If not, send a support ticket to get a answer.

2. The Models box

On the left is the model box. The box lists all model files prefixed by 'model'. All model files reside in the directory 'model'. Refer Model in later chapter regarding commands available in Model file. When a model is selected, it becomes active and all subsequent functions are carried out for the model.

3. Instruction page

On the right is the box for all other pages. Instruction page contains an editing box to allow user to record instructional notes.

4. Model page

Model page presents the parameters of all commands in the Model file. User could alter the parameters to fit for a particular run.

5. Manual page

Manual page consists the locations and all manual input files. The word 'manual' indicates all commands are entered by user into input sheets of Excel files specified here.

6. Auto page

Auto page consists the locations and all bank CSV input files. User does not need to modify the CSV files. They can directly participate in the 'run'.

7. Run page

Run page is the dividing page. On its left are the pages of preparation before the execution. On its right are the pages of results of the execution. This page allows user to click a button to produce the reports. There are few argumental UI choices user could consider before the 'run'.

8. Log page

Log page lists all log files of the executions.

9. Rpt page

Rpt page lists all resulting report files which includes XLSX and HTML file types.

10. Archive page

Archive page lists all archived files. The archived files are not generated by the execution, and not subject to being deleted by the 'clean' operation. On the Log and Rpt pages there is a button 'Archive' user could click to archive a particular file. We recommned user to archive at least 2 files for each cycle. One is the report file that has beed reconciled, the other the result report file that has been forecasted. Archive is a great way to keep history files at bay for future references.

11. Backup page

Backup page provides a way to backup the tool and user's family or business accounting data to a location deemed safe and can be recovered if a break-down took place unexpectively. We recommend user to backup the files when significant additions of data have taken place, or at least after each cycle's forecast has been done successfully.

12. Clean page

Clean page allows user to delete files in the directory 'log' and 'rpt'. These files are created after each execution and cumulatively increasing in number. We recommend user to delete the files every half year and also consider a reasonal minimum number of files to be kept for refernece in immediate future.

13. Doc page

Doc page provides few URL link buttons for easy access of on-line User Manual and others.

14. Support page

Support page provides a URL link button to access the on-line support page.

15. Upgrade page

Upgrade page provides a URL link button to access the on-line upgrade page. Upgrade is free within the term of a valid license.

16. About page

About page describes informational detail about our company Teknewell Engineering.


F. Work flow chart


1. The Chart

Follow the below work-flow chart which resembles a family monthly financial management activities.


G. The model


1. The model file

Under the directory 'model', user creates model files prefixed with 'model' like model-*.xlsx. In this file you can specify the location of input files. There are few commands available. See following table of commands. Note user can edit Excel file in Microsoft Office Excel, LibreOffice Calc or a Google Spreadsheet.

2. The input sheet files

Under the directory specified by command 'scan' in model file, you can supply Input sheet files prefixed with 'eat' like eat-*.xlsx. The commands are described in following chapter.

3. The bank CSV files

Under the directory specified by command 'scanBank' in model file, you can supply Credit Card Bank CSV statement files. Further detail will be provided in a later chapter.


H. The model commands


1. scan

Fields:
ColumnWhatExample
ACommandNamescan
BDirectorysale
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Directory' is used to specify the directory where the input files are located.
Sample: 'scan':
auto


2. scan

Fields:
ColumnWhatExample
ACommandNamescan
BDirectoryservice
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Directory' is used to specify the directory where the input files are located.
Sample: 'scan':
auto

2nd sample

3. scanBank

Fields:
ColumnWhatExample
ACommandNamescanBank
BDirectorybank-23-1st-quater
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Directory' is used to specify the directory where the bank CSV statement files are located.
Sample: 'scanBank':
auto


4. setDebugMode

Fields:
ColumnWhatExample
ACommandNamesetDebugMode
BFlagyes/no
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to switch the debug mode. The debug mode will enable to write extra log data for examination.
Sample: 'setDebugMode':
auto


5. ExternalCsvMapFile

Fields:
ColumnWhatExample
ACommandNameExternalCsvMapFile
BFlagyes/no
CFileNamedata\csvmaps.xlsx
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not use the map file.
  3. Column C 'FileName' is used to specify the name of the file. This file is supplied by Teknewell. The file is used to translate the bank's credit card CSV file into this tool's data input.
Sample: 'ExternalCsvMapFile':
auto


6. createOneBook

Fields:
ColumnWhatExample
ACommandNamecreateOneBook
BFlagyes/no
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not create a consolidated input one book. This book stores all input sheets as if the user has entered them in one book with the categorizing method we recommend.
Sample: 'createOneBook':
auto


7. createAccountHierarchy

Fields:
ColumnWhatExample
ACommandNamecreateAccountHierarchy
BFlagyes/no
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not create an account hierarchy book. This book stores all account relationships in a hierarchical way by Command GetFrom and PullFrom.
Sample: 'createAccountHierarchy':
auto


8. cutOffDate

Fields:
ColumnWhatExample
ACommandNamecutOffDate
BFlagyes/no
CMonth4
DDate30
EYear2023
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not create a sheet for InitCommands with a Cut Off Date.
  3. Column C 'Month' is used to specify the month of the Cut Off Date.
  4. Column D 'Date' is used to specify the date of the Cut Off Date.
  5. Column E 'Year' is used to specify the year of the Cut Off Date.
Sample: 'cutOffDate':
auto


9. useStyleTemplates

Fields:
ColumnWhatExample
ACommandNameuseStyleTemplates
BFlagyes/no
CFileNamedata\csvmaps.xlsx
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not use the map file.
  3. Column C 'FileName' is used to specify the name of the file. The file is used to provide style to the end reports. The sample model downloaded includes such a file. It uses a rule of sheet name matching sheet name.
Sample: 'useStyleTemplates':
auto


10. createNextCycle

Fields:
ColumnWhatExample
ACommandNamecreateNextCycle
BFlagyes/no
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Flag' is used to specify the flag value to whether or not execute the command 'prepareSheet'.
Sample: 'createNextCycle':
auto


11. numberOfFilesToKeep

Fields:
ColumnWhatExample
ACommandNamenumberOfFilesToKeep
BThe number200
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'The number' is used to specify an integer number which is used to keep the latest files within the limit when user cleans the history files in directory log, rpt and copy.
Sample: 'numberOfFilesToKeep':
auto



I. The input sheets


1. General

The input sheets are a collection of Excel Sheets from 1 or multiple Excel files. Following rules are enforced to qualify as input commands.
  1. The Excel files reside in a directory specified by command 'scan' in Model.
  2. The Excel file names contain a prefix 'eat'.
  3. The sheet names contain a prefix 'Sheet' or 'Cycle'.
  4. On column 1 of each sheet, a valid command name must be there and its syntax must be complying with what defined in this document.

2. Command dependencies

Following listed the order in which the later ones depend on the previous ones.
  1. Define account types.
  2. Define accounts.
  3. Define account modifiers.
  4. Define account header forms.
  5. Define analytical commands.
  6. Define cycle sheet.
  7. Add records on form.

3. Arithmetic Expression

Following is the EAT definition of Arithmetic Expression.
  1. Basic Operands
    OperandFunction
    Field NameThese field names are the ones that were already defined as a table's column names.
    DoubleAny valid double value character sequence. For example, 100.15 or -90.07.
    PREVThis reserved word indicates the previous row value at the same field is used in calculation.

  2. 4 2-operand arithmetic operators
    OperatorFunction
    /Division
    *Multiplication
    -Subtraction
    +Addition

  3. General rules
    1. The operator order goes: First division, seocond multiplication, third subtraction, forth addition.
    2. User can change the operator order by placing parenthesis '(' and ')'.

4. Conditional Expression

Following is the EAT definition of Conditional Expression.
  1. Basic Operands
    OperandFunction
    Field NameThese field names are the ones that were already defined as a table's column names.
    StringAny valid charactor sequence. It does not need to be double quoted. For example, Bank of America. It shouldn't be a Field Name, otherwise it will be treated so.
    DoubleAny valid double value character sequence. For example, 100.15 or -90.07.

  2. 8 none-case-sensitive 2 operand comparison operators
    If the 2 operand values are deemed to be double values, the operator is a streight math equivalent one. If one of the 2 operand values is a double value and the other is empty, then the empty one is converted to 0.0, the the operator is a streight math equivalent one. Following table is a description for String Comparison only.
    OperatorFunction
    ==If the 2 strings are not in equal length, it is false; If one of their chars in same index position is not same ('a' is considered the same as 'A' in none-case-sensitive), it is false.
    !=If the 2 strings are not in equal length, it is true; If one of their chars in same index position is not same ('a' is considered the same as 'A' in none-case-sensitive), it is true.
    +>If first string contains second string, it is true, otherwise false.
    ->If first string does not contains second string, it is true, otherwise false.
    >First leading and trailing Space chars are removed and second the short string is padded with space char ' ' to the length of the longer string, then compare each char starting from the left most to the right end, if the char at same index position in first string is smaller, it is false. otherwise true;
    <First leading and trailing Space chars are removed and second the short string is padded with space char ' ' to the length of the longer string, then compare each char starting from the left most to the right end, if the char at same index position in first string is larger, it is false. otherwise true;
    >=First apply the rule for operator '==', if false, apply the rule for operator '>'.
    <=First apply the rule for operator '==', if false, apply the rule for operator '<'.

  3. 8 case-sensitive 2 operand comparison operators
    OperatorFunction
    #==same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #!=same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #+>same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #->same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #>=same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #<=same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #>same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.
    #<same as the equivalent one above except a sharp char # is added at leading position and it is case-sensitive.

  4. logic operators
    OperatorFunction
    &&this operator takes 2 operands. If both operands are true, then the expression is true, otherwise false.
    ||this operator takes 2 operands. If one of them is true, then the expression is true, otherwise false.
    The operator "&&" is evaluated first and "||" second.
    Use parenthesises '(' and ')' to promote expression priority.

  5. EAT does not offer singular logic operator !

  6. General rules
    1. If the expression is true, then the record is evaluated to be picked up.
    2. For logic operators, the operator "&&" is evaluated first, the operator "||" is evaluated second. If you have a different order, use the parenthesis '(' and ')'. The expression in the parenthesises is evaluated first, then proceed to the rest of the entire expression.
    3. See example in command CopyAccount below.
    4. The char # called Sharp means more detailed and clearer. and Case-sensitive means considering more details. So they do connect in meaning, so good for memory to remember.


J. The input commands


1. type

Fields:
ColumnWhatExample
ACommandNametype
BTypeNameUtilityBill
CFieldNameMinus
DFieldNamePlus
EFieldNameNet|Calc=Plus-Minus|
FFieldNameBalance|Calc=PREV+Net
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'TypeName' is used to specify the type name.
  3. Column C 'FieldName' is used to specify the amount deducted.
  4. Column D 'FieldName' is used to specify the amount added.
  5. Column E 'FieldName' is used to specify the net amount.
  6. Column F 'FieldName' is used to specify the incremental amount.
Sample: 'type':
auto

Description:
The command 'type' is the first record a user has to write because a user has to start with an account and an account depends on a type. The command 'type' specifies the fields an account uses. A 'type' record comes with several other features like hiding a column positioning a column or a column calculation. Once a Type is defined one or more account(s) can derive from it.

The key word 'PREV':
The key word 'PREV' is reserved and represents a value from previous record in order in the same field.

Hide a field:
In 'Note|Hide=yes' 2 strings are seperated by |. The first string is the field name 'Note' second string is 'Hide=yes'. 'yes' means this field is to be hidden in resulting report. Use 'Hide=no' to do the opposite.

Calculate a field:
In 'Balance|Calc=PREV+Net' 2 strings are seperated by |. The first string is the field name 'Balance' second string is 'Calc=PREV+Net'. The second string 'Calc=PREV+Net' means this field is calculated from the result the sum of the previous value and the value of field 'Net'. Currently the arithmetic calculation only involves the 4 basic operators + - * and / with round brackets '(' and ')' for example Calc=(Plus-Minus)*Scale+PREV'. The char 'c' is reserved to mark the calculation. Refer to Arithmetic Expression above.

Position a field:
In 'Description|Index=5' 2 strings are seperated by |. The first string is the field name 'Description' the second string is 'Index=5'. The second string 'Index=5' means this field is to be shown in position 5 relatively.

Reset balance:
In 'GroupBalance|GCalc=PREV+Net' the formula will just act like 'Calc=PREV+Net' except that the GroupBalance will start from 0.0 when it arrives on a different group of the record key on which the transactions were sorted.

Default fields:
  1. 'KeyField' is the field to hold another field name whick serves as 'key'.
  2. 'DateSeqAsKey' combines field Year (4 digits) Month (2 digits) Date (2 digits) and SeqNumber (4 digits) seperated by a '.'.
  3. 'Year'
  4. 'Month'
  5. 'Date'
  6. 'SeqNumber' is an integer number. This allows multiple transactions on the same date.
  7. 'Marker' holds the string 'good here' to allow pulling over to another account.
  8. 'HyperLinkFieldId' is internally used for HyperLink for field Source Target and LinkTo.
  9. 'Source' holds the account name the row transactional data comes from.
  10. 'Target' holds the account name the row transactional data to be updated to.


2. account

Fields:
ColumnWhatExample
ACommandNameaccount
BAccountNameGasBill
CTypeNameUtilityBill
DCategoryNameUtility
EReportFlagyes/no
FDescriptionany notes
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'TypeName' is used to specify the type name the account is going to derive from
  4. Column D 'CategoryName' is used to specify the category the account falls in. The category is used to group accounts together on reports.
  5. Column E 'ReportFlag' is used to specify the report flag. 'yes' means to show the account records on report, 'no' otherwise.
  6. Column F 'Description' is used to specify notes a user can take and has no impact on reports.
Sample: 'account':
auto

Description:
Defining an account is quite simple. Multiple accounts can be grouped together and be derived from a same 'type'.


3. AddHeader

Fields:
ColumnWhatExample
ACommandNameAddHeader
BAccountNameChecking
CFormNameWay01
DFieldNameYear
EFieldNameMonth
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FormName' is used to specify the form name to enter data in.
  4. Column D 'FieldName' is used to specify the field name for which a value can be entered in command 'AddRecord' at the same column.
  5. Column E 'FieldName' is used to specify more fields are listed beyond.
Sample: 'AddHeader':
auto

Description:
This command defines a data entry form for an account. A different form can be defined by a different form name at column C. This command is used in conjunction with command 'AddRecord'. The values entered in the latter command must match the columns in this command 'AddHeader'. This command can only be written once for the same form or an warning is logged.


4. AddHeader

Fields:
ColumnWhatExample
ACommandNameAddHeader
BAccountNameChecking
CFormNameWay01
DFieldNameYear
EFieldNameMonth
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FormName' is used to specify the form name to enter data in.
  4. Column D 'FieldName' is used to specify the field name for which a value can be entered in command 'AddRecord' at the same column.
  5. Column E 'FieldName' is used to specify more fields are listed beyond.
Sample: 'AddHeader':
auto

Description:
This command defines a data entry form for an account. A different form can be defined by a different form name at column C. This command is used in conjunction with command 'AddRecord'. The values entered in the latter command must match the columns in this command 'AddHeader'. This command can only be written once for the same form or an warning is logged.


5. addHeader

Description:
Second sample.

Sample: 'addHeader':
auto


6. VirtualHeader

Fields:
ColumnWhatExample
ACommandNameVirtualHeader
BAccountNameChecking
CFormNameWay01
DFieldNameYear
EFieldNameMonth
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FormName' is used to specify the form name to enter data in.
  4. Column D 'FieldName' is used to specify the field name for which a value can be entered in command 'AddRecord' at the same column.
  5. Column E 'FieldName' is used to specify more fields are listed beyond.
Sample: 'VirtualHeader':
auto

Description:
This command is a equivalent command that can guide user to enter data in form. This command has no functional impact internally. It can be written anywhere to help user. The field order is the same as the one with the same account name and form name.


7. add

Fields:
ColumnWhatExample
ACommandNameadd
BAccountNameChecking
CFieldNameYear
DFieldValue2023
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name to enter data for.
  4. Column D 'FieldValue' is used to specify the field value for the field right before it on the left.
Sample: 'add':
auto

Description:
This is a free style of data entry method. Starting at cell C the field name and value appear as a pair one after another. If user needed to insert blank cell a pair of blank cells must be entered so that all the pairs start at a column of odd number like 3 5 etc. Here column C is odd number 3.


8. add

Description:
Third sample with formula 'PullFrom'. This 'PullFrom' formula has 3 or 4 fields separated by char '|'. The 4th field is optional can be omitted. When omitted the default key phrase 'good here' is used to validate the row in which a value is pull from or otherwise the user key word is used.

Sample: 'add':
auto


9. add

Description:
Second sample.

Sample: 'add':
auto


10. AddRecord

Fields:
ColumnWhatExample
ACommandNameAddRecord
BAccountNameChecking
CFormNameWay01
DFieldValueYear
EFieldNameMonth
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FormName' is used to specify the form name to enter data in.
  4. Column D 'FieldValue' is used to specify the field value that can be entered according to its form.
  5. Column E 'FieldName' is used to specify more field values are listed beyond.
Sample: 'AddRecord':
auto

Description:
This command allows user to enter transactional record values according to its form defined in command 'AddHeader'.


11. Repeat

Sample: 'Repeat':
auto

Description:
This command has the same syntax as the command 'Add'. It will duplicates an 'Add' command in a sheet prefixed with 'Repeat' provided it has the same SeqNumber. User can also utilize the preserved word 'copy' in the field 'Year' to copy the year month and date values to the duplicate.


12. RepeatRecord

Sample: 'RepeatRecord':
auto

Description:
This command has the same syntax as the command 'AddRecord'. It will duplicates an 'AddRecord' command in a sheet prefixed with 'Repeat' provided it has the same SeqNumber. User can also utilize the preserved word 'copy' in the field 'Year' to copy the year month and date values to the duplicate.


13. AddByLink

Fields:
ColumnWhatExample
ACommandNameAddByLink
BSourceAccountNameDevGasss
CSourceFieldNameMinus
DTargetAccountNameBaChecking
ETargetFieldNameMinus
FMatchWordLink
GFieldNameCategory
HFieldValueBill
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'SourceAccountName' is used to specify the source account name.
  3. Column C 'SourceFieldName' is used to specify the source account field name.
  4. Column D 'TargetAccountName' is used to specify the target account name.
  5. Column E 'TargetFieldName' is used to specify target account field name.
  6. Column F 'MatchWord' is used to specify the matching word in field Marker used to validate a transaction to move the field value from source to target.
  7. Column G 'FieldName' is used to specify the field name to be appended to the transaction.
  8. Column H 'FieldValue' is used to specify the field value to be appended to the transaction.
  9. Column I 'maybe more' is used to specify maybe more
Sample: 'AddByLink':
auto

Description:
This is the second method to move a field data to a target field. The first method is to use the command 'RepeatRecord'. These 2 methods should be used exclusively. The column F specify a match word which should also be written in a record's Marker field so that to allow the record to be validated. Starting at column G pairs of field name and value can be attached. Empty pairs can be inserted too but all must be entered in pairs immediately one after another.


14. groupSubtotalForChart

Fields:
ColumnWhatExample
ACommandNamegroupSubtotalForChart
BAccountNameChecking
CFieldName(s)Year|Month
DFieldName(s)Plus|Minus
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName(s)' is used to specify the field name(s) to group upon. The field names are seperated by a pipe '|'.
  4. Column D 'FieldName(s)' is used to specify the field name(s) to get total on group. The field names are seperated by a pipe '|'.
Sample: 'groupSubtotalForChart':
auto

Description:
The result of the group totals are appended at the bottom of the account in report. User can utilize the Excel graph function to draw a picture.


15. SetInitValue

Fields:
ColumnWhatExample
ACommandNameSetInitValue
BAccountNameBaChecking
CFieldNameBalance
DValue103.67
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field at which an initial value will be set.
  4. Column D 'Value' is used to specify a value as an initial for the field.
Sample: 'SetInitValue':
auto

Description:
An accumulative field like 'Balance' is increasing by adding current net value to the previous value. So the very first previous value is called an initial value and user can set it with this command.


16. setKeyField

Fields:
ColumnWhatExample
ACommandNamesetKeyField
BAccountNameBaChecking
CFieldNameCategory
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field which will serve as a key. Or multiple field names separated by '|', like Category|Location.
Sample: 'setKeyField':
auto

Description:
User can choose a field other than the Year Month Date and SeqNumber combination as a key. The records of an account in report will be sorted by the key.


17. setKeyField

Description:
The second sample with multiple key fields.

Sample: 'setKeyField':
auto


18. setGroupField

Fields:
ColumnWhatExample
ACommandNamesetGroupField
BAccountNameBaChecking
CFieldNameCategory
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field which will serve as a group key. Or multiple field names separated by '|', like Category|Location.
Sample: 'setGroupField':
auto

Description:
The default group key filed is the Year Month Date and SeqNumber combination which is the same as the sorting key. User must use this command in order to use a different field as the grouping field. Note the sorting key and grouping key are 2 different functions.


19. changeOrderIndex

Fields:
ColumnWhatExample
ACommandNamechangeOrderIndex
BAccountNameDevElect
CFieldNameMonth
DValue12
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field which will assume a different index in order on report.
  4. Column D 'Value' is used to specify the value of an integer which serves as an index on which the fields are sorted on report.
Sample: 'changeOrderIndex':
auto

Description:
This command allows user to move a field of an account to a new position on report.


20. changeOrderIndex

Fields:
ColumnWhatExample
ACommandNamechangeOrderIndex
BAccountNameDevElect
CFieldNameMonth
DValue12
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field which will assume a different index in order on report.
  4. Column D 'Value' is used to specify the value of an integer which serves as an index on which the fields are sorted on report.
Sample: 'changeOrderIndex':
auto

Description:
This command allows user to move a field of an account to a new position on report.


21. discardRowWhen

Fields:
ColumnWhatExample
ACommandNamediscardRowWhen
BAccountNameDevElect
CConditional ExpressionNet==0
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Conditional Expression' is used to specify the conditional expression upon which the records are evaluated to be descarded or not.
Sample: 'discardRowWhen':
auto

Description:
This command allows user to specify whether to skip a record if the conditional expression is true. For example if the calculation 'Net==0' is true the record is not shown on report (discarded). Refer Conditional Expression above.


22. hideTheField

Fields:
ColumnWhatExample
ACommandNamehideTheField
BAccountNameDevElect
CFieldNameMonth
DValueyes/no
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name of a field which will be hidden on report.
  4. Column D 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to hind the field, 'no' is otherwise.
Sample: 'hideTheField':
auto

Description:
This command allows user to hind a field of an account on report.


23. startingColumnOfContenT

Fields:
ColumnWhatExample
ACommandNamestartingColumnOfContenT
BAccountNameDevElect
CValue3
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of an integer which serves as a column number at which the account records are to be shown starting.
Sample: 'startingColumnOfContenT':
auto

Description:
This command allows user to specify the starting column the account records are to be shown from.


24. startingRowOfContent

Fields:
ColumnWhatExample
ACommandNamestartingRowOfContent
BAccountNameDevElect
CValue7
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of an integer which serves as a row number at which the account records are to be shown starting.
Sample: 'startingRowOfContent':
auto

Description:
This command allows user to specify the starting row the account records are to be shown from.


25. writeIndexRow

Fields:
ColumnWhatExample
ACommandNamewriteIndexRow
BAccountNameDevElect
CValueyes/no
DValue1
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to show the index row , 'no' is otherwise.
  4. Column D 'Value' is used to specify the value of an integer which serves as a row number to show the index row.
Sample: 'writeIndexRow':
auto

Description:
This command allows user to write the indexes assigned to fields on a row specified.


26. writeNameRow

Fields:
ColumnWhatExample
ACommandNamewriteNameRow
BAccountNameDevElect
CValueyes/no
DValue2
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to show the field name row , 'no' is otherwise.
  4. Column D 'Value' is used to specify the value of an integer which serves as a row number to show the field name row.
Sample: 'writeNameRow':
auto

Description:
This command allows user to write the field names on a row specified.


27. writeNumberRow

Fields:
ColumnWhatExample
ACommandNamewriteNumberRow
BAccountNameDevElect
CValueyes/no
DValue3
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to show the number row , 'no' is otherwise.
  4. Column D 'Value' is used to specify the value of an integer which serves as a row number to show the number row.
Sample: 'writeNumberRow':
auto

Description:
This command allows user to write the number row which simply count and show integers from 1 to N where 1 is column A 2 is B etc.


28. writeInitValueRow

Fields:
ColumnWhatExample
ACommandNamewriteInitValueRow
BAccountNameDevElect
CValueyes/no
DValue6
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to show the initial value , 'no' is otherwise.
  4. Column D 'Value' is used to specify the value of an integer which serves as a row number to show the initial value.
Sample: 'writeInitValueRow':
auto

Description:
This command allows user to write the initial value onto a row on report. The column is decided by the field name the value is associated.


29. writeNavigationColumn

Fields:
ColumnWhatExample
ACommandNamewriteNavigationColumn
BAccountNameDevElect
CValueyes/no
DValue2
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'Value' is used to specify the value of 'yes' or 'no'. 'yes' is to write the navigational hyperlinks , 'no' is otherwise.
  4. Column D 'Value' is used to specify the value of an integer which serves as a column position the HyperLinks will be written at on report.
Sample: 'writeNavigationColumn':
auto

Description:
This command allows user to write the Sheet Navigational Hyperlinks on a specified column.


30. startColumnForGSC

Fields:
ColumnWhatExample
ACommandNamestartColumnForGSC
BAccountBaChecking
CColumnG
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'Account' is used to specify the account name.
  3. Column C 'Column' is used to specify the column letter or number starting at 1.
Sample: 'startColumnForGSC':
auto

Description:
The column is either a column letter or an integer number starting at 1. This command allows user to move the cell block horizontally. The block is generated by commnad 'groupSubtotalForChart'.
… … … … … …

31. setFirstRowColor

Fields:
ColumnWhatExample
ACommandNamesetFirstRowColor
BAccountNameBaChecking
CFieldNameMonth
DColorNameYellow
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'AccountName' is used to specify the account name.
  3. Column C 'FieldName' is used to specify the field name used to mark the first row with a background color.
  4. Column D 'ColorName' is used to specify the color name used to mark the first row in a group identified by the field above. The color names are listed at the end of this document.
Sample: 'setFirstRowColor':
auto

Description:
Marking the first row of groups makes account reading more productive.


32. copyAccount

Fields:
ColumnWhatExample
ACommandNamecopyAccount
BOrderIndex4
CSourceAccountNameBaChecking
DTargetAccountNameBaCheckingByCategory
EConditional ExpressionFieldName> 100
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'OrderIndex' is used to specify an integer which serves as value on which all action commands are sorted and executed in order.
  3. Column C 'SourceAccountName' is used to specify the source account name of the account from which to copy data to target account.
  4. Column D 'TargetAccountName' is used to specify the target account name of the account to which to copy data from a source account.
  5. Column E 'Conditional Expression' is used to specify how to pick records. This cell is optional. Leave it blank for all records. See example below and the definition of Conditional Expression above.
Sample: 'copyAccount':
auto

Description:
This command simply copies an account records to another. The order index indicates all action commands are executed in order by the index.


33. copyAccount

Description:
The second sample with record-picking conditional expression

Sample: 'copyAccount':
auto


34. copyAccount

Description:
The third sample with record-picking conditional expression

Sample: 'copyAccount':
auto


35. joinAccounts

Fields:
ColumnWhatExample
ACommandNamejoinAccounts
BOrderIndex16
CSourceAccountNameBaChecking
DSourceAccountNameBaSavings
ETargetAccountNameBaCheckingSavingsJoined
FKeyFieldNamesCategory|Net
GJoinTypesyours|mm
HFieldControlsBaChecking|Minus|none#BaChecking|Plus|Credit
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'OrderIndex' is used to specify an integer which serves as value on which all action commands are sorted and executed in order.
  3. Column C 'SourceAccountName' is used to specify the account name of the first source account from which the records are joined with the second account into a traget account.
  4. Column D 'SourceAccountName' is used to specify the account name of the second source account from which the records are joined with the first account into a traget account.
  5. Column E 'TargetAccountName' is used to specify the account name of the target account into which the 2 source accouts are joined.
  6. Column F 'KeyFieldNames' is used to specify the key field names of both source accounts.
  7. Column G 'JoinTypes' is used to specify the types of joinning methods.
  8. Column H 'FieldControls' is used to specify Additional rules to deal with other fields.
Sample: 'joinAccounts':
auto

Description:
This command joins 2 source accounts into 1 target account.

Key Fields:
The key fields are used to join records. If multiple fields were to be used use '|' to separate the field's names. All accounts must have the same key fields.

Join Types:
The first type is 'mine'. The second type is 'yours'. The third type is 'mm m1 1m'. The adoption of word 'mine' and 'yours' is derived from a metaphor which is saying that the user is the owner of the first account of 'mine' and he is trying to join the first account to the second account which is owned by a second person of 'yours' .
'mine' means the records are of first account and have no match from second account. 'yours' means the records are of second account and have no match from first account.
The third type is 'mm m1 1m'. This type means the records are matching from both source accounts.
'mm' means 'many to many' and the matched records are joined completely. For example if there are 2 records from first account and 3 records from second account that they share same key value there will be 6 joined records in the traget account.
'm1' means 'many to 1' and the matched records are joined partially. For example if there are 2 records from first account and 3 records from second account that they share same key value there will only be 2 joined records in the traget account 2 from first account and 1 from second account.
'1m' means '1 to many' and the matched records are joined partially. For example if there are 2 records from first account and 3 records from second account that they share same key value there will only be 3 joined records in the traget account 1 from first account and 3 from second account.
mm' 'm1' and '1m' are mutually exclusive meaning only one can be used at a time. So 'mine' 'yours' and one of the 'mm' 'm1' and '1m' can be used together using a seperator '|' for example 'your|m1' or 'mine|1m' etc.

FieldControls:
Without the additional FieldControls the default function is to move data from a source field to a matching target field. All source fields that are not in the target account are abandoned.
The rule is written in format 'account|field|field' or 'account|field|none'. The format has 3 strings. The first string is the account name. The second string is a field from the account of first string. The third string is either the reserved word 'none' or a target field name. The word 'none' indicates the field from the first string account will not participate in the 'join'. A target field name indicates the field from the first string account will be joined over to the target field (renamed).
Multiple rules are concatenated using the seperator char '#'.

More Illustration:


36. prepareCycle

Fields:
ColumnWhatExample
ACommandNameprepareCycle
BSheetNameCycle2023May
CSheetNameCycle2023June
DCommandNamesAdd|AddRecord
EFirst of the Pair of Type 1column|f
FSecond of the Pair of Type 1value|empty
GFirst of the Pair of Type 2field|PreviousBalance
HSecond of the Pair of Type 2fieldValue|NewBalance
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'SheetName' is used to specify the sheet name of a sheet which is based to copy from as source.
  3. Column C 'SheetName' is used to specify the sheet name of a sheet which is to be created by copying a source sheet intoi it.
  4. Column D 'CommandNames' is used to specify command names of the commands of which their fields are to be modified based on the formulas in pairs that follow.
  5. Column E 'First of the Pair of Type 1' is used to specify the formula which consists 2 strings seperated by |. The first is reserved name 'column' which indicates the second is a column letter like 'f' etc.
  6. Column F 'Second of the Pair of Type 1' is used to specify the formula which consists 2 strings seperated by |. The first is reserved name 'value' which indicates the second is a pure value. The string 'empty' indicates nothing in value.
  7. Column G 'First of the Pair of Type 2' is used to specify the formula which consists 2 strings seperated by |. The first is reserved name 'field' which indicates the second is a field defined in 'type'. The field is to be updated from the second of the pair.
  8. Column H 'Second of the Pair of Type 2' is used to specify the formula which consists 2 strings seperated by |. The first is reserved name 'filedvalue' which indicates the second is also a field name. This field is pulled from its value and the value is updated to the field defined in the first of the pair.
Sample: 'prepareCycle':
auto

Description:
This command will create a new cycle sheet for a new cycle. The new cycle sheet will most inherits the previous cycle values except the updates made through the formulas. This enables the next cycle forecasting very effectively. The formula pairs are to be placed starting at cell E. Empty pairs can be placed in between. They must be appearing in pairs. There is a UI CheckBox on page Model. User can disable the generation of the new cycle sheet even though the command is present as input.


37. EatDll

Fields:
ColumnWhatExample
ACommandNameEatDll
BDllFileNamecodeSnippet.dll
CNameSpaceCodeSnippet
DClassNameClass1
EMethodNamewriteCodeSnippet
FSheetNameCodeSpippet
GAccountNameBaChecking
HExtra data 1data1
IExtra data 2data2
JExtra data 3data3
  1. Column A 'CommandName' is used to specify the command.
  2. Column B 'DllFileName' is used to specify the dll file name with extension.
  3. Column C 'NameSpace' is used to specify the NameSpace name in the dll project.
  4. Column D 'ClassName' is used to specify the class name within the NameSpace.
  5. Column E 'MethodName' is used to specify the method name to which the main program EatUi.exe will calll. Copy the method prototype from below.
  6. Column F 'SheetName' is used to specify the sheet name.
  7. Column G 'AccountName' is used to specify the account name. User can place more than 1 account names here , the names are separated by a string '[0]'. For each account, user can add a condition expression after the account name and a separator string '[1]'. Here is an example 'AccountName1[1]fieldName==aValue[0]AccountName2[1]fieldName2==aValue2'. If they are multiple account names, the multiple method prototype is used instead of the single one. Refer to below for the second prototype.
  8. Column H 'Extra data 1' is used to specify the additional data at user‘s disposal, optional.
  9. Column I 'Extra data 2' is used to specify the additional data at user‘s disposal, optional.
  10. Column J 'Extra data 3' is used to specify the additional data at user‘s disposal, optional. No limit is set for how many data row to follow.
Sample: 'EatDll':
auto

Description:
Please copy the method prototype to build a dll project. The returned array will be presented in a sheet named in field F. 3 dll app code files are presented as example below. When creating a dll project in Visual Studio 2022 use the project type 'Class Library (.NET Framework)'. The dll function includes a delegate function at the end. User can call it to get more data. This delegate function is defined in a dll named EatUserDllExtension. User must includes it as a reference in project. The delegate name is AskForMore.

Dll Sample Code for Command EatDll:
auto

38. EatDll

Description:
The second sample with a condition.

Sample: 'EatDll':
auto

Dll Sample Code 2 for Command EatDll:
auto

39. EatDll

Description:
The third sample rendering multiple accounts

Sample: 'EatDll':
auto

Dll Sample Code 3 for Command EatDll:
auto


K. Credit card accounts


1. General

Simply download CSV monthly statement files to the directory defined by command 'scanbank' in Model. We avoided to use a PDF product to interpret PDF statements so that we could save for customers. The CSV files are working fine.

2. Supported Bank Websites


Card Name Bank Name Web Site
Costco citi citi.com
N/A Chase chase.com
TJX Synchrony tjx.syf.com
Homedepot N/A homedepot.com
Lowes N/A https://www.lowes.com/u/login
Macys N/A https://www.macys.com/account/signin
N/A CapitalOne CapitalOne.com
Checking Bank of America bankofamerica.com
Savings Bank of America bankofamerica.com
Home Equity Bank of America bankofamerica.com
US Bank US Bank https://onlinebanking.usbank.com/
figure 3571

3. CSV Map file

On Model page in the master program EatUi.exe, there is an entry for the map file. We supply the file and it works for the major bank credit card companies listed above. We recommend user don't change this file because it is too complicated to change. If a user has a CSV statement for a company other than the ones listed, we will add the map sheet for the user for free to support his or her cause.

4. Bank file names

In the map file, each sheet represent a statement. The sheet name must conform to such a format that there are 4 strings seperated by hyphone '-'. The first string must be 'sheet', second string is the bank name. The third string is the accoune name, usually the account's last 4 digits. And the last string is the file's extention which is 'csv'. So for example, if a user have 2 Capital accounts with last 4 '1234' and '5678', the sheet name will be 'sheet-capital-1234-csv' and 'sheet-capital-5678-csv', and the file names of the bank files downloaded should be renamed as 'capital-1234-20230901.csv' and 'capital-5678-20230901.csv'. All sheet content remains intact. For more than 1 statemnets, the second and beyond are just copies of the first one.


L. Link a field


1. General

The strength of this tool is its capability to link field and move data to another account to effectively complete a new record.

2. The GetFrom Link Command

This command takes the format 'GetFrom|AccountName|FieldName' or 'GetFrom|AccountName|FieldName|RecordKey'. The second format includes a 4th field which is the record key to locate a source record. If this 4th field is missing, the target record key is used to locate the source record.

3. The first scenario

In this example, The bill payment of Electricity $45.00 is updated to account 'Utility', but as it is a fact that the amount is paid from the account 'Checking', the amount should also be updated to the account 'Checking'. 2 'Add' or 'AddRecord' commands must be entered into the input sheet. This is done by using the formula 'GetFrom'.

4. The second scenario

In this example, user created an aggregate account that collects the last balance amounts from several ladger accounts. User needs to enter an 'Add' or 'AddRecord' command into the input sheet. This is done by using the formula 'PullFrom'.

5. Link Method 1

Using the formula 'GetFrom' in command 'Add' or 'AddRecord' and enter the command in a cycle sheet. One condition is that the key DateSeqAsKey must be the same as the source account's. So it is recommended to set the SeqNumber to an unique value in cycle sheet. And user could enter 'Copy' in field 'Year' to indicate to copy the year month and date part from the source account as long as the SeqNumber is the same in the same cycle sheet.

6. Link Method 2

Using the formula 'GetFrom' in command 'RepeatRecord' and enter the command in a seperate stand-alone sheet. And name the cycle sheet with prefix 'Cycle'. All other rules remain the same. The command 'RepeatRecord' means that this command will be renamed to 'AddRecord' and duplicated into all sheets that have the prefix 'Cycle'.

7. Link Method 3

This method uses the command 'AddByLink'. Please refer to it regarding how to use it.

8. Link on 'good here'

The above 3 methods apply to the first scenario. The seconed is using the formula 'PullFrom'. This method is looking for the last record with the Marker having the value 'good here', then the specified field value is pulled over to the new record. The 'PullFrom' formula has 3 or 4 fields separated by char '|'. The 1st field is the formula name. The 2nd is the accoune name. The 3rd is the field name. The 4th is the validation key phrase. The 4th field is optional, can be omitted. When omitted, the default key phrase 'good here' is used to validate the row in which a value is pull from, or otherwise the user key word is used.

9. Examples

Looking at command 'Add', 'AddRecord' and 'RepeatRecord', user can find an example regarding anything in this chpter. Say, how 'Copy' is used, refer to command 'RepeatRecord'.


M. Analyze the reports


1. General

The reports are just showing each accounts in Excel sheet. The order of the sheets is the order defined in a argument of all the command 'account'. Each sheet will list all transactions in the order of the key, usualy the combination of the fields 'year', 'month', 'date' and 'SeqNumber'.

2. The total

Each user could have the Model set up such that the financial total is calculated in the total account. The total account is the top tier account that contains transactions that aggregate the end balances from several other lower tier accounts, and which in turn could aggregate from further other lower tier accounts. For example, the 'Total' account is a sum of the end balance from 'Cash', 'Investment' and 'Liability'. And The 'Cash' account is a sum of the end balance from 'Checking' and 'Savings' , etc.

3. The style

Each sheet could be tuned to certain style. On the Model page in EatUi.exe, there is an entry for the style template file. This file contains the sheets that the names of the sheets match the account names, and then the sheets provide a base for the accounts to be written upon.

4. The Archive

The report file is named with date and model name. The file can be archived into the directory 'Archive'. The files in 'Archive' will not be deleted when user performs a 'clean' function which deletes files in directory 'rpt', 'log' and 'Copy' beyong the allowed number of files to keep.

5. Web Page Report

When user checked the CheckBox 'as Web Page' , and an equivalent report file is generated in the format of HTML. This report only selects the columns between Collumn 'DateSeqAsKey' and 'LastField'. So user should add the field 'LastField' and arrange the account fields in an order that user prefer.


N. Analyze the data


1. Group Transactions

The command 'groupSubtotalForChart' can group an account transactions and calculate the group's totals, and show the data at the end of the account report.

2. Filter & Group Transactions

Other than the command 'groupSubtotalForChart', there are 4 other commands that can move transactions to a new account. The movement can be customized with filters and groups to produce a subset of or aggregated set of transations, for analysis purposes. These commands are 'copyAccount', 'copyMatched', 'copyMismatched' and 'joinAccounts'. Refer to each commands for user's purpose.


O. Prepare next cycle


1. General

For a user, monthly recurrence of payments and reconciliation of bank accounts are mandatory or smart task. User use a cycle sheet to describe all entry forms for a cycle. When entering next cycle, the forms in the next one are exactly the same, or otherwise the model is not smart due to requiring user to constantly change the forms. The smart way is that user only need to enter the data in the form assuming the cell had been background-color-highlighted. For example, just replace the payment amount, from $40.03 to $45.32, etc. This EAT tool provides a command to copy the very last cycle sheet to a new cycle sheet with styles intact. The command can change some of the field values in the new sheet. Following 3 items are recommended to be changed for the new cycle sheet.
  1. clear the Status field. The Status field is to flag if a record is entered and thus updated. When all is flagged, it is time to reconcile the new cycle.
  2. replace a new month number, and maybe year number.
  3. move a field data to another. For example, move 'currentBalance' to 'previousBalance', because a formula could calculate a new balance based on previous balance. This is true for Mortgage Balance and Escrow Balance.
Use the command 'prepareCycle' to carry out this task. Refer the command for how to use it. The new cycle sheet resides in its own Excel file. User just need to copy the sheet to where user prefers. There is a CheckBox for this command on page Model of EatUi.exe. If unchecked, the command is disabled.


P. Create one book


1. General

All bank files are translated into the input commands. All manual input commands plus all bank input commands can be consolidated into one Excel book. In turn this one book can be fed as an input file in a model. The result generated by this new One Book should be identical to the result by the ones in user's model. User could refer to this One Book and examine as a good exercise. There is no command for this function. Only use a CheckBox of 'create one book' on page Model of EatUi.exe to carry out this task. If unchecked, the function is disabled. The new One Book file is saved as its own Excel file. User could move it to a new model to test out, just for fun if there is no other practical reasons.


Q. Shorten the cycles


1. General

User may already has 4 years of data in input sheets. He is no longer concerned about the history data more than 1 year ago, want to remove them.

2. Step 1

He can remove the history cycle sheets by either renaming them with a prefix 'x' or simply remove the cycle sheets, and remove the bank directory from the model. This assumes that the cycle sheet only contains the cycle commands (Add and AddRecord commands for their cycle), not commands like 'type', 'account' and other generic commands.

3. Step 2

But that is not enough because what about the starting balances for all accounts? That means user has to come up with a set of initial values for the commnad 'setInitValue' for each accounts. Fortunately on the page Model of EatUi.exe there is an entry of 'create init commands', user can provide the Month, Date and Year as 'CutOffDate' for which the balance values are used to generate a set of commands of 'setInitValue'. This set of commands can replace the old ones. So by doing this 2 steps of task, user can reduce the number of cycles being considered in the accounting.


R. Back up


1. Purpose

All files reside on the same disk drive. If the disk drive is no longer functioning, all financial data files are lost. The solution is to backup the files to another USB or network drive.

2. Backup a Must

Purchase a portable USB drive for backup purpose. Plug the drive to a USB port. Backup after you have made changes to the system, usually at the end of day. Once the backup is finished, then proceed to shut down the PC.

3. How to

In Eat Manager, go to page Backup, click the bottom buttons to back up either to a drive or to a directory.

4. Method

This backup command only backs up the files that have the file dates newer than the ones on destination drive, avoiding waste of time on copying unnecessarily the files that have the same contents as that of the destination ones.


S. Update EAT


1. Download zip File

EAT update is free from charge. An owner of EAT can download the new version at www.excelaccountingtool.com/EatDownLoadV2Two/EatUpdate, provided the owner had logged in.

2. Unzip File

Unzip the downloaded zip file, put in the Installation Full Path (IFP) in 'This Folder' and choose 'Replace the files in the destination'.

3. License File

The new updated version works on the existing license file, which was untouched during the update and continues to work through out its term.


T. Definitions & Practices


1. Color Names

AliceBlue,    AntiqueWhite,    Aqua,    Aquamarine,    Azure,    Beige,    Bisque,    Black,    BlanchedAlmond,    Blue,    BlueViolet,    Brown,    BurlyWood,    CadetBlue,    Chartreuse,    Chocolate,    Coral,    CornflowerBlue,    Cornsilk,    Crimson,    Cyan,    DarkBlue,    DarkCyan,    DarkGoldenrod,    DarkGray,    DarkGreen,    DarkKhaki,    DarkMagenta,    DarkOliveGreen,    DarkOrange,    DarkOrchid,    DarkRed,    DarkSalmon,    DarkSeaGreen,    DarkSlateBlue,    DarkSlateGray,    DarkTurquoise,    DarkViolet,    DeepPink,    DeepSkyBlue,    DimGray,    DodgerBlue,    Firebrick,    FloralWhite,    ForestGreen,    Fuchsia,    Gainsboro,    GhostWhite,    Gold,    Goldenrod,    Gray,    Green,    GreenYellow,    Honeydew,    HotPink,    IndianRed,    Indigo,    Ivory,    Khaki,    Lavender,    LavenderBlush,    LawnGreen,    LemonChiffon,    LightBlue,    LightCoral,    LightCyan,    LightGoldenrodYellow,    LightGray,    LightGreen,    LightPink,    LightSalmon,    LightSeaGreen,    LightSkyBlue,    LightSlateGray,    LightSteelBlue,    LightYellow,    Lime,    LimeGreen,    Linen,    Magenta,    Maroon,    MediumAquamarine,    MediumBlue,    MediumOrchid,    MediumPurple,    MediumSeaGreen,    MediumSlateBlue,    MediumSpringGreen,    MediumTurquoise,    MediumVioletRed,    MidnightBlue,    MintCream,    MistyRose,    Moccasin,    NavajoWhite,    Navy,    OldLace,    Olive,    OliveDrab,    Orange,    OrangeRed,    Orchid,    PaleGoldenrod,    PaleGreen,    PaleTurquoise,    PaleVioletRed,    PapayaWhip,    PeachPuff,    Peru,    Pink,    Plum,    PowderBlue,    Purple,    Red,    RosyBrown,    RoyalBlue,    SaddleBrown,    Salmon,    SandyBrown,    SeaGreen,    SeaShell,    Sienna,    Silver,    SkyBlue,    SlateBlue,    SlateGray,    Snow,    SpringGreen,    SteelBlue,    Tan,    Teal,    Thistle,    Tomato,    Transparent,    Turquoise,    Violet,    Wheat,    White,    WhiteSmoke,    Yellow,    YellowGreen

2. Names Case

All names like sheet's, type's, account's, etc are case insensitive.

3. Word Search

If a word definition is not clear, try to search entire document for the word. It may exits somewhere else that could clear user's confusion.


U. History of changes


1. The changes

Date Description
07/15/2021v3.1adjust User Manual.
07/20/2021v3.1ENV Commands: modified how to define the command strings for Excel and Text editors. The new way is simpler and the user just write out an entire string with none case-sensitive 'XXX' which is to be replaced with the target file name.
07/20/2021v3.1Trial Period: modified the trial term period to be 3 month.
08/13/2021v3.1Concatenate the Calculation strings with separator Comma instead of '/r' to avoid not being displayed in LibreOffice in sheet Main_MathMap.
08/14/2021v3.2Remove error warning bell sound.
08/14/2021v3.2log as an error when capacity limits are reached.
08/22/2021v3.3Add Excel Macro-enabled file extension XLSM file as input file.
08/23/2021v3.3Change command 'setKeyField' to set none-standard key in multiple fields.
08/28/2021v3.3Add a command 'CopyAccount' to copy records from one account to another.
09/07/2021v3.3Add a command 'CopyAccountOnFilter' to copy and also filter records from one account to another.
09/14/2021v3.3Add a default field 'Marker' for every account. This field holds the value 'good here' or 'reset|FieldName1:0.0|FieldName2:0.0'. See 'Marker' for more information.
09/15/2021v3.3Add a mode of entering header and values separately, the Compact Mode.
09/16/2021v3.3Add 2 default fields 'Description' and 'Status' for every account. These 2 fields are so useful and important that we might just add them as default for conveniences.
10/06/2021v3.3Allow calculation formula accepts constants like '3.0' in 'Amount|c=Share*3.0'.
10/06/2021v3.3Since computer double calculation is not accurate and the near zero result is expressed in 'Natural Exponential Function', the value less than 0.03 is presented as 0 to avoid confusion.
10/08/2021v3.3change CopyAccountOnFilter to CopyAccountOnIncFilter and add CopyAccountOnExcFilter command.
10/12/2021v3.3change CopyAccount, CopyAccountOnIncFilter and CopyAccountOnExcFilter commands to have a 'Order Number' field which serves as an order in which the commands are executed.
10/19/2021v3.3Add Repeat and RepeatRecord commands. These 2 commands are primarily used for records that have a link field 'GetFrom' and its 'Year' field is 'Link' so that user don't have to enter them in each sheets. The 2 new commands are duplicated into sheets prefixed by 'Cycle' in name.
10/25/2021v3.3Add inclusion of 'a template file' for report. Refer to section 'Report templates' for more information.
10/26/2021v3.3Add few other formatting commands for report. Refer to section 'Report formatting commands' for more information.
11/10/2021v3.3Add process to create reports on Credit Card statements from 5 banks. More banks will be added in the future.
01/01/2022v4.1Add Capital and USBank Credit card PDF statement processing. Finished a better coding strategy for PDF Statement processing.
01/09/2022v4.1Made a minor revision for Command 'groupSubtotalForChart'. The revision is to allow the command to handle zero values for the totaling fields.
01/15/2022v4.1Added a Prelude file as Excel format that defined additional parameters. One parameter is the 'Scan' command which defines a directory in cell 2. User can define multiple 'Scan' command to define multiple directories. The other is a command called 'setDebugMode' which set the debug flag on and off. When it is on, many statistical data are written into the log file for analysis.
02/04/2022v4.1Enhanced the log report in Notepad++, which utilizes the collapsing #region #endregion region. Added a 'Debug mode' for log report.
02/08/2022v4.1Enhanced the field calculation formula to use left and right brackets '(' and ')' in the 'Type' definition. For example, user can use FieldName|c=Field3*(field4+45)
03/07/2022v3.1Removed ENV Command ExcelEditor and TextEditor: We learned Windows provides default programs to open files. So those parameters are removed. User should go to Windows Default Program to assign desired programs to open Excel and Text files.
03/15/2022v4.1Completed the code logic to handle bank CSV files, and removed the code handling bank PDF files due to the license requirement.
04/25/2022v4.1Added a log entry for version limit controls.
04/28/2022v4.1Added a report formating commnad 'writeNavigationColumn'.
04/28/2022v4.1Changed CSV bank directory from signular and static to multiple and configurable in parameter file using the command 'scanBank'.
04/29/2022v4.1Added Dialog form key Enter and ESC responses.
04/30/2022v4.1Integrated the location limit for both manual and bank auto directories.
05/01/2022v4.1Adjusted source code to its optimum consolidated object structure.
07/18/2022v4.1Changed command 'CopyAccountOnExcFilter' to 'CopyAccountOnExcFilter'.
07/18/2022v4.1Changed command 'CopyAccountOnIncFilter' to 'CopyAccountOnIncFilter'.
08/15/2022v4.3Fixed a bug of Pointer error caused by missing description field in Command 'account'.
09/02/2022v4.2Added an 'Instructiona and notes' page in Eat Manager to allow user to enter self-guiding instructions and notes for each model.
09/02/2022v4.3Fixed a mistake that the Hyper Link was written to a record of zero value, which then was removed from report.
09/17/2022v4.3Added a function of removing the customer download zip files for the sake of saving disk space.
09/17/2022v4.3Added a few of table fields to store data about the customer download files.
10/25/2022v4.4Created auto ordering process on the math steps to eliminate the need of user manually specifying the math steps.
11/20/2022v4.4Integrated the hierarchy view of any dependencies.
02/04/2023v5.0Created a new Object Programming Framework, and applied the entire program source codes to the framework.
02/05/2023v5.0Finished moving the entire program source codes to WinForm GUI.
03/05/2023v5.0Finished deployment of the project separate from Teknewell CS OOP Framework product.
04/27/2023v5.0Examined the correctness of the math calculation algorithm.
02/27/2024v5.1Added Conditional Expression. Its purpose is to evaluate a record for further processing.
03/06/2024v5.1Completed log entries in entirety.
03/07/2024v5.1Revised help argument.
03/08/2024v5.1Added auot back up function.
03/08/2024v5.2Upgrated to version v5.2.
03/14/2024v5.2Optimized Excel read and write class family.
03/27/2024v5.2Optimized WinForm main and sub threads.
04/06/2024v5.2Revisited Epplus GNU LGDL. Decided to document the changes on Epplus. So far only 1 change was made.
04/16/2024v5.2Added function for the 4th field of command GetFrom. When this 4th field is present, it serves as the key to locate the source record from which the field value by the name of 3rd field is copied over.
04/30/2024v5.2Tuned and tested the 3 dll commands: eatdll, oopdll and oehdll.
06/07/2024v5.2Added account hierarchical charts for EAT.
06/17/2024v5.2Added 4th field as user key for formula PullFrom.
figure 8001


V. History of Epplus Changes


1. The changes

Date File Line Description
04/01/2024v5.1 na na sample line
04/05/2024ExcelWorksheet.cs4244After a sheet was added to another book, the ConditionalFormatting was not aaded along with. So this 'set' method could allow to set it from source.
04/06/2024ExcelComment.cs92fake entry