RecurringGivingForecast

The RecurringGivingForecast python script produces a report showing the current monthly recurring giving for a specified budget, the projected annual income based on the current level holding steady, and the percent of your budget covered by that projection.

http://i.tpsdb.com/recurringgivingforecast.jpg

Sample RecurringGivingForecast Report

The report can be modified by specifying a fund ID and Name and by entering your annual budget total amount.

Create the RecurringGivingForecast Script

Step 1
Go to Administration > Setup > Special Content and select the Python Scripts tab.
Step 2
Click the green + New Python Script File button and enter the name of the file as RecurringGivingForecast and press Submit.
Step 3
Copy all of the code below and then paste it into the new script file and press the blue Save Python Script button.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
#Roles=Finance

from System.DateTime import Now
budget = 4000000
fundID = 1
fundname = 'Budget/Local Operation'

queries = [
    ["Every month","sum(amt.Amt)","SemiEvery='E' AND EveryN=1 AND Period='M'","SemiEvery,EveryN,Period"]
    ,["Every week","4.345*sum(amt.Amt)","SemiEvery='E' AND EveryN=1 AND Period='W'","SemiEvery,EveryN,Period"]
    ,["Every 2 weeks","2.18*sum(amt.Amt)","SemiEvery='E' AND EveryN=2 AND Period='W'","SemiEvery,EveryN,Period"]
    ,["Semi-Monthly","2*sum(amt.Amt)","SemiEvery='S'","SemiEvery"]
]

mainquery = '''
SELECT {} as MonthlyTotal
    FROM dbo.RecurringAmounts amt
    JOIN dbo.ManagedGiving schedule
    ON amt.peopleid = schedule.peopleid
    WHERE amt.fundid = {}
    AND {}
    GROUP BY {}
'''

grandtotal = 0

for qu in queries:
#    row = Row(qu[0])
    rowcolquery = mainquery.format(qu[1],fundID,qu[2],qu[3])
    data = q.QuerySql(rowcolquery)
    for i in data:
        if i.MonthlyTotal is not None:
            grandtotal += i.MonthlyTotal

Data.grandTotal = '${:,.2f}'.format(grandtotal)
Data.annualTotal = '${:,.2f}'.format(12*grandtotal)
Data.budgetReached = '{:.2f}%'.format(12*100*grandtotal/budget)
Data.fundname = fundname
Data.budget = '${:,}'.format(budget)
template = '''
<script>document.title = "Recurring Giving Forecast"</script>
<h3>Recurring Giving Forecast</h3>
<table class="table" style="width:auto">
    <thead>
    <tr>
        <th>Item</th>
        <th align="right">Amount</th>
    </tr>
    </thead>
    <tr>
        <th>Monthly recurring gifts to {{this.fundname}} fund</th>
            <td align="right">{{this.grandTotal}}</td>
    </tr>
    <tr>
        <th>Projected next 12 months recurring giving (monthly x 12)</th>
            <td align="right">{{this.annualTotal}}</td>
    </tr>
    <tr>
        <th>% Annual budget ({{this.budget}}) covered by projected next 12 months</th>
            <td align="right">{{this.budgetReached}}</td>
    </tr>
</table>
'''
print model.RenderTemplate(template)
Step 4
Modify the script with the target fund and budget information. Enter your budget on line 4 as an unformatted number (no commas or currency symbol). Enter the Fund ID on line 5 and the Fund Name on line 6. Make sure to enclose the Fund Name within single quotes as shown in the sample code.
Step 5
Click the Run Script button at the top of the form. Then, if you want the report available on the Reports Menu, click the link at the bottom of the report - Add Report to Menu.


Latest Update 03/04/2020

Added this new article.

© 2020 TouchPoint Software, LLC.  All rights reserved.