Annotation of pta/pta_import.1, Revision 1.10
1.10 ! freda 1: .\" $Id: pta_import.1,v 1.9 2020/11/28 15:10:36 freda Exp $
1.1 schwarze 2: .\"
3: .\" Copyright (c) 2020 Freda Bundchen
4: .\"
5: .\" Permission to use, copy, modify, and distribute this software for any
6: .\" purpose with or without fee is hereby granted, provided that the above
7: .\" copyright notice and this permission notice appear in all copies.
8: .\"
9: .\" THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
10: .\" WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
11: .\" MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
12: .\" ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
13: .\" WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
14: .\" ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
15: .\" OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
16: .\"
1.10 ! freda 17: .Dd $Mdocdate: November 28 2020 $
1.1 schwarze 18: .Dt PTA_IMPORT 1
19: .Os
20: .Sh NAME
21: .Nm pta_import
22: .Nd print pta journal entries from bank CSV file input
23: .Sh SYNOPSIS
24: .Nm pta_import
1.5 schwarze 25: .Fl I Ar accountname
1.2 schwarze 26: .Op Ar file
1.1 schwarze 27: .Sh DESCRIPTION
28: The
29: .Nm
1.2 schwarze 30: utility converts lines from a CSV
31: .Ar file
32: generated by a financial
1.1 schwarze 33: institution into journal entries used by
34: .Xr pta 1 .
1.2 schwarze 35: .Pp
36: If the
37: .Ar file
38: argument is omitted, standard input is read instead.
1.3 schwarze 39: .Pp
1.5 schwarze 40: .Ar accountname
1.3 schwarze 41: is exactly one of
1.4 schwarze 42: .Pp
43: .Bl -bullet -offset indent -compact
44: .It
1.6 freda 45: .Cm bbva_usa
46: .It
1.9 freda 47: .Cm capital_one_360
48: .It
1.4 schwarze 49: .Cm capital_one_credit
50: .It
51: .Cm chase_credit
52: .It
1.9 freda 53: .Cm etrade_ira
54: .It
1.4 schwarze 55: .Cm optum_hsa
1.6 freda 56: .It
57: .Cm sparkasse_camt
58: .It
59: .Cm wellsfargo
1.3 schwarze 60: .El
61: .Pp
62: and corresponds to an existing
1.8 freda 63: .Pa ./import/ Ns Ar accountname Ns Pa .txt
64: configuration file.
1.5 schwarze 65: .Ss Configuration file format
1.8 freda 66: Each configuration file contains rules
1.7 freda 67: used to import transactions from a financial institution.
1.5 schwarze 68: Like all
69: .Xr pta 1
70: input files, it is an
71: .Xr ascii 7
72: text file using strings of one or more space characters
73: as the field delimiter and using the hash character
74: .Pq Sq #
75: in the first column to mark lines as ignored.
76: .Pp
1.7 freda 77: The rules are as follows:
78: .Bl -tag -width Ds
79: .It Ic ACCOUNT Ar csv_account_number
80: The number in
81: .Xr pta-accounts 5
82: of the bank or credit card account this CSV file contains entries for.
83: This rule is required in every configuration file.
1.10 ! freda 84: .It Ic COSTCENTER Ar costcenter_column
! 85: The column number in the CSV file containing the cost center.
! 86: Numbering starts from 1.
! 87: If this rule is not specified, no cost centers are assigned.
! 88: .It Ic DATE Ar date_column date_format
! 89: The column number in the CSV file containing the date.
! 90: Numbering starts from 1.
! 91: The
! 92: .Ar date_format
! 93: is one of
! 94: .Ic MM/DD/YY ,
! 95: .Ic MM/DD/YYYY ,
! 96: .Ic YYYY-MM-DD ,
! 97: or
! 98: .Ic DD.MM.YY .
! 99: This rule is required in every configuration file.
1.7 freda 100: .It Ic DELIM Ar delimiter
101: The character used to separate fields.
102: This rule is required in every configuration file.
103: Most financial institutions use commas but some use semicolons.
104: .It Ic HEADER Ar header
105: Skip the first line of the CSV file if it matches the
106: .Ar header
107: line verbatim, or cause parsing to fail if it does not.
108: .It Ic IGNORE Ar regular_expression
109: Skip all lines in the CSV file matching the
110: .Ar regular_expression .
1.10 ! freda 111: .It Ic QUANTITY Ar quantity_column
! 112: The column number in the CSV file containing the number of stock shares.
! 113: Numbering starts from 1.
! 114: If this rule is not specified, no information about the number of traded
! 115: securities is appended to the text string.
1.7 freda 116: .It Ic QUOTE Ar quote
117: Expects that every field starts and ends with the
118: .Ar quote
119: character.
120: .It Ar selector account_number booking_identifier
121: Lines in the CSV file that match the
1.5 schwarze 122: .Ar selector
1.7 freda 123: are associated with the contra account
1.5 schwarze 124: .Ar account_number
1.7 freda 125: and are assigned the
126: .Ar booking_identifier .
127: The selector is a string consisting of regular expressions separated
128: by delimiters defined in the
129: .Ic DELIM
130: rule.
131: .El
1.1 schwarze 132: .Sh FILES
1.4 schwarze 133: .Bl -tag -width Ds -compact
1.8 freda 134: .It Pa ./import/bbva_usa.txt
1.9 freda 135: .It Pa ./import/capital_one_360.txt
1.8 freda 136: .It Pa ./import/capital_one_credit.txt
137: .It Pa ./import/chase_credit.txt
1.9 freda 138: .It Pa ./import/etrade_ira.txt
139: .It Pa ./import/optum_hsa.txt
1.8 freda 140: .It Pa ./import/sparkasse_camt.txt
141: .It Pa ./import/wellsfargo.txt
1.1 schwarze 142: .El
1.3 schwarze 143: .Pp
144: These files contain the header that the financial institution uses, the
145: account number from
146: .Pa ./accounts.txt
147: used by this account, as well as patterns used to map transactions to
148: the correct account and booking identifier.
1.1 schwarze 149: .Sh EXIT STATUS
150: .Ex -std
151: .Sh EXAMPLES
152: Add journal entries to a journal file using a CSV file generated by
153: a financial institution:
154: .Pp
1.3 schwarze 155: .Dl "$ pta_import -I chase_credit chasedownload.csv >> journal.txt"
1.5 schwarze 156: .Ss Account numbers in the configuration file
157: The line
1.1 schwarze 158: .Pp
1.5 schwarze 159: .Dl ACCOUNT 3235
1.3 schwarze 160: .Pp
1.5 schwarze 161: in the configuration file corresponds to a line such as
1.3 schwarze 162: .Pp
1.5 schwarze 163: .Dl 3235 L Capital One credit card
1.3 schwarze 164: .Pp
165: in
1.5 schwarze 166: .Pa ./accounts.txt .
1.1 schwarze 167: .Pp
1.3 schwarze 168: The description
169: .Qq Capital One credit card
170: may vary, but in this example, the account numbers
1.5 schwarze 171: .Qq 3235
1.3 schwarze 172: match, and the account type is
173: .Qq L
174: to indicate that it is a liability account.
1.5 schwarze 175: .Ss Patterns in the configuration file
1.3 schwarze 176: Suppose
1.4 schwarze 177: .Pp
1.3 schwarze 178: .Dl ,,,Automotive,,, 1111 AUTO
179: .Pp
180: is a sample entry in the
1.8 freda 181: .Pa ./import/chase_credit.txt
1.3 schwarze 182: file which uses headers of the form
183: .Pp
184: .Dl Transaction Date,Post Date,Description,Category,Type,Amount
185: .Pp
186: Then this
187: .Ar selector
188: ignores the transaction date, post date, description, type, and amount.
189: It matches the
190: .Qq Automotive
191: category and maps transactions to account
192: .Qq 1111
193: defined in
194: .Pa ./accounts.txt
195: with the
196: .Qq Auto
197: booking identifier.
1.5 schwarze 198: .Ss A sample credit card purchase
199: If
200: .Pa chasedownload.csv
201: file contains
202: .Pp
203: .Dl "10/26/2020,10/26/2020,example.com*asldfkj,Shopping,Sale,-17.07"
204: .Pp
205: and
1.8 freda 206: .Pa ./import/chase_credit.txt
1.5 schwarze 207: contains
208: .Bd -literal -offset Ds
209: ACCOUNT 3234
210: ,,,Shopping,,, 9996 SHOP
211: .Ed
212: .Pp
213: and
214: .Pa ./accounts.txt
215: contains
216: .Bd -literal -offset Ds
217: 3234 L Chase credit card
218: 9996 X shopping
219: .Ed
220: .Pp
221: then the
222: .Qq Shopping
223: category defined by Chase is mapped to the user's
224: .Qq SHOP
225: booking identifier, the user's account for shopping
226: expenses is defined as
227: .Qq 9996
228: in their
229: .Pa ./accounts.txt ,
230: the user's account for the credit card is 3234 in their
231: .Pa ./accounts.txt ,
232: and
233: .Nm
234: prints
235: .Pp
236: .Dl "20201026 SHOP 9996 3234 17.07 example.com*asldfkj"
1.3 schwarze 237: .Ss A complete example
238: Suppose
239: .Pa ./accounts.txt
240: is given by
241: .Bd -literal
242: 1700 A My Bank
243: 3234 L My Chase Credit Card
244: 5102 X Physician expenses
245: 5103 X Dental expenses
246: 5203 X Shopping
247: .Ed
1.1 schwarze 248: .Pp
1.3 schwarze 249: and
250: .Pa chasedownload.csv
251: is given by
252: .Bd -literal
253: Transaction Date,Post Date,Description,Category,Type,Amount,Memo
254: 11/06/2020,11/09/2020,Dr. Joseph Schmoe,Health & Wellness,Sale,-100.00,
255: 11/06/2020,11/09/2020,Dr. Sally Sue,Health & Wellness,Sale,-183.00,
256: 11/08/2020,11/08/2020,example.com,Shopping,Sale,-53.83,
257: 11/04/2020,11/04/2020,Payment Thank You - Web,,Payment,75.43,
258: .Ed
1.1 schwarze 259: .Pp
1.3 schwarze 260: and
1.8 freda 261: .Pa ./import/chase_credit.txt
1.3 schwarze 262: is given by
263: .Bd -literal
264: # Chase credit card header is
265: # Transaction Date,Post Date,Description,Category,Type,Amount
266: # The format of this file is
267: # selector account_number booking_identifier
268: # selector is a comma-separated list of regular expressions which
269: # matches corresponding fields in the CSV file
270: ACCOUNT 3234
271: ,,,Automotive,,, 1111 AUTO
272: ,,,Bills & Utilities,,, 2222 BILLS
273: ,,,Fees & Adjustments,,, 3333 FEES
274: ,,,Food & Drink,,, 4444 FOOD
275: ,,,Gas,,, 5555 GAS
276: ,,,Gifts & Donations,,, 6666 GIFTS
277: ,,,Groceries,,, 7777 GROCERIES
278: ,,Dr. Sally Sue,,,, 5103 DENTAL
279: ,,,Health & Wellness,,, 5102 PHYSICIAN
280: ,,,Home,,, 9999 HOME
281: ,,,Personal,,, 9998 PERSONAL
282: ,,,Professional Services,,, 9997 PRO
283: ,,,Shopping,,, 5203 SHOP
284: ,,,,Payment,, 1700 PAY
285: .Ed
1.1 schwarze 286: .Pp
287: Then
1.4 schwarze 288: .Pp
1.3 schwarze 289: .Dl pta_import -I chase_credit chasedownload.csv
1.4 schwarze 290: .Pp
1.3 schwarze 291: outputs
292: .Bd -literal
293: 20201109 PHYSICIAN 5102 3234 100.00 Dr. Joseph Schmoe
294: 20201109 DENTAL 5103 3234 183.00 Dr. Sally Sue
295: 20201108 SHOP 5203 3234 53.83 example.com
296: 20201104 PAY 3234 1700 75.43 Payment Thank You - Web
297: .Ed
1.5 schwarze 298: .Pp
299: Note that the selector for Dr\&. Sally Sue
300: in the example configuration file comes before the
301: Health & Wellness selector.
302: Since the first match wins, transactions for Dr\&. Sally Sue
303: can be classified with the DENTAL booking identifier,
304: even though the bank's CSV file classifies
305: these transactions as Health & Wellness.
1.1 schwarze 306: .Sh SEE ALSO
307: .Xr pta-accounts 5 ,
308: .Xr pta-journal 5 ,
309: .Xr pta-glossary 7
310: .Sh BUGS
311: Currently,
312: .Nm
313: has the following limitations:
314: .Bl -dash
315: .It
1.3 schwarze 316: Pattern recognition in the
1.8 freda 317: .Pa import/accountname.txt
1.3 schwarze 318: files is under continued development.
319: .It
1.9 freda 320: User-defined configuration files are not currently
321: supported.
1.1 schwarze 322: .El
CVSweb
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to pta_import.1 CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [cvsweb.bsd.lv] / pta