Need help configuring ODBC for separate table for each symbol

Hi,

My data setup is a Postgres DB with separate table for each SYMBOL. In the 'configure' window I am confused on what to set in the 'Table name' field for my case. There is hint that for multiple table use {SYMBOL} tag in table. I fail to understand what this means.

Could anybody please help me on how to configure for my above described case .

NB: Also my current table setup is Time, Open, High, Low, Close, Volume. I can change this model if need be.

Thanks,
AB

{SYMBOL} tag is a special Token. This means that at run-time, AB will replace this token with the actual symbol name to fetch Data from a Table that is named as the Symbol name.

Hi,

I tried to configure ODBC setting as shown in below image:

amibroker

But amibroker is still not loading symbol list from the configured database. My table headers are symb, time, open,high,low,close,volume. My database has 3 tables (for logging purpose) in addition to seperate tables for each symbol.

Please help me with my issue of not getting amibroker to load symbols automatically.

Thanks,
AB

You apparently are mixing up two things. Everyone thinks you want quotations in separate tables (per-symbol), but then you are talking about "symbol list".
This is not how things work.
If you have SEPARATE tables for each symbol, you can't retrieve symbol list ("retrieve all symbols" button) simply because there is NO single table with all symbols.
So if you are using SEPARATE tables for each symbol you have to ADD symbols YOURSELF (Symbol->New) in AmiBroker.

Thankyou @Tomasz and @travick. With the information you shared, I have finally resolved my issue.

For people out there, I am sharing below the ODBC settings which worked for me. Please note that this may not be the best solution but this is what worked for me. My case was a separate table for each scrip.

amibroker%20forum

The "Quotations" query, which is clipped in the attached screenshot, is as follows:
SELECT time, open, high, low, close, volume FROM {SYMBOL} WHERE 1=1 ORDER BY time DESC

Now, I would like to share my journey with development/usage of Amibroker data plugin so far. This is purely novice stuff:

  1. The Beginning: Decided to build a data plugin for amibroker. I have access to both OHLC historical data and live streaming data through WebSockets.

  2. The Journey: Went through amibroker forum, got to know about ADK.
    Note: I have worked in PHP, Java, Python, and Javascript. So C++ was alien to me or at least it wasn't growing on me. Anyhow, I went through sample codes and after a few days, I finally succeeded in getting Amibroker load my data plugin. This data plugin only generates random OHLC data into amibroker (testing phase). So, it didn't have any Database code or WebSockets code.
    Being a lazy fellow and my inherent nature to jump between coding languages, I felt its a lot of heavy lifting to get this data plugin work using C++ and probably weeks of work. I feared I might loose interest in the entire project by then. So to keep my interests piqued, I decided to do something simple no matter how impractical it is to use (read loading time). This took me to ODBC data plugin.

  3. The Oasis: I loaded my historical OHLC data to Postgres DB. After a bit of struggle (which is basically this page), I finally succeeded in loading data to amibroker. Due to some database setting issue, it was taking 15 to 20 seconds to load 40000 bars to Amibroker. I knew something was wrong with DB settings. After many attempts to find a solution online, I decided to try MariaDB and finally got the loading time to 2.5 seconds.
    Now, I have to develop a way to load symbol lists from DB to Amibroker, hopefully with Company Fundamental Data. I am looking at ASCII Importer for this.

  4. The Destination: After this, I will resume my work on ADK data plugin for both real-time and historical data.

Regards,
AB

4 Likes

Hello Everyone,
Aim : To import data to AB from local DB (local server - 127.0.0.1)
Data : Getting from Rest API, Data is Committed at every transaction of input data, for this i used Python to stream Data (working correctly) and db connection close by same program
Reference used : This forum Page
Database used : Postgresql
I try to have single table for single ticker model, so by the previous post as suggested by others i can use {SYMBOL} in table name and use custom SQL Query, To add Symbol, I can add manually (Symbol->New Symbol)
Problem: Data not able to import to AB, attached images for better clarity
1

  1. Connection driver used Postgresql Unicode and connection successful and can see all table in table name dropbox
    2
  2. {SYMBOL} as table name and error is "aa " relation does not exist
QUOTATION SQL QUERY : SELECT SYMBOL,OPEN,HIGH,LOW,CLOSE,VOLUME FROM {SYMBOL} WHERE 1=1 ORDER BY DATE DESC

SYMBOL LIST : SELECT NAME FROM LOG WHERE 1=1 GROUP BY NAME

3
3. When Manually adding the symbol, it cannot find the symbol in DB, but i given {SYMBOL} as table name :roll_eyes:
4
4. Above Image is the DB Schema for Ticker (left side), Log(Symbol table) - Right Side, Bottom right - Committed O,H,L,C,Vol and OI and Bottom Left - Created Tables for each Ticker and a common Symbol Table named as Log

I know i am missing a bit to direct data to AB, tried really hard to come here, read all various forums, Please AB Mentors Guide me for solution to get data from local db to ab

You have defined custom query as SELECT ... FROM {SYMBOL} ... and the {SYMBOL} gets replaced by current symbol so you end up with SELECT ... FROM AA .... And your database does not have such table. You need to have the table that has name "AA".

1 Like

I have not entered a ticker as 'aa',

But the current symbol is 'JUSTDIAL', as you can see in 3rd image and JUSTDIAL table exist (4th image)

If you see 3rd image, ab does not pick up the columns of the table,
error :" column 'symbol' does not exist" ,
but table schema has a column symbol.

to reduce the complexity if I remove symbol from quotations query . It does not recognize any columns of table like open, high , low... Any way.

All transactions are committed in db, that's is also checked
Thanks for quick reply, I am also checking what I have missed out :slight_smile:

Your queries are wrong. CAREFULLY Read the manual http://www.amibroker.com/odbc.html Everything is written there. One must however READ it carefully.

It says:

The query needs to return the recordset that contains all price fields defined above (SYMBOL, DATA, OPEN, HIGH, LOW, CLOSE and VOLUME) for given ticker. The recordset must be sorted in DESCENDING date order. Default query (for mySQL database) used is:
SELECT SYMBOL, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME FROM WHERE SYMBOL='{SYMBOL}' ORDER BY DATE DESC

Your query does not include date/time column which is obviously incorrect.

1 Like

@raaghulr I too have struggled to get Amibroker to correctly fetch data and I have succeeded at it.
Following are my observations/tips based on your settings:

  1. In your 4th image, JUSTDIAL is selected but table data shows ACC in symbol name.

  2. With regard to 2nd image, enter field names as I have in my post above. Make sure it reflects your column names.

  3. Modify your 1st SQL query to the following:

  • SELECT time, open, high, low, close, volume FROM {SYMBOL} WHERE 1=1 ORDER BY time DESC

NB: Close charts open in amibroker. Make sure no SYMBOLS are selected.

Please try the above and let me know.

1 Like

@Tomasz @ansarb Thanks you both for your valuable suggestions, I had date column in table ,but missed out in the select quotation query, that caused the error.
Finally with both of your help, I too got it working.. :smile:
Cheers

1 Like