How to import plain CSV files with Amiquote?

Hello,

New AmiQuote 4.1 ReadMe indicates that now we can use ANY web-based data source if it provides REST-like APIs and CSV
Currently I download Australian Super Fund data (in CSV format) from the website(s) and then import them into the Amibroker database manually.
For instance Core Strategy | Rest Super allows me to download CSV file with daily price history. (please don't confuse REST-like API with the name of the fund manager here)
Capture
The website does not use APIs and there is no login or token required.
Can new Amiquote 4.1 be used to automate this process were plain CSV files can be accessed?

Kind Regards
Richard

Hello

first of all, I never use this website.

But I think is easy to find the proper download link of the file that you interest

All the rest must be easy if you know the structure of the link.

For example, below link is from the file I download, since I try to help you but is not the correct one.

// your proper download link will be something like this line below
https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=1&action=unitprice&dateFrom=03/08/2021&dateTo=13/08/2021&itemId=

image

Thank you PanoS for your reply.
It was a great hint to to get a download link this way.
I am starting to understand how Amiquote Data sources work.

However, I am still struggling to see how define URL template in "Data Source Settings"
To use Amiquite for this type of source will be only possible if the data source supports both " REST-like APIs and CSV" - and being operative word in this case.
Because this website does not have REST-like API I will have to automate it with something else.
Like using Python script or Selenium Web driver for Python. Then I will be able to use AB Import Wizard to import my data. Amiquote takes care of ticker name, while with manual download there is always a problem with mapping of the CSV file name to the ticker name.

Thanks again for your input - it made me understand better how Amiquote works.

Kind Regards
Richard

hello @richpach
If i understood well you already found the download link of your interest.
So can you please copy-paste the URL link here to see how look like? so we can try and we give you the solution.
Sometimes you DONOT need to type the correct ticker name in A.Q to download a CSV file,. At least In my website i download CSV file with daily price history with this trick. i type my name for example and i download the cvs file and automatic import with AmiQuote. So please paste your link here and give us time to try out.

Just a Hint:
Below is an example how to import a csv file from you hard drive

//  where is "symbol" i type the filename of the csv
C:\Temp\{symbol}.csv

Amiquote is very flexible now, special after version 4.00

Hello PanoS,

Here are the links to the CSV files.

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=1&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=1

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=3&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=3

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=4&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=4

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=5&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=5

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=6&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=6

https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=7&action=unitprice&dateFrom=01/01/2020&dateTo=31/08/2021&itemId=7

It seems that the only difference is fund " id " number which I think maps to the file name in REST database.

If I copy them to the Firefox (one by one) I get my downloads but the CSV file names are like this;
Unit_Price_&Crediting_rate_History.csv
Unit_Price
&Crediting_rate_History(1).csv
Unit_Price
&Crediting_rate_History(2).csv
Unit_Price
&Crediting_rate_History(3).csv
Unit_Price
&Crediting_rate_History(4).csv
Unit_Price
&_Crediting_rate_History(5).csv

The actual ticker/fund names are;
CORE
BALANCED
BALANCED-INDEXED
CAPITAL-STABLE
DIVERSIFIED
HIGH-GROWTH

So, this is the second problem Amiquote would need to solve;
Usually after skipping 44 lines price data starts.

|Investment - Date|Core Strategy - Buy|Core Strategy - Sell|Core Strategy - FYTD %|
|31/08/2021 | 2.0946 | 2.0931 | 2.56 |
|28/08/2021 | 2.0917 | 2.0902 | 2.42 |

As you can see there is only Buy and Sell price which I map to Open and Close (no H or L)

I am looking forward to see your solution/approach.

Kind Regards
Richard

1 Like

one more question
i like to know how is the date time is in your PC settings?
for example. my one in Europe i like to see the date in this format 05/09/2011 that means DDMMYYYY

you may find out how look like if you open parameters window in Amibroker

FromDay=ParamDate("Start Date", "01/02/2017",1);	printf( "\n  Start Date =\t" + FromDay );

ok i have to go now i be back at 20:00 London time

i wrote an afl to extract the data by press "Press to Start Download" in parameters window in Amibroke
But before i sent the final project to you, we need to check if this one work at your end

try this sample AFL .

//////////  here is a basic afl code to extract few lines or columns 



PathToSave	= "C:\\temp\\";   		//<<< PATH  to save the file
FileName = "Rest_Com.txt";          // <<< the File name, to save in your hard drive

FromDay=ParamDate("Start Date", "01/09/2021",1);	printf( "\n  Start Date =\t" + FromDay );
ToDay=ParamDate("End Date","03/09/2021",1 );		printf( "\n  End Date =\t" + toDay );



PressToDownload= ParamTrigger( "Press to Start Download", "Press Once");

if( PressToDownload )
{

CORE ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=1&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=1";

    ih = InternetOpenURL( CORE );
    fhLots = fopen( PathToSave + FileName, "a" );

    if( ih && fhLots )
    {
        while( ( str = InternetReadString( ih ) ) != "" )
        {

            if( StrFind( Str, "2021," ) )  // if year found do something
            {
                Date_ = StrExtract( str , 0 );		//_TRACE("#, Year = "+ Date_ );
				Buy_  = StrExtract( str , 1 );
				Sell_ = StrExtract( str , 2 );		// Extract the 3rd column
				Vol_  = StrExtract( str , 3 );
				

                Line = ""+ Date_ +","+ Buy_ +","+ Sell_ +","+ Vol_;
                _TRACE("#, My extracted lines = " +  Line );
                
                fputs( Line, fhLots );	// save the Clean data file txt
            }
        }
        InternetClose( ih );

        if( fhLots ) fclose( fhLots );
    }
}

hello @richpach

Below afl is one way to solve the problem.
Here I use AFL and one Batch file to import the data.

i hope you like it

/**
/// @link https://forum.amibroker.com/t/how-to-import-plain-csv-files-with-amiquote/27363/7

How to use:  
to download the data from internet
1) Open parameters window in Amibroker


To import the data
2) you must Create a Batch file 
3) write the Path to your Batch File 
 "e:\\AmiBroker\\Batch\\BatchFileName.abb", "" );  // << Path to your Batch File 

///////////////////////////////////////////////////////////////
This is the output of this afl: 

$FORMAT Ticker,Date_DMY,Open,Close,Volume
CORE,03/09/2021,2.1012,2.0997,2.89
BALANCED,03/09/2021,4.7099,4.7075,1.93
BALANCEDIDX,03/09/2021,1.3739,1.3728,3.41
CAPITAL_STABLE,03/09/2021,3.7327,3.7308,1.36
DIVERSIFIED,03/09/2021,6.2783,6.2739,2.68
HIGH_GROWTH,03/09/2021,7.3194,7.3143,3.21

Write your PATH 
PathToSave	= "R:\\";   		//<<< PATH  to save the file
FileName = "Rest_Com.txt"; // <<< the File name, to save in your hard drive

*/
_SECTION_BEGIN( "Download From Rest.com.au" );
 
PathToSave	= "R:\\";   		//<<< PATH  to save the file
FileName = "Rest_Com.txt"; // <<< the File name, to save in your hard drive

FromDay=ParamDate("Start Date", "01/09/2021",1);	printf( "\n  Start Date =\t" + FromDay );
ToDay=ParamDate("End Date","03/09/2021",1 );		printf( "\n  End Date =\t" + toDay );


EnableTextOutput(False);
CORE ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=1&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=1";
BALANCED ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=3&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=3";
BALANCEDIDX ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=4&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=4";
CAPITAL_STABLE="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=5&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=5";
DIVERSIFIED ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=6&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=6";
High_GROWTH ="https://rest.com.au/Client/Templates/Rest/InvestmentSection/csvexport/csvexport.aspx?id=7&action=unitprice&dateFrom="+FromDay+"&dateTo="+ToDay+"&itemId=7";
EnableTextOutput(True);

function GetSymbolName( x )
{
    return StrExtract( "CORE,BALANCED,BALANCEDIDX,CAPITAL_STABLE,DIVERSIFIED,HIGH_GROWTH", x );
}


PressToDownload= ParamTrigger( "Press to Start Download", "Press Once");

if( PressToDownload )
{

URLList= CORE +"\n"+BALANCED+"\n"+BALANCEDIDX+"\n"+CAPITAL_STABLE+"\n"+DIVERSIFIED+"\n"+HIGH_GROWTH;

for( i = 0; ( symbol = StrExtract( URLList, i,'\n' ) ) != ""; i++ )
{

    ih = InternetOpenURL( symbol );
    fhLots = fopen( PathToSave + FileName, "a" );

	// below line is Optional, if you dont like the Header just comment out this below line 
	if( fhLots ) 	fputs( "$FORMAT Ticker,Date_DMY,Open,Close,Volume\n", fhLots );

    if( ih && fhLots )
    {
        while( ( str = InternetReadString( ih ) ) != "" )
        {

            if( StrFind( Str, "2022," ) OR StrFind( Str, "2021," ) OR StrFind( Str, "2020," ))  // if year found do something
            {
                Date_ = StrExtract( str , 0 );		//_TRACE("#, Year = "+ Date_ );
				Buy_  = StrExtract( str , 1 );		// Buy, map as Open
				Sell_ = StrExtract( str , 2 );		// Sell, map as Close
				Vol_  = StrExtract( str , 3 );		// Extract the 3rd column
				
                Line = ""+GetSymbolName(i) +"," + Date_ +","+ Buy_ +","+ Sell_ +","+ Vol_ ;
                _TRACE("#, My extracted lines = " +  Line );
                
                fputs( Line, fhLots );	// save the Clean data file txt
            }
        }
        InternetClose( ih );

        if( fhLots ) fclose( fhLots );
    }
   
}
 Say("File is ready",1);
}

_SECTION_END();


///////////////////////////////////////////////////////////////
// Import a CSV file using Batch
////////////////////////////////////////////////////////////// 

 _SECTION_BEGIN( "Import file using Batch" );
// run batch 
if( ParamTrigger( "Import a CSV file with Batch", "Press Once to Import" ) )
{
    ShellExecute( "runbatch", "e:\\AmiBroker\\Batch\\BatchFileName.abb", "" );  // << Path to your Batch File 
}
_SECTION_END();

Now. If you still want to import CSV file that is in your hard drive. Is already covered in AmiQuote readme

Below afl is to add / insert in the bottom of the above code ( but to work you need to read first )


///////////////////////////////////////////////////////////////
// Import a CSV file with AmiQouote
// C:\MyDataFolder\{symbol}.txt
////////////////////////////////////////////////////////////// 


if( ParamTrigger( "AQ Import a CSV file", "Press Once to Import" ) )
{  

    AQ = CreateObject( "AmiQuote.Document" );
    AQ.AddSymbols("Rest_Com");  // "Rest_Com"   << FileName,  your hard drive
    AQ.Source = 16; // <<< change as needed
    //AQ.From = dt_From; //"2018-01-01 00:00:00";
    AQ.To = Now();
    AQ.AutoImport = 1; //False; //True;
    AQ.Download();

   while( AQ.DownloadInProgress() OR AQ.ImportInProgress() )
    {
        ThreadSleep( 20 );  
    }
  
}  

1 Like

Hello @PanoS ,

Thank you very, very much for your help. This is fantastic. Certainly beyond my level of programming skills in AFL.
I am in Australia and we/I use European date format DDMMYY so, this should work.

I am not at home at right now where I can try your AFL, but when I return home tomorrow I will try to run the AFL.
Correct me if I am wrong but, to download the data to the text file, I just "Press to start download" in AFL parameter window?
And to download and import, I have to execute (ABB) batch ?

Kind Regards
Richard