@Tomasz asked me to show my json file in this thread. To avoid hjacking the thread, I decided to start a new one.
This is a json file from Finnhub.io for EpsSurprise of AAPL from 2021-09-17:
[{"actual":null,"estimate":1.2507342000000001,"period":"2021-12-31","surprise":null,"surprisePercent":null,"symbol":"AAPL"},{"actual":1.3,"estimate":1.0269258,"period":"2021-09-30","surprise":0.273074,"surprisePercent":26.5914,"symbol":"AAPL"},{"actual":1.4,"estimate":1.0064237999999999,"period":"2021-06-30","surprise":0.393576,"surprisePercent":39.1064,"symbol":"AAPL"},{"actual":1.68,"estimate":1.4397606,"period":"2021-03-31","surprise":0.240239,"surprisePercent":16.6861,"symbol":"AAPL"}]
In my notepad++ json viewer it looks like this:
Currently I do convert this file via a bat-file to csv (a very slow process via a converter which works with node.js) and import the resulting csv-file in Amibroker as Static variable to use in explorations. I also save these converted csv files for later use.
My goal is to keep my current solution with the import as static variables and saving the converted csv-file. The new approach should only improve the speed of the conversion. I do not want to populate my database with fundamental data for now, as I prefer it to be as lean as possible and easily replaceable. To be honest, I fear I would make it too complicate and would lose overview and would not be able to easily check, if things are still correct.
Since I do not like to ask for help without trying at least a bit (I have zero knowledge of Javascript): I looked again at the Polygon.IO example and think, this here is the main part for the parsing.
output = "";
for( i = 0; i < quotes.length; i++ )
{
date = new Date( quotes[ i ].t );
open = quotes[ i ].o;
high = quotes[ i ].h;
low = quotes[ i ].l;
close = quotes[ i ].c;
volume = quotes[ i ].v;
output += dateToYMD( date ) + ", ";
output += dateToHMS( date ) + ", ";
output += open + ", ";
output += high + ", ";
output += low + ", ";
output += close + ", ";
output += volume + "\n";
}
I tried a bit with different coding and settings: I got one crash of AQ and often the Error "TypeError: 'quotes.lenght' is Null or no object." (translated into English by me). I think I got this thing totally wrong and I do not really understand how the line "quotes = all.results;" works, as the error seems to point to it.
This is my Finnhub.io.ads file of the try above:
<AmiQuote Version="4.00">
<DataSource>
<Name>Finnhub.io</Name>
<Description>Test to convert json to csv and NOT importing it</Description>
<URLTemplate>C:\Program Files\AmiBroker\MyFiles\Data\test\{symbol}.json</URLTemplate>
<ErrorMsgPrefix>Error:</ErrorMsgPrefix>
<Options>
<UseTodayAsEndDate>0</UseTodayAsEndDate>
<RunJavascript>1</RunJavascript>
</Options>
<Limits ReqPerMinute="5"/>
<CSVFormatOptions RemoveDoubleQuotes="0" ConvertISODateTime="0"/>
<ImporterOptions>
<FormatEOD>Skip,Skip,Skip,Skip,Skip,Skip,Skip</FormatEOD>
<FormatIntra>Skip,Skip,Skip,Skip,Skip,Skip,Skip</FormatIntra>
<FileExtension>csv</FileExtension>
<Separators>,</Separators>
</ImporterOptions>
<SupportedIntervals>
<Interval Period="1440" MaxDays="3650" ExtraToken="1/day"/>
<Interval Period="1" MaxDays="0" ExtraToken=""/>
<Interval Period="5" MaxDays="0" ExtraToken=""/>
<Interval Period="10" MaxDays="0" ExtraToken=""/>
<Interval Period="15" MaxDays="0" ExtraToken=""/>
<Interval Period="30" MaxDays="0" ExtraToken=""/>
<Interval Period="60" MaxDays="0" ExtraToken=""/>
</SupportedIntervals>
<Javascript>function pad(num, size)
{
num = num.toString();
while (num.length < size) num = "0" + num;
return num;
}
function dateToYMD( d )
{
return d.getFullYear() + "-" +
pad( d.getMonth() + 1, 2 ) + "-" +
pad( d.getDate(), 2 );
}
function dateToHMS( d )
{
return pad( d.getHours(), 2 ) + ":" +
pad( d.getMinutes() + 1, 2 ) + ":" +
pad( d.getSeconds(), 2 );
}
// the processing function takes text as input and produces text as ouput
function Process( input )
{
try
{
all = JSON.parse( input );
if( all.status == "ERROR" )
{
return "Error: " + all.error;
}
if( all.resultsCount == 0 )
{
return "Error: no data for this symbol";
}
quotes = all.results;
output = "";
for( i = 0; i < quotes.length; i++ )
{
date = new Date( quotes[ i ].t );
actual = quotes[ i ].o;
estimate = quotes[ i ].h;
period = quotes[ i ].l;
output += dateToYMD( date ) + ", ";
output += dateToHMS( date ) + ", ";
output += actual + ", ";
output += estimate + ", ";
output += period + ", ";
}
}
catch( e )
{
output = "Error: " + e.name + ": " + e.message;
}
return output;
}</Javascript>
</DataSource>
</AmiQuote>
I now ask myself:
- Can AmiQuote even just convert my json files and save them as csv?
- Can AmiQuote do this without actually importing the file?
My thoughts for question 1:
As I understand the new "User-definable data source"-tab "Debug": AmiQuote reads the source-file and displays the content in the "Response"-field. AmiQuote then tries to run the Javascript code (if checkbox is set) and writes the output in the "Processed content"-field. So in theory my question 1 should be possible by writing the correct javascript code.
And for question 2:
I think I should also be able to do that by using "skip" for all ASCII import settings. Though, I am unclear about the file extension: Will "csv" work?
Can anybody give me some hints or examples or is even so kind to solve it?