import os  # Make sure to import the os module

# https://data.bis.org/topics/CBTA

import pandas as pd

urls = ["https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q...USD+XDC+XDF_R_B1GQ?format=csv"]

df = pd.concat([pd.read_csv(url) for url in urls])

dataframes = []

for url in urls:
    try:
        df = pd.read_csv(url)
        dataframes.append(df)
    except Exception as e:
        print(f"Failed to read {url}: {e}")

if dataframes:
    df = pd.concat(dataframes)
    print("Data successfully concatenated.")
    
    # Check and convert string columns to a maximum length of 244
    for col in df.select_dtypes(include=['object']).columns:
        df[col] = df[col].astype(str).str.slice(0, 244)
    
    # Specify the folder where you want to save the file
    folder_path = "C:/Users/jamel/Dropbox/Jupyter"  # Change this to your desired folder path
    
    # Ensure the folder exists
    if not os.path.exists(folder_path):
        os.makedirs(folder_path)
    
    # Full file path
    file_path = os.path.join(folder_path, "concatenated_data_test.dta")
    
    # Attempt to export to Stata
    try:
        df.to_stata(file_path, write_index=False)
        print(f"Data successfully exported to {file_path}")
    except ValueError as e:
        print(f"Error exporting to Stata: {e}")
else:
    print("No data was loaded.")

Data successfully concatenated.
Data successfully exported to C:/Users/jamel/Dropbox/Jupyter\concatenated_data_test.dta