Adding a yahoo fundamental data piece to norgate database

I am trying to get shares short info into my norgate platinum information/fundamental database for use with coding. Norgate does not offer this specific data but Yahoo does.

Is there a way to use yahoo fundamental to fill this one data point within the norgate database for all SP500 stocks without overwriting all other data?

thanks in advance

AFAIK you can not get single piece of fundamental.
But you can use AFL and Ascii import to do what you want.

This code gets shares outstanding info of a list saved to text file.

/// Save "Shares Outstanding" fundamentals from Yahoo finance to text file
/// Replace tickerlist be your own symbol list or single symbol
/// AFL code by fxshrat@gmail.com
/// @link https://forum.amibroker.com/t/adding-a-yahoo-fundamental-data-piece-to-norgate-database/17549/2
EnableTextOutput(0);
listnum = 0;//GetOption( "FilterIncludeWatchlist"); // or instead use some hardcoded number etc.
tickerlist = "AAPL,MSFT";//CategoryGetSymbols(categoryWatchlist, listnum);  

shares_out = "";

Filter = 0;
trigger = ParamTrigger( "Create Shares out", "CLICK HERE" ) OR Status("action") == actionExplore;

if ( trigger ) {
	if ( Status( "stocknum" ) == 0 ) {	
		for ( i = 0; ( nm = StrExtract( tickerlist, i, ',' ) ) != ""; i++ ) {
			ih = InternetOpenURL( "https://finance.yahoo.com/quote/"+nm+"/key-statistics?p="+nm );
			
			if ( ih ) {	
				str_repl = "";
				while( ( str = InternetReadString( ih ) ) != "" ) {
					if ( StrFind(str, ">Shares Outstanding<") ) 
						str_repl = StrReplace(str, ">Shares Outstanding<", "\n");
				}
				InternetClose( ih );
				
				str_ext = StrExtract(str_repl, 1, '\n');
				str_ext = StrExtract(str_ext, 9, '>');
				shares_out += nm + "," + StrExtract(str_ext, 0, '<')+"\n";
			}		
		}

		_TRACE(shares_out);

		// save to file        
		fh = fopen("AmiQuote\\Download\\shares_out.txt", "w" );
		if ( fh ) {
			fputs( shares_out, fh );
			fclose( fh );
		} else
			Error( "ERROR: file can not be open" );
	}
}

9


Then you can do ASCII import via Format file (read documentation)

$NOQUOTES 1
$AUTOADD 1
$OVERWRITE 1
$SEPARATOR ,

$FORMAT TICKER,SHARES_OUT

Save content to file with file name yfso.format to be saved to AmiBroker ..\Formats folder and in same folder open import.types file via some NotePad version and add following line YahooFinance_SharesOut (*.txt)|*.txt|yfso.format to it.

Then use File - Import ASCII with appropriate "Files of type" selection and browse to ...Amiquote\Download folder and import shares_out.txt file.

If you do not understand something then please read documentation. It is all there already.
Upper codes are tested and working.


You may also automate file creation and import via AmiBroker batch.

1 Like

Oops, my subconsciousness was apparently trying to trick you by not giving exact fundamental statistic that was asked for (to make you think on your own?)... Now on 2nd look I realize that you asked about shares short instead of shares outstanding (of previous post).

But in order to get shares short it is just very small modification in StrExtract code of previous AFL required.

So here is code for shares out (the rest of procedure of previous post remains the same):

/// Save "Shares Short" fundamentals from Yahoo finance to text file
/// Replace tickerlist by your own symbol list or single symbol
/// AFL code by fxshrat@gmail.com
/// @link https://forum.amibroker.com/t/adding-a-yahoo-fundamental-data-piece-to-norgate-database/17549/3
EnableTextOutput(0);
listnum = 0;//GetOption( "FilterIncludeWatchlist"); // or instead use some hardcoded number etc.
tickerlist = "AAPL,MSFT";//CategoryGetSymbols(categoryWatchlist, listnum);  

what = "sharesShort\":";
yhoo_out = "";

Filter = 0;
trigger = ParamTrigger( "Create Shares out", "CLICK HERE" ) OR Status("action") == actionExplore;

if ( trigger ) {
	if ( Status( "stocknum" ) == 0 ) {	
		for ( i = 0; ( nm = StrExtract( tickerlist, i, ',' ) ) != ""; i++ ) {
			ih = InternetOpenURL( "https://finance.yahoo.com/quote/"+nm+"/key-statistics" );
			
			if ( ih ) {	
				str_repl = "";
				while( ( str = InternetReadString( ih ) ) != "" ) {
					if ( StrFind(str, what) ) 
						str_repl = StrReplace(str, what, "\n");
				}
				InternetClose( ih );
				
				str_ext = StrExtract(str_repl, 1, '\n');				
				str_ext = StrExtract(str_ext, 2, ':');
				yhoo_out += nm + "," + StrExtract(str_ext, 1, '"')+"\n";
			}		
		}

		_TRACE(yhoo_out);

		// save to file        
		fh = fopen("AmiQuote\\Download\\yhoo_out.txt", "w" );
		if ( fh ) {
			fputs( yhoo_out, fh );
			fclose( fh );
		} else
			Error( "ERROR: file can not be open" );
	}
}

Sample list of code consisting of AAPL and MSFT:

AAPL
9

MSFT
11

AmiBroker output
(file is saved to ...AmiQuote\Download folder again)
10

4 Likes

Thanks so much for your help fxshrat!

@djjazzy213, I forgot to mention that the format file for AFL code of post #3 has to be modified to:

$NOQUOTES 1
$AUTOADD 1
$OVERWRITE 1
$SEPARATOR ,

$FORMAT TICKER,SHARES_SHORT

see SHARES_SHORT field

But instead of import via ASCII importer you may alternatively import via OLE.

So below is 3rd (alternative) code (doing that)...
In addition the code should work for most single fundamental (number) to be imported.
Right now you can select from following list via ParamList:
9

You have to read here https://www.amibroker.com/guide/objects.html if you want to add more.
As well as you have investigate source code of key statistic page
e.g. https://finance.yahoo.com/quote/AAPL/key-statistics
Hint: (ATM) at line 57 of page's source code after defaultKeyStatistics .
Format:

"statistic":{"raw":....

If you want to import more statistic then you have to modify line #15 and switch statement beginning at line #50

Another note, it is recommended to use the code in Exploration to enable UI refresh after completion.
But you may also click " View - Refresh all" or menu bar afterwards if applying the code in chart pane.

And... symbols of ticker_list variable (line 10) have to exists in your DB otherwise you will get Error 18!

/// Import SINGLE "key statistics" from Yahoo finance via OLE and save to text file (optionally)
/// Replace ticker_list by your own symbol list or single symbol
/// If you want to import different statistic then you have to modify line #15 and switch statement beginning at line #50 
/// Also see bottom of https://www.amibroker.com/guide/objects.html
/// As well as investigate source code of key statistic page for statistic_list e.g. https://finance.yahoo.com/quote/AAPL/key-statistics
/// AFL code by fxshrat@gmail.com
/// @link https://forum.amibroker.com/t/adding-a-yahoo-fundamental-data-piece-to-norgate-database/17549/5
EnableTextOutput(0);
listnum = 0;//GetOption( "FilterIncludeWatchlist"); // or instead use some hardcoded number etc.
ticker_list = "AAPL,MSFT";//CategoryGetSymbols(categoryWatchlist, listnum);  

/// modify list if required
/// statistics names of source code from e.g.  
/// @link https://finance.yahoo.com/quote/AAPL/key-statistics
statistic_list = "beta,bookValue,ebitda,floatShares,pegRatio,sharesOutstanding,sharesShort,sharesShortPriorMonth";
statistic = ParamList("Choose Statistic", statistic_list, 6);
///
export_to_file = True;// export to file in addition -> True/False setting

Filter = 0;

if ( GetChartID() > 0 )
	chart_trigger = ParamTrigger( "Import Statistic", "CLICK HERE" );
else
	chart_trigger = 0;

trigger = chart_trigger OR Status("action") == actionExplore;
SetOption("RefreshWhenCompleted", True);// works in analysis only

if ( trigger ) {
	if ( Status( "stocknum" ) == 0 ) {		
		yhoo_out = "";	
		what = statistic+"\":";	
		AB = CreateObject( "Broker.Application" );			
		for ( i = 0; ( nm = StrExtract( ticker_list, i, ',' ) ) != ""; i++ ) {
			ih = InternetOpenURL( "https://finance.yahoo.com/quote/"+nm+"/key-statistics" );
			
			if ( ih ) {	
				str_repl = "";
				while ( ( str = InternetReadString( ih ) ) != "" ) {
					if ( StrFind(str, what) ) 
						str_repl = StrReplace(str, what, "\n");
				}
				InternetClose( ih );
				
				str_ext = StrExtract(str_repl, 1, '\n');				
				str_ext = StrExtract(str_ext, 1, ':');
				value = StrToNum(StrExtract(str_ext, 0, ','));
				yhoo_out += nm + "," + value + "\n";				
				
				/// @link https://www.amibroker.com/guide/objects.html
				st = AB.Stocks( nm );
				switch( statistic ) {
					case "beta": st.Beta = value;									break;
					case "bookValue": st.BookValuePerShare = value;					break;
					case "ebitda": st.EBITDAPerShare = value;						break;
					case "floatShares": st.SharesFloat = value;						break;
					case "pegRatio": st.PEGRatio = value;							break;
					case "sharesOutstanding": st.SharesOut = value;					break;
					case "sharesShort": st.SharesShort = value;						break;
					case "sharesShortPriorMonth": st.SharesShortPrevMonth = value;	break;
					default: break;
				}
			}		
		}

		_TRACE(statistic);
		_TRACE(yhoo_out);
		
		if ( export_to_file ) {
			// save to file        
			fh = fopen("AmiQuote\\Download\\"+statistic+".txt", "w" );
			if ( fh ) {
				fputs( yhoo_out, fh );
				fclose( fh );
			} else
				Error( "ERROR: file can not be open" );
		}	
		
		PopupWindow("Import of "+statistic+" finished!\n\n"+
					"Click 'View - Refresh All' of Menu bar\n"+
					"if import not being visible yet.", "Status", 5);	
	}
}
2 Likes