Free Resources

Before download and use the software, you must agree with the following license terms:

Copyright 2024 Zhao Sang (Ray Sang)

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Notwithstanding the above, redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

• Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

• Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

• Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

Notwithstanding the above, permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Download Link:

zip version (no installation is required)

Windows Installation (64 bit)

Text-based Instructions (Here)

🔧 General Prompt Engineering Tips for FinTech Coding

• Start with context.
  Specify tools/libraries, e.g., “Using pandas and streamlit, write a script that…”
  Helps avoid irrelevant answers or generic implementations.

• Specify input/output formats clearly.
  E.g., “Given two Excel files with 'Customer' and 'Subsidiary' columns, return a reconciled DataFrame showing unmatched rows.” E.g., “Output a table with ['Customer', 'Total Unapplied', 'Write-off Needed'] where write-off = unapplied < $5.”

• State your programming environment.
  If on Mac, NetSuite, or PyCharm, clarify early: “Running this as a .app on MacOS, files are in temp directory—how to persist a .env?”

• Use pseudo-code requests for complex logic.
  E.g., “Before writing code, walk me through a plan in bullets for matching invoice vs. cash receipt data using fuzzy logic.”

• Ask for edge case handling.
  “Please include checks for empty DataFrames and NaN values.”

• Include data samples when feasible.
  “Here’s a sample of the rollforward CSV. Use this format to write the matching logic.”

• Tell it your goal, not just your task.
  “My goal is to automate FX adjustments in AR rollforward. I need the formula to derive 'Local Currency Ending Balance' based on monthly FX rates.”

• Use “Refactor” or “Optimize” prompts for iterative improvements.
  “Refactor this NetSuite Map/Reduce to avoid governance limit breaches.”

• Specify accounting standards or financial logic up front.
  “Under ASC 606, defer revenue when invoice date < delivery date. Write Python code to flag those cases.”

• Define columns and meaning:
  “Column A = Invoice ID, Column B = Invoice Date, Column C = Payment Date.”

• Clarify time logic:
  “Calculate current month revenue by comparing invoice date to a cutoff period (e.g., 4/30/2025).”

• Ask for Enhancements Incrementally

  • “Now add error handling.”

  • “Now sort by ‘Subsidiary’ then ‘Account’.”

  • “Now create a download button in Streamlit.”

  • “Now show unmatched rows in a separate sheet.”

Example Prompt Structure for Accounting Automation

Write a [language/tool] script that:

- Takes input from [Excel/Google Sheet/API]

- Performs logic: [accounting rule or reconciliation logic]

- Produces output: [journal entry/reconciled sheet/report]

Include:

- Edge case handling (e.g., zero values, mismatches)

- Optional: [logging, file export, date filters]

🟨 VBA (Excel Automation)

🔧 Prompt Engineering Tips

• Always state the worksheet names and cell ranges explicitly.
  E.g., “Write VBA to compare Column A in ‘CP BS’ with Column A in ‘AA.1 BS’, and highlight mismatches in red.”

• Clarify event types and control types.
  “Write code in a CommandButton_Click() event to populate an ActiveX ListBox with unmatched values.”

• Mention whether you’re using ActiveX vs Form controls.
  Helps ChatGPT generate correct object references (ListBox1.List vs Me.ListBox1.List).

• Specify if you want a userform, worksheet interaction, or workbook-level automation.

⚙️ Tricks

• Use Application.ScreenUpdating = False in large loops for better performance.

• Use With Sheets("SheetName") blocks to reduce repetition.

• Avoid Select and Activate in generated code—explicit object references are cleaner.

• Ask AI to add error handlers:
  “Wrap in On Error Resume Next and log errors in a separate worksheet.”

🟩 Google Apps Script (Sheets Automation)

🔧 Prompt Engineering Tips

• Clarify authorization and triggers.
  “Write a function triggered on file upload to parse Sheet1 and send an email summary.”

• State target cell locations clearly:
  “Copy values from column A in ‘RawData’ to column D in ‘Consolidated’ if column B = ‘Approved’.”

• Use “Bound Script” or “Container-bound” when relevant.
  Useful for accessing UI or dialog boxes in the attached spreadsheet.

⚙️ Tricks

• Use getRange(row, col, numRows, numCols).getValues() instead of getValue() in loops.

• Use flush() to enforce sync before UI updates or writing large data blocks.

• For large sheets, ask ChatGPT to batch write using 2D arrays instead of writing per cell.

• Leverage ScriptProperties or PropertiesService to persist flags/states.

• Use SpreadsheetApp.getUi() alert() for user prompts when building automation with decision trees.

🐍 Python (with pandas, Streamlit, and automation)

🔧 Prompt Engineering Tips

• Always describe your DataFrame structure.
  “I have a DataFrame with [‘Customer’, ‘Invoice’, ‘Subsidiary’, ‘Amount’]. I want to group by Customer and compare to another DataFrame of Holds.”

• Define logic before asking for code.
  “If ‘OS Balance’ abs() > ‘End Bal’, then use End Bal. Else, use OS Balance * -1.”

• Clarify deployment context:
  “This script runs as a .app on MacOS where files get saved to a temp folder—how do I persist a .env?”

• For Streamlit:
  Specify file inputs, state retention, and export behavior (e.g., “show download buttons that persist after rerun”).

• Use a structure like this:

Step 1: Upload Excel/Pass Google Sheets URL

Step 2: Clean and Import Data

Step 3: Transformation and Manipulation

Step 4: Output Report/Visualization