Created
May 11, 2023 08:11
-
-
Save dharmatech/a781d4c1c2666cb5e1347f8e61c676cd to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $base = 'https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/dts' | |
| $rest_url = '/dts_table_2?filter=record_date:gte:{0},transaction_catg:eq:{1}&fields=record_date,transaction_type,transaction_catg,transaction_today_amt&page[number]=1&page[size]=300' | |
| $date = '2023-01-01' | |
| # ---------------------------------------------------------------------- | |
| # https://www.opm.gov/policy-data-oversight/pay-leave/federal-holidays/#url=2023 | |
| $holidays = @( | |
| Get-Date "Monday, January 02" -Format 'yyyy-MM-dd' # New Year’s Day | |
| Get-Date "Monday, January 16" -Format 'yyyy-MM-dd' # Birthday of Martin Luther King, Jr. | |
| Get-Date "Monday, February 20" -Format 'yyyy-MM-dd' # Washington’s Birthday | |
| Get-Date "Monday, May 29" -Format 'yyyy-MM-dd' # Memorial Day | |
| Get-Date "Monday, June 19" -Format 'yyyy-MM-dd' # Juneteenth National Independence Day | |
| Get-Date "Tuesday, July 04" -Format 'yyyy-MM-dd' # Independence Day | |
| Get-Date "Monday, September 04" -Format 'yyyy-MM-dd' # Labor Day | |
| Get-Date "Monday, October 09" -Format 'yyyy-MM-dd' # Columbus Day | |
| Get-Date "Friday, November 10" -Format 'yyyy-MM-dd' # Veterans Day | |
| Get-Date "Thursday, November 23" -Format 'yyyy-MM-dd' # Thanksgiving Day | |
| Get-Date "Monday, December 25" -Format 'yyyy-MM-dd' # Christmas Day | |
| ) | |
| # ---------------------------------------------------------------------- | |
| $start_date = '2023-05-05' | |
| $end_date = '2023-07-01' | |
| # ---------------------------------------------------------------------- | |
| # SSA - Benefits Payments | |
| # Rule: | |
| # Each month on the 3rd of the month, and then the second, third, and fourth Wed of the month 24-26b of SSA benefits go out. | |
| # If the 3rd falls on a Saturday or Sunday, payment goes out the Friday before | |
| $result_ssa = Invoke-RestMethod -Method Get -Uri ($base + $rest_url -f $date, 'SSA - Benefits Payments') | |
| # ---------------------------------------------------------------------- | |
| $reference_start_date = '2023-04-01' | |
| $reference_end_date = '2023-04-30' | |
| # ---------------------------------------------------------------------- | |
| $avg_big = $result_ssa.data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -gt 10000 } | | |
| ? transaction_type -EQ 'Withdrawals' | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| $avg_small = $result_ssa.data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -lt 10000 } | | |
| ? transaction_type -EQ 'Withdrawals' | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| # ---------------------------------------------------------------------- | |
| $eval_date = $start_date | |
| $rows = while ($eval_date -le '2023-07-01') | |
| { | |
| $date = Get-Date $eval_date | |
| $val = if ($date.Day -eq 3 -and $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.Day -in 1, 2 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.Day -in 8..28 -and $date.DayOfWeek -eq 'Wednesday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.Day -in 7..27 -and $date.DayOfWeek -eq 'Tuesday' -and $date.AddDays(1).ToString('yyyy-MM-dd') -in $holidays) { $avg_big } | |
| elseif ( $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_small } | |
| if ($val -ne $null) | |
| { | |
| [PSCustomObject]@{ date = $eval_date; transaction_type = 'Withdrawals'; transaction_catg = 'SSA - Benefits Payments'; val = $val } | |
| } | |
| $eval_date = $date.AddDays(1).ToString('yyyy-MM-dd') | |
| } | |
| $rows | Select-Object *, @{ Label = 'day_of_week'; Expression = { (Get-Date $_.date).DayOfWeek } } | ft * | |
| # ---------------------------------------------------------------------- | |
| # 'HHS - Federal Supple Med Insr Trust Fund' | |
| # Rule - Big payment on first of the month, if the first of the month is a weekend or holiday, payment comes day before | |
| # Take average of big payments back to first of the year | |
| # Take average of small payments back to first of the year | |
| $result = [ordered]@{} | |
| # $date = '2023-01-01' | |
| # $result['HHS - Federal Supple Med Insr Trust Fund'] = Invoke-RestMethod -Method Get -Uri ($base + $rest_url -f $date, 'HHS - Federal Supple Med Insr Trust Fund') | |
| function get-tga ($date, $category) | |
| { | |
| $result[$category] = Invoke-RestMethod -Method Get -Uri ($base + $rest_url -f $date, $category) | |
| } | |
| get-tga '2023-01-01' 'HHS - Federal Supple Med Insr Trust Fund' | |
| $reference_start_date = '2023-01-01' | |
| $reference_end_date = $start_date | |
| $avg_big = $result.'HHS - Federal Supple Med Insr Trust Fund'.data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -gt 10000 } | | |
| ? transaction_type -EQ 'Withdrawals' | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| $avg_small = $result.'HHS - Federal Supple Med Insr Trust Fund'.data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -lt 10000 } | | |
| ? transaction_type -EQ 'Withdrawals' | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| function compute-average-large ($data, $reference_start_date, $reference_end_date, $threshold, $type) | |
| { | |
| $val = $data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -ge $threshold } | | |
| ? transaction_type -EQ $type | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| [math]::Round($val, 0) | |
| } | |
| function compute-average-small ($data, $reference_start_date, $reference_end_date, $threshold, $type) | |
| { | |
| $val = $data | | |
| ? record_date -GE $reference_start_date | | |
| ? record_date -LE $reference_end_date | | |
| ? { [decimal]$_.transaction_today_amt -lt $threshold } | | |
| ? transaction_type -EQ $type | | |
| Measure-Object -Property transaction_today_amt -Average | | |
| ForEach-Object Average | |
| [math]::Round($val, 0) | |
| } | |
| $avg_big = compute-average-large $result.'HHS - Federal Supple Med Insr Trust Fund'.data $reference_start_date $reference_end_date 10000 'Withdrawals' | |
| $avg_small = compute-average-small $result.'HHS - Federal Supple Med Insr Trust Fund'.data $reference_start_date $reference_end_date 10000 'Withdrawals' | |
| # ---------------------------------------------------------------------- | |
| $eval_date = $start_date | |
| $rows = while ($eval_date -le '2023-07-01') | |
| { | |
| $date = Get-Date $eval_date | |
| $val = if ($date.Day -eq 1 -and $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(1).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(2).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(3).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays -and $date.AddDays(3).ToString('yyyy-MM-dd') -in $holidays) { $avg_big } | |
| elseif ( $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_small } | |
| if ($val -ne $null) | |
| { | |
| [PSCustomObject]@{ date = $eval_date; transaction_type = 'Withdrawals'; transaction_catg = 'HHS - Federal Supple Med Insr Trust Fund'; val = $val } | |
| } | |
| $eval_date = $date.AddDays(1).ToString('yyyy-MM-dd') | |
| } | |
| $rows | Select-Object *, @{ Label = 'day_of_week'; Expression = { (Get-Date $_.date).DayOfWeek } } | ft * | |
| # ---------------------------------------------------------------------- | |
| # 'HHS - Federal Hospital Insr Trust Fund' | |
| # Rule - Big payment on first of the month, if the first of the month is a weekend or holiday, payment comes day before | |
| # Take average of big payments back to first of the year | |
| # Take average of small payments back to first of the year | |
| $reference_start_date = '2023-01-01' | |
| $reference_end_date = $start_date | |
| $date = '2023-01-01' | |
| $category = 'HHS - Federal Hospital Insr Trust Fund' | |
| $result = Invoke-RestMethod -Method Get -Uri ($base + $rest_url -f $date, $category) | |
| $avg_big = compute-average-large $result.data $reference_start_date $reference_end_date 10000 'Withdrawals' | |
| $avg_small = compute-average-small $result.data $reference_start_date $reference_end_date 10000 'Withdrawals' | |
| # ---------------------------------------------------------------------- | |
| $eval_date = $start_date | |
| $rows = while ($eval_date -le '2023-07-01') | |
| { | |
| $date = Get-Date $eval_date | |
| $val = if ($date.Day -eq 1 -and $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(1).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(2).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays) { $avg_big } | |
| elseif ($date.AddDays(3).Day -eq 1 -and $date.DayOfWeek -eq 'Friday' -and $eval_date -notin $holidays -and $date.AddDays(3).ToString('yyyy-MM-dd') -in $holidays) { $avg_big } | |
| elseif ( $date.DayOfWeek -notin 'Saturday', 'Sunday' -and $eval_date -notin $holidays) { $avg_small } | |
| if ($val -ne $null) | |
| { | |
| [PSCustomObject]@{ date = $eval_date; transaction_type = 'Withdrawals'; transaction_catg = $category; val = $val } | |
| } | |
| $eval_date = $date.AddDays(1).ToString('yyyy-MM-dd') | |
| } | |
| $rows | Select-Object *, @{ Label = 'day_of_week'; Expression = { (Get-Date $_.date).DayOfWeek } } | ft * |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment