This document shows the detailed procedures for querying cash projection data from CS Lucas to Excel worksheet.
WHY IS THIS IMPORTANT?
This Excel cash worksheet is built to allow flexibility for users to include any ad-hoc or other known/ planned items by adding rows to the cash worksheet to aid in their cash planning. This worksheet can also be saved for record and version tracking purposes.
1. Save a copy of Cash_Worksheet_V1.xlsm on your computer.
2. Open Cash_Worksheet_V1.xlsm and change to correct URL for the data fetch.
Click on View > Macros > View Macros
You will see a list of macros that you can work with.
Select JSONGet and click Edit.
Change the URL highlighted in the screen shot above, i.e. https://demo.cslucas.com, to the server’s URL you wish to connect to.
Then, save the Cash_Worksheet_V1.xlsm.
Click File > Save or press Ctrl+S.
3. Multi-factor authentication (MFA) is required to query data from CS Lucas to cash worksheet. Click here to see how to set up MFA for your user id in CS Lucas.
1. Open Cash_Worksheet_V1.xlsm.
(1) If you see a prompt on PROTECTED VIEW, click Enable Editing.
(2) If you see a prompt on SECURITY WARNING, click Enable Content.
2. This worksheet comprises of previous day closing balance, treasury transactions to be booked, treasury transactions booked, operational cash flow forecast, ad-hoc cash flow item and the expected closing bank balance.
3. To query the data from CS Lucas, click on Query button.
4. You will be asked to enter user ID and 2FA code for authentication and the report parameters.
Please see below explanation on how you can enter for each field.
|User ID||Enter your user id to access CS Lucas. This is case sensitive.|
|2FA Code||Enter the multi-factor authentication code generated from Google Authenticator.|
|Acct Cntr||Enter the accounting centre short name as per set up in CS Lucas. This is case sensitive.|
|SSI Group||Enter the SSI short name or SSI Group short name as per set up in CS Lucas. This is case sensitive.|
|From Date||Enter the reporting from date in DD-MMM-YYYY format.|
|To Date||Enter the reporting to date in DD-MMM-YYYY format.|
|Report Ccy||Enter the currency short name as per set up in CS Lucas. This is case sensitive.|
Note: Please ensure the values entered are valid for system to pull the data out in this cash worksheet. There is no validation check on the values entered.
5. Then, click on Refresh button.
6. Wait a little while for the cash worksheet to populate the data.
7. Once it is done, you will see the accounting centre long name appears in the cash worksheet and also the date and timestamp of when the report is extracted.
8. The following explains the data pulled for each section.
|Previous Closing Bank Balance||This shows the closing balance for reporting from date -1 day from the Actual Cash screen.|
|Treasury transactions to be booked||This shows all portfolio transaction activities that have yet to be booked in the reporting date range. For example, term loan periodic repayment.|
|Treasury transactions booked||This shows all portfolio transaction activities that are booked in the reporting date range.|
|Operational cash flow forecast||This shows forecast from “All Forecast” group that are maintained for “-DEFAULT-” cycle and “-DEFAULT-” source in the reporting date range.|
|Ad hoc||Users can include any ad-hoc items in the closing balance calculation.|
9. If you wish to include any other ad-hoc or known/planned items in the closing balance calculation, simply add a row item under the Ad hoc section.
FREQUENTLY ASKED QUESTIONS