Delete Optimization Run

PHOTO

Tue Apr 12 2022 19:07:36 GMT+0000 (Coordinated Universal Time)

Saved by @cvdubs #python #sql

"""A sciprt to drop optimization runs based on a run_date"""
import os
from ams.db import sql_execute

connection_string = os.environ['DB_A']

uk_models = [
    'UKGR',
    'UKCO',
    'UKCB',
    'UKBG',
    'UKBAL'
]

uk_models_policies = [
    'UKGR',
    'UKCO',
    'UKCB',
    'UKBG',
    'UKBAL',
    'PUKBAL',
    'PUKBG',
    'PUKCB',
    'PUKCO',
    'PUKGR'
]

uk_asset_classes = [
    'EMD_GBP',
    'UK_LARGE',
    'UK_MID',
    'UK_GOV',
    'US_AGG_GBP_H',
    'US_SHORT_GBP',
    'EUR_AGG_XUK_GBP_H',
    'PACIFIC_XJP_GBP',
    'EM_GBP',
    'US_HY_GBP_H',
    'US_LC_GBP',
    'UK_SHORT_GOV',
    'US_SHORT_GBP_H',
    'UK_CORP',
    'US_SMALL_GBP',
    'EUR_AGG_XUK_GBP',
    'EMD_GBP_H',
    'UK_CASH',
    'EUR_XUK_GBP',
    'JAPAN_GBP',
    'US_HY_GBP',
    'UK_LINK',
    'UK_SHORT_CORP',
    'US_AGG_GBP'
]

muni_models = [
    'HBGMN',
    'HBGMT',
    'HBMN',
    'HBMT',
    'HCBMN',
    'HCBMT',
    'HCMN',
    'HCMT'
]

muni_models_policies = [
    'HBGMN',
    'HBGMT',
    'HBMN',
    'HBMT',
    'HCBMN',
    'HCBMT',
    'HCMN',
    'HCMT',
    'PMUNBA',
    'PMUNBG',
    'PMUNCB',
    'PMUNCO'
]

muni_asset_classes = [
    'NT_MUNI',
    'MUNI_AA',
    'MUNI_BB',
    'MUNI_B',
    'MUNI_CCC',
    'EAFE_AC_MUNI',
    'US_LC_MUNI',
    'US_DEF_MUNI',
    'US_SMID_MUNI',
    'EAFE_MUNI',
    'EAFE_SC_MUNI',
    'EM_MUNI',
    'EMD_MUNI',
    'INTL_AGG_MUNI',
    'EMLC_MUNI',
    'CASH_MUNI',
    'MA_MUNI',
    'MF_MUNI',
    'US_LONG_MUNI',
    'US_MID_MUNI',
    'US_SMALL_MUNI',
    'MUNI',
    'MUNI_HY',
    'MUNI_SHORT',
    'MUNI_AAA',
    'MUNI_A',
    'MUNI_BBB'
]

uk_models = tuple(uk_models)
uk_models_policies = tuple(uk_models_policies)
uk_asset_classes = tuple(uk_asset_classes)
muni_models = tuple(muni_models)
muni_models_policies = tuple(muni_models_policies)
muni_asset_classes = tuple(muni_asset_classes)

def delete_fi_cmas(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(
        connection_string,
        f"""DELETE FROM ams_equilibrium_cma WHERE run_date = '{run_date}'"""
        )
    sql_execute(
        connection_string,
        f"""DELETE FROM ams_fi_cma WHERE run_date = '{run_date}'"""
        )
    sql_execute(
        connection_string,
        f"""DELETE FROM ams_fi_inputs WHERE run_date = '{run_date}'"""
        )


def delete_pre_opt(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_asset_class_stats WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_distributions WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_matrices WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_model_stats WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_scenarios WHERE run_date = '{run_date}'""")


def delete_opt(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_account WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_policy WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_account WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_policy WHERE run_date = '{run_date}'""")


def delete_opt_to(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_weights_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_stats_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_weights_acct WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_stats_acct WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_weights_pol WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_to_stats_pol WHERE run_date = '{run_date}'""")


def delete_pre_opt_eqinc(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_eq_inc_matrices WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eq_inc_scenarios WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eq_inc_fund_stats WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eq_inc_coefficients WHERE run_date = '{run_date}'""")


def delete_opt_eqinc(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_eqinc_ax_weights_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eqinc_ax_stats_rebal WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eqinc_ax_weights_account WHERE run_date = '{run_date}'""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_eqinc_ax_stats_account WHERE run_date = '{run_date}'""")


def delete_opt_uk(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_account 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_policy 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_rebal 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_rebal 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_account 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_policy 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models)}""")


def delete_pre_opt_uk(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_asset_class_stats 
                WHERE run_date = '{run_date}' 
                AND asset_class in 
                {repr(uk_asset_classes)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_distributions 
                WHERE run_date = '{run_date}' 
                AND asset_class in 
                {repr(uk_asset_classes)}""")
    sql_execute(connection_string,
    f"""DELETE FROM ams_matrices 
        WHERE run_date = '{run_date}' 
        AND (asset_class_1 in 
        {repr(uk_asset_classes)} 
        OR asset_class_2 in 
        {repr(uk_asset_classes)})""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_model_stats 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(uk_models_policies)}""")
    sql_execute(connection_string,
        f"""DELETE FROM ams_scenarios 
        WHERE run_date = '{run_date}' 
        AND asset_class in 
        {repr(uk_asset_classes)}""")

def delete_opt_muni(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_account 
                WHERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_policy 
                WHERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_weights_rebal 
                WHERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_rebal W
                HERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_account 
                WHERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_axioma_stats_policy 
                WHERE run_date = '{run_date}' 
                AND model_code in {repr(muni_models)}""")


def delete_pre_opt_muni(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_asset_class_stats 
                WHERE run_date = '{run_date}' 
                AND asset_class in 
                {repr(muni_asset_classes)}""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_distributions 
                WHERE run_date = '{run_date}' 
                AND asset_class in 
                {repr(muni_asset_classes)}""")
    sql_execute(connection_string, f"""DELETE FROM ams_matrices 
        WHERE run_date = '{run_date}' 
        AND (asset_class_1 in 
        {repr(muni_asset_classes)}
        OR asset_class_2 in 
        {repr(muni_asset_classes)})""")
    sql_execute(connection_string,
                f"""DELETE FROM ams_model_stats 
                WHERE run_date = '{run_date}' 
                AND model_code in 
                {repr(muni_models_policies)}""")
    sql_execute(connection_string, f"""DELETE FROM ams_scenarios 
        WHERE run_date = '{run_date}' 
        AND asset_class in 
        {repr(muni_asset_classes)}""")


def delete_raw_returns(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_raw_returns WHERE time_series_date = '{run_date}'""")


def delete_fundamentals(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_fundamentals WHERE run_date = '{run_date}'""")


def delete_equilibrium(run_date):
    """
    This function deletes SQL output from the script in the function name for a given run_date

    Args:
    run_date (str): The date to delete from the SQL tables
    """
    sql_execute(connection_string,
                f"""DELETE FROM ams_equilibrium_cma WHERE run_date = '{run_date}'""")


def delete_optimization_output(script, run_date):
    """
    This function deletes SQL output from the optimization script runs for a given run_date.
    List of Scripts [delete_raw_returns, delete_fundamentals, delete_equilibrium, delete_fi_cmas, 
    delete_pre_opt, delete_opt, delete_opt_to, delete_pre_opt_eqinc, delete_opt_eqinc, delete_opt_uk,
    delete_pre_opt_uk, delete_opt_muni, delete_pre_opt_muni]

    Args:
    script (str): The script that was run to delete from the SQL tables
    run_date (str): The date to delete from the SQL tables
    """
    run_funcs = {
        'delete_raw_returns', 
        'delete_fundamentals', 
        'delete_equilibrium', 
        'delete_fi_cmas', 
        'delete_pre_opt', 
        'delete_opt', 
        'delete_opt_to', 
        'delete_pre_opt_eqinc', 
        'delete_opt_eqinc', 
        'delete_opt_uk',
        'delete_pre_opt_uk', 
        'delete_opt_muni', 
        'delete_pre_opt_muni'
        }
    assert script in run_funcs, 'script not in dictionary'
    func_return = globals()[script](run_date)
    return func_return


DELETE_DATE = '30-SEP-2023'
delete_optimization_output('delete_opt', DELETE_DATE)

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

To Share

@cvdubs

Delete Optimization Run

Importing images from a directory (Python) to list or dictionary

from PIL import Image
import glob
image_list = []
for filename in glob.glob('yourpath/*.gif'): #assuming gif
    im=Image.open(filename)
    image_list.append(im)

python - Find out the percentage of missing values in each column in the given dataset - Stack Overflow

percent_missing = df.isnull().sum() * 100 / len(df)
missing_value_df = pd.DataFrame({'column_name': df.columns,
                                 'percent_missing': percent_missing})

#python #python #loops #whileloop

Print the name of 7 days in a week - by using while loop

days = 0
week = [‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’, ‘Saturday’, 3.‘Sunday’]
while day < 7:
print(“Today is” + week[days])
days += 1

Getting the index of an item in a list containing it in Python

>>> ["foo", "bar", "baz"].index("bar")
1

#python #python #strings #vowels #function

Get vowels in strings

This method gets vowels (‘a’, ‘e’, ‘i’, ‘o’, ‘u’) found in a string.
   
#make a function:
def get_vowels(string):

#return is the keyword which means function have to return value: 
 return [each for each in string if each in 'aeiou']


#assign the words and function will return vowels words.
get_vowels('foobar') # ['o', 'o', 'a']


get_vowels('gym') # []

Could not build wheels for tokenizers which use PEP 517 and cannot be installed directly

https://github.com/pydata/bottleneck/issues/281

How To Bypass Cloudflare Bot Protection In Selenium - CodingTutz

options = webdriver.ChromeOptions()
options.add_experimental_option("excludeSwitches", ["enable-automation"])
options.add_experimental_option('useAutomationExtension', False)
options.add_argument("--disable-blink-features=AutomationControlled")
driver = webdriver.Chrome(options=options)

#javascript #python #search #historicalcode #google #algorithms

Google’s PageRank Algorithm from 1996 - the origin of internet search

import numpy as np

def pagerank(M, num_iterations=100, d=0.85):
    N = M.shape[1]
    v = np.random.rand(N, 1)
    v = v / np.linalg.norm(v, 1)
    iteration = 0
    while iteration < num_iterations:
        iteration += 1
        v = d * np.matmul(M, v) + (1 - d) / N
    return v

Python Loop through Excel sheets, place into one df - Stack Overflow

import pandas as pd

sheets_dict = pd.read_excel('Book1.xlsx', sheetname=None)

full_table = pd.DataFrame()
for name, sheet in sheets_dict.items():
    sheet['sheet'] = name
    sheet = sheet.rename(columns=lambda x: x.split('\n')[-1])
    full_table = full_table.append(sheet)

full_table.reset_index(inplace=True, drop=True)

print full_table

#python #dates #functions #python3.8

How to parse a String into Datetime in Python

from datetime import datetime

datetime_object = datetime.strptime('Jun 1 2005  1:33PM', '%b %d %Y %I:%M%p')

python - Way to change Google Chrome user agent in Selenium? - Stack Overflow

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from fake_useragent import UserAgent

options = Options()
ua = UserAgent()
userAgent = ua.random
print(userAgent)
options.add_argument(f'user-agent={userAgent}')
driver = webdriver.Chrome(chrome_options=options, executable_path=r'C:\WebDrivers\ChromeDriver\chromedriver_win32\chromedriver.exe')
driver.get("https://www.google.co.in")
driver.quit()

python - How to see the progress bar of read_csv - Stack Overflow

def read_csv_pgbar(csv_path, chunksize, usecols, dtype=object):


    # print('Getting row count of csv file')

    rows = sum(1 for _ in open(csv_path, 'r')) - 1 # minus the header
    # chunks = rows//chunksize + 1
    # print('Reading csv file')
    chunk_list = []

    with tqdm(total=rows, desc='Rows read: ') as bar:
        for chunk in pd.read_csv(csv_path, chunksize=chunksize, usecols=usecols, dtype=dtype):
            chunk_list.append(chunk)
            bar.update(len(chunk))

    df = pd.concat((f for f in chunk_list), axis=0)
    print('Finish reading csv file')

    return df

#python #python #lists #dictionary

Convert two lists into a dictionary

keys, values)) # {'a': 2, 'c': 4, 'b': 3}
 
 
#make a function: def is the keyword for the function:
def to_dictionary(keys, values):
 
 
#return is the keyword that tells program that function has to return value   
return dict(zip(keys, values))
 
  
 
# keys and values are the lists:
 
keys = ["a", "b", "c"]   
 
values = [2, 3, 4]

Convert string to Enum in Python - Stack Overflow

>>> from enum import Enum
>>> class Build(Enum):
...   debug = 200
...   build = 400
... 

Build['debug']

Raising errors in Python

my_list = [27, 5, 9, 6, 8]

def RemoveValue(myVal):
    if myVal not in my_list:
        raise ValueError("Value must be in the given list")
    else:
        my_list.remove(myVal)
    return my_list

print(RemoveValue(27))
print(RemoveValue(27))

Browse more snippets >>