Technical analysis API to Excel

Posted by Profitspi Admin at 10/13/2016 12:00 AM

Our API now allows you to retrieve historical instrument data in CSV format which is ideal for use in Excel.

Retrieve your API key

First you need to get an API key. For that you will need to be a subscriber.

Navigate to the API page using the link at the top right of any page:

Click the 'Generate API Key' button to retrieve your key:

Get the list of indicator aliases

The API documentation page at https://www.profitspi.com/api/v1/swagger/ui/index has plenty of info on all the API functions but for the purposes of this post we are only looking at the ReferenceData indicatoraliases API function and the Instruments instrumenthistory API function.

You will need to know the indicator alias codes in order to retrieve the data you need from the instrumenthistory API function.

Format the following address, replacing apikey and userid with your own, and enter the address into a browser address bar:

https://www.profitspi.com/api/v1/indicatoraliases?api_key=apikey&user_id=userid&format=csv

You should receive a file of alias codes as follows:

Retrieve instrument history data

Using the list of alias codes and the instrumenthistory API function you can specify what types of historical data you would like to retrieve.

Format the following address, replacing apikey and userid with your own, and enter the address into a browser address bar:

https://www.profitspi.com/api/v1/instrumenthistory/ibm?api_key=apikey&user_id=userid&format=csv

You should receive a file of basic historical information as follows:

But by adding one or more 'additional value' parameters we can retrieve any additional data we need. Up to 10 additional value parameters can be specified as av_0 thru av_9.

So if you extend the API address as follows:

https://www.profitspi.com/api/v1/instrumenthistory/ibm?api_key=apikey&user_id=userid&av_0=CLOSE&av_1=VOLUME&format=csv

You should now receive a file of historical information as follows:

A more complex example could include indicators with parameters, custom inputs, offsets, and different periods:

https://www.profitspi.com/api/v1/instrumenthistory/ibm?api_key=apikey&user_id=userid&av_0=SMA(50)&av_1=MACDHIST(12 26 9)&av_2=SMA(VOLUME 50)&av_3=SMA(10)[-1]&av_4=WEEKLY BBUPPER(SMA(50) CLOSE 30 2)&format=csv

You should now receive a file of historical information as follows:

Other parameters

  • per_page and page allow you to page thru the result set. The defaults are 100 for per_page and 0 for page
  • period_type can be specified as "W" for Weekly, "M" for Monthly, "Y" for Yearly. Default is "D" for Daily
  • begin_date and end_date can be specified to restrict the retrieval. Format is yyyy-mm-dd

Excel data functionality

Experienced Excel users can take advantage of the Excel data retrieval function at Data > New Query > From File > From CSV. This will allow you to easily refresh the sheet with the latest data without having to do a file download each time.