hey guys, actually I want to import sales and growth data in amibroker , my problem is that I have already used aux1 and aux2 field and want to use sales and growth as another fields to import data so that I can easily have limited symbols. One way I know is to change ticker name to ("ticker"_sales)
and use foreign("ticker"_sales,"1"),but I do not want to use that because I have so many fields that are yet to be imported . Please help me as ASAP
@anubhav as an alternative to the use of "synthetic tickers" you may want to investigate the ODBC AFL plugin.
The document explains how to connect to an instance of the MS SQL Server (Express edition is OK), but to properly use it IMHO you should be already familiar with essential SQL databases/queries.
(the plugin can use any ODBC compliant data source - not only MS SQL Server).
A significant part of the work is preliminary: you have to populate the external database with your fundamentals (if data is formatted correctly in Excel as a table, there are some migration tools to import such data directly into the MS SQL database tables).
This will require a good amount of work: planning the DB, creating queries and stored procedures, plus the Afl formulas to use the odbc AFL plugin.
Moreover, you should also evaluate how to "update/append data" to the external database when you'll get some new data (typically at least quarterly for fundamentals).
But, after all the pieces are in place, everything it is very flexible allowing you to add any extra time series/fundamental data that will be available in your analysis and plots (also allowing complex queries via stored procedures and/or views).
A suggestion: before fully committing to such a solution (and build a sophisticated database, queries, views, SQL scripts, etc.) do a short test using only one or two data tables (ideally with a long history) and see if the functionality and the performances are what you are looking for.
Finally, be sure to address any potential connection issues (due to long inactivity between queries and/or network problems when/if using remote machine servers).
Dear Beppe, I write you for an advice: I'm trying to setup exactly this procedure you were speaking about: I have a MySql database EOD with several fundamental data (12K stocks, 30years, 20 fields). I have connected successfull my database via the ODBC connector and imported in Amibroker but just for the normal field (Close, open, etc, ..) I can't understand how to add the around 20 additional columns that I have in MySql.
I tried using the Custom Queries in Amibroker (adding in the query the columns) but without success: is this the way? to import directly the additional columns in this phase?
Or Do I need to work via AFL formula, so In AFL use the function to connect and get the array (OdbcGetArray) and after store in Amibroker database the new data (with AddColumn maybe?).
Morever I see that Amibroker has also some fundamental data fields that you can retrieve with "GetFnData", and I can import easily via ASCI file but Î cant understand if they are scalar or they can contain all the historical data. Do you think is this a viable/better solution ?
Thank you very much
in general, for auxiliary data, I use the odbcGetArraySQL() function:
connString = "ODBC;DATABASE=ab_extra;DSN=ab_mysql;";
ok = odbcOpenDatabase( connString );
ticker = Name();
query = StrFormat( "SELECT eps, date FROM earnings WHERE Symbol = '%s' ORDER BY date ASC;", ticker );
eps = odbcGetArraySQL( query );
query = StrFormat( "SELECT epsEstimated, date FROM earnings WHERE Symbol = '%s' ORDER BY date ASC;", ticker );
estimated = odbcGetArraySQL( query );
eps_colors = iif( eps >= estimated, colorGreen, colorRed );
Filter = 1;
AddColumn( eps, "eps", 1.2, colorWhite, eps_colors);
AddColumn( estimated, "estimated", 1.2 );
Plot( eps, "Eps", eps_colors );
Plot( estimated, "Estimated", colorBlue, styleDashed );
Title = _N( Title = StrFormat( "{{NAME}} - " + FullName() + " - {{INTERVAL}} {{DATE}}" ) );
The above query eps for the current ticker that I previously imported into a MySQL database (data downloaded from FMP in .csv format to simplify the import in the database).
I see no need to store this data in the AB database if you already have it in the SQL database (except for performance reasons, and in such a case, you must import your extra data via the ASCII wizard or OLE to the free aux fields or into some synthetic symbols).
The "fundamentals" data provided via "GetFnData" are scalars, so their utility is quite limited.
An alternative to explore:
Lately, instead of storing the data in a database, I use Amibroker's ability to directly download the data also in JSON format and parse it (with the Chakra engine) within a JScript code section: it's a little less performant but I don't have to worry about maintaining and updating the SQL database.
Thank you very much for the exhaustive answer, now I got it!
Also very interesting this data provider FMP, I didn't know it, do you know if it's better than bloomberg or can it be solid alternative? Bloo is getting very tight with data download control lately
Thanks
Daniele
@DaseinDDM, regarding FMP, I only used it because it was required by a specific project.
I don't have access to the data from the Bloomberg terminal, but I assume that the data quality is not the same (FMP is more suitable for private customers - as can also be inferred from the subscription price it is definitely on another level).
I can neither recommend nor advise against it because, as mentioned, I limited myself to accessing it on behalf of third parties according to their specifications (which were focused only on earnings).
Considering the low cost, it's probably best to sign up for a one-month subscription and draw your conclusions.
Yes for sure Bloo standard is quite high, but it's good to know lower priced alternative in case.
Thank you very much!
Daniele
