Question about importing data with Import Wizard that is not OHLC

I have read through the documentation regarding the Import Wizard and I regularly use it to download the TICKERS ONLY (column 1) from this spreadsheet into a watchlist. I want to look at the possibility of importing the earnings surprise columns and weighted alpha column as well. My concerns are basically 1) doing so will not mess up any of my OHLC data, 2) once uploaded how is this data accessible (or visible) on screen 3) once I figure out the previous concerns, would I end up needing to use staticvars to get it visible in my interpretation window or some other method?

I just don't want to screw anything up in the process. I want to be able to make this data usable at some point possibly in a ranking process or just as a quick research tool. Thanks ahead of time for any input you might have to offer!



If you are using a local database, you should have Aux1 and Aux2 as columns to import data into.

These may work for your listed data.

As always, BACKUP your Database before you do any testing.

When data is imported, it is accessible by direct reference, just like OHLC.

Hope this helps.

1 Like

I haven't tried using Aux yet just because I want to make sure I research this thoroughly first before doing so. It looks like this might be the easiest way to go. Not 100% sure this will help however since we are limited to only two AUX when I need 4-5. Will keep researching!

If you need more than two fields, then the easiest thing to do it to create an "artificial ticker symbol" to hold data related to a particular symbol. Usually this is done by using some standard prefix and the symbol name. For example, if your prefix is ~MyStuff, then you might end up with new symbols named ~MyStuffAAPL, ~MyStuffMSFT, etc.

Now you can map your new data into any of the eight available database fields: O, H, L, C, Vol, OI, Aux1 and Aux2


A solution similar to the one suggested by @mradtke is included in the knowledge base,

Also while Amibroker only has two AUX columns they exist on every every bar giving potentially unlimited fields you can enter data into. You could for example load the earnings surprise into the AUX field on the bar dated the day it was released or the last bar of the month or quarter which would allow you to keep more than just the last four quarters of data if you wanted.

You probably need a loop to extract the AUX data or use the Ref function if you know which bar holds the data.

1 Like

@MCassICT, using OLE, there is also the possibility to read the data from your Excel file directly.

If employed in complex explorations, it will be noticeable slower than the previous methods, but this comes with the advantage to avoid the steps to import data, locating the row associated to a ticker with an Excel function and extracting the value from the desired column cell. This also allows to retrieve content from cells with text (non-numeric data) like notes, etc.

Obviously it entails the disadvantage of having to always have the Excel sheet available...

1 Like

Well I finally found time to get back to this and decided to just do it as simple as I could. All I want to do is add the Weighted Alpha to Aux1 and the Surprise % to Aux2 and then be able to see the info in the interpretation window. Have run into a slight issue though and not sure why. Here is the code and then the output....

WeightedAlpha = Aux1;
QtrSurprise = Aux2;

printf("Weighted Alpha:			"			+ WeightedAlpha + "\n");
printf("Surprise %%:			"				+ QtrSurprise + "\n");

Screen Shot 2020-06-21 at 9.19.58 PM

It seems to be using the Close information for the Weighted Alpha and then not pulling the Surprise%. Any ideas?

Go to Symbol->Quote Editor to see what you really have in your database. Also, importer by default would reject importing records with close price == 0.
Everything is explained in the knowledge base: You need to follow carefully as details matter.


I must really be missing something Tomasz, even after reading the link you provided. I have tried it several ways and see no changes, it is still filling Aux1 with the Closing price and nothing in Aux 2. Here are the last settings I attempted as well as the result....Thanks for your help by the way! (i have also tried it with selecting "No Quotation Data" in the Wizard)

Screen Shot 2020-06-22 at 10.00.32 PM

Screen Shot 2020-06-22 at 10.07.22 PM

Screen Shot 2020-06-22 at 9.59.53 PM


have you seen that your file does not have any date column?
So where do you think AB is supposed to import that data of your file? What timestamp to align to if there is not any timestamps data in your file?




Good call @fxshrat ! That does make sense. I hadnt considered that as a possibility. So considering the "Surprise%" would only be updated quarterly, how and where could something like that be stored and recalled? I guess maybe I need to look into and understand how Amibroker handles fundamental data. I hadnt really thought about that to be honest. Looks like its back to the drawing board on this one. I appreciate the input!

You can still add the data to Aux1 and Aux2 every quarter but you have to add Date column yourself before. It takes just few seconds and considering that the data is updated just every quarter but not every second or minute it is not big of a deal in addition.

So open the file in Excel and add a date that exists in DB to 2nd column of file (I used last date of your first post's picture but you can use other existing one). Then save and import it (with correct import format).


PS: In case anyone is wondering... I have not transcribed the data of your first picture manually by hand but have used OCR to copy the data of that picture. Takes just few seconds. Would have been insane idea to transcribe it by hand. We are living in year 2020 (but not in caves anymore). :grin:


Excellent! And thanks for mentioning OCR...thats great to know.

Well your advice did help. I added the DATE just as you suggested and it did cause it to correctly bring in the desired data for Weighted Alpha into AUX1. However, for some reason, AUX2 it is now populating with the Close and not SURPRISE%. This is interesting because before, the Close was populating AUX1 and AUX2 was 0 throughout (before adding the date).

It is not interesting but a consequence of incorrect/undesired import format.
Please take a look at your original picture. You are setting format two times.

I'm quite certain you are still doing the same (defining import format two times). Just do it one time (and correctly).

That's why I wrote:

I just made a test import with one of your data records and it works




You are absolutely correct in everything you've pointed out. Thank you so much for the assistance. I have done as you directed and at first, I thought it wasn't working properly but then I realized that actually, it is. However, because of the date it is only putting it into that one position in the array. I didn't realize that but it does make sense. If I am going to do this, I would literally need to update it daily just as I do any other data for it to be recalled properly. Without the date, that is when it is filling it the closing price from that day.

Screen Shot 2020-06-25 at 11.00.41 PM

For what it is worth you can "keep" non-zero values for all bars with zeros using simple code:

HoldNonZeroAux2Value = ValueWhen( Aux2 != 0, Aux2 ); 

I switched from Norgate Premium to Norgate Data as my data provider and they use the Aux1 & Aux2 fields so I had to find alternative methods.

While the Import Wizard drop downs allows you to import OHLC etc the Import ASCII option allows you to import other fields including fundamental data which is visible in the Information window and can be included in AFL using the GetFnData function.

If you only have one value per symbol to save you could hijack one or more of the fundamental fields to store your data. For example I was able to import my target price and overwrite the one provided by Norgate.

1 Like

Thanks for that info @BoNeZ that may come in handy at some point. I have been considering Norgate for some time and am getting closer to pulling the trigger on it. That said, I will still want to be able to access this particular information as well. I would actually like to access all of the Quarterly Surprise data as shown in the OP and had considered using synthetic tickers to do so but that seems like a lot of work that I dont have time for. I would also like to find a way at some point to try and figure out to write a program to download this info from the website and into Amibroker using a batch file. So many things I would like to do but finding the time to learn it and then doing it takes awhile. I will keep that in mind though!

Thanks @Tomasz I appreciate the info!