Instuctions for setting up an AmiBroker datasource from stock prices stored in Excel or CSV using ODBC plugin connector. This will automate pricing data update into AmiBroker -- THE PAINLESS WAY.
This method worked for me: AmiBroker Ver 6.40.4, ODBC Plugin 1.7.0
Step 1) Setup a SQ Lite 3 database and connect to AmiBroker
Follow this video https://www.youtube.com/watch?v=c7UWJ0z40kg
Step 2) Convert Excel and Upload data to the SQ Lite 3 database created in Step 1
Follow the python code at the end
Step 3) If Window Keeps Popping Up -- Select Data Source (DSN Name)
Thanks to DingoCC. This post solved the issue, after spending 10 painful hours looking for a solution
The default ODBC Connection String has DSN in Position #2 and Database in Position #3. You have to first connect this way to Select Table_Name. After clicking Retrive all symbols. Move DSN to Position #3. Database must be in Position #2. After swapping the order should be like this.
ODBC; Database=C:\your\db\filepath.db;DSN=name_setup_in_step1.....
This is the default string order.
Python Code for Step 2
import sqlite3
import pandas as pd
import os
def clean_data(file_path, tab_name):
#Change your data cleaning ETL logic
df = pd.read_excel(file_path, sheet_name=tab_name, usecols=list(range(7)))
df.columns = ["Symbol", "Date", "Open", "High", "Low", "Close", "Volume"]
df["Date"] = pd.to_datetime(df["Date"]).dt.strftime("%Y-%m-%d")
return df
def upsert_data(df, conn, table_name):
"""
Upsert function: insert/update rows into SQLite based on Symbol+Date.
Table name is controlled by the table_name parameter.
"""
cursor = conn.cursor()
# Ensure table exists
cursor.execute(f"""
CREATE TABLE IF NOT EXISTS {table_name} (
Symbol TEXT NOT NULL,
Date TEXT NOT NULL,
Open REAL,
High REAL,
Low REAL,
Close REAL,
Volume INTEGER,
PRIMARY KEY (Symbol, Date)
);
""")
# UPSERT statement
sql = f"""
INSERT INTO {table_name} (Symbol, Date, Open, High, Low, Close, Volume)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(Symbol, Date) DO UPDATE SET
Open=excluded.Open,
High=excluded.High,
Low=excluded.Low,
Close=excluded.Close,
Volume=excluded.Volume;
"""
# Efficient batch insert
cursor.executemany(sql, df.values.tolist())
conn.commit()
# -------------------------------
# Example usage with multiple files
# -------------------------------
path = r"C:\Data" #<-----Change file path
files = [
"Excel - PxDnLoad - HK.xlsx",
"Excel - PxDnLoad - TW.xlsx",
"Excel - PxDnLoad - CN.xlsx",
"Excel - PxDnLoad - Index.xlsx"
]
db_path = r"C:\Data\sqlite3.db" #<-----Change sqlite3 database path
table_name = "ohlc_d_1y"
conn = sqlite3.connect(db_path)
for file in files:
excel_file = os.path.join(path, file)
print(f"Processing {excel_file}...")
df = clean_data(excel_file, "OHLC") #<-----Change tab name
# print(df)
upsert_data(df, conn, table_name)
conn.close()
