import pandas as pd
import os  # Make sure to import the os module

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

urls = [
"https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.AE+AR+AT+AU+BE+BR+CA+CH+CL+CN+CZ+DE+DK+EE+ES+FI+FR+GB+GR+HK+HR+HU+ID+IE+IL+IN+IT+JP+KR+LT+LU+LV+MK+MX+MY+NL+NO+NZ+PE+PH+PL+PT+US+SA+SE+SG+SI+SK+TH+TR+XM+ZA.B.USD+XDF_R_B1GQ._Z.B?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.AE.B.XDC.AED.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.AR.B.XDC.ARS.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.AT+BE+DE+EE+ES+FI+FR+GR+IE+IT+LT+LU+LV+NL+PT+SI+SK+XM.B.XDC.EUR.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.AU.B.XDC.AUD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.BR.B.XDC.BRL.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.CA.B.XDC.CAD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.CH.B.XDC.CHF.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.CL.B.XDC.CLP.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.CN.B.XDC.CNY.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.CZ.B.XDC.CZK.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.DK.B.XDC.DKK.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.HK.B.XDC.HKD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.HR.B.XDC.HRK.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.HU.B.XDC.HUF.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.ID.B.XDC.IDR.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.IL.B.XDC.ILS.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.IN.B.XDC.INR.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.JP.B.XDC.JPY.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.KR.B.XDC.KRW.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.MK.B.XDC.MKD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.MX.B.XDC.MXN.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.MY.B.XDC.MYR.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.NO.B.XDC.NOK.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.NZ.B.XDC.NZD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.PE.B.XDC.PEN.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.PH.B.XDC.PHP.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.PL.B.XDC.PLN.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.SA.B.XDC.SAR.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.SE.B.XDC.SEK.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.SG.B.XDC.SGD.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.TH.B.XDC.THB.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.TR.B.XDC.TRY.B+N?format=csv","https://stats.bis.org/api/v2/data/dataflow/BIS/WS_CBTA/1.0/Q.ZA.B.XDC.ZAR.B+N?format=csv"
]

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.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.dta