Jump to content
Sign in to follow this  
marc kochno

Import Live Data to Excel??

Recommended Posts

Hi Marc,

I have some queries to help clarify your request. 

1) When you say "Live" do you mean the current streaming "Live" prices imported into Excel and continuing to update? Or do you just mean say the past months price OHLC data starting from the current time (but not continuously updating)?

2)  Have you already developed an App to pull price data via the API? In which case, how are you storing the price data? 

Perhaps if you tell me what you are trying to do I can suggest how we can get to that target goal where possible.

Kind Regards, PM

Share this post


Link to post

1) Both. I am looking to have the current streaming prices, as well as previous month HLC(Last)

2) No. I am trying to figure out how to pull the data from Forex to my Excel. How do I develop an App to pull the data via API?

 

I am trying to get previous dates HLC as well as today's HL(current price) in an excel sheet

Example

Time Series CHF A0-FX
             High     Low Last  
  SWISS FRANC/US DOLLAR    
01/25/2018 0.94669 0.92895    0.94059  
01/26/2018 0.94320 0.93218 0.93218  
01/29/2018 0.93868 0.93180 0.93742  
01/30/2018 0.93936 0.93105 0.93407  
01/31/2018 0.93591 0.92895 0.93101  
02/01/2018 0.93420 0.92562 0.92630  
02/02/2018 0.93368 0.92582 0.93050  
02/05/2018 0.93768 0.92875 0.93140  
02/06/2018 0.93980 0.93068 0.93521  
02/07/2018 0.94548 0.93401 0.94292  
02/08/2018 0.94701 0.93547 0.93602  
02/09/2018 0.94091 0.93492 0.93908  
02/12/2018 0.94067 0.93637 0.93894  
02/13/2018 0.93985 0.93225 0.93497  

 

 

Thank you for your response

Share this post


Link to post

Hi Marc,

Thanks for the clarifications.

Depending upon your technical programming skills, and how much time and effort you want to expend then it is possible to do what you are seeking thought the REST API. The documentation for the API is at http://docs.labs.cityindex.com/ for your reference.

The ideal but most time/effort intensive way, is to develop an Excel plugin that enables you to connect to your account via the API within Excel. The plugin would contain functions that use the API calls to fetch historic price data for the markets you specify. Additionally, it would have a function that subscribes to Live streaming market prices and display that in Excel. 

A slightly less time expensive option would be to write an app outside Excel that automates fetching just historic price data via our API and saving that in a JSON file. The following Mircosoft article explains how you can then connect to the JSON file in Excel https://support.office.com/en-us/article/connect-to-a-json-file-f65207ab-d957-4bf0-bec3-a08bb53cd4c0. You won't have live streaming price data with this option. Additoinally, you'll have to use the formula as described in the following article to convert the UNIX time in the JSON file to human readable date https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html.

-----------------------------

In the meantime, there is a manual way you can do right now to get historic market prices into Excel so you can start your analysis. It would be a temporary workaround until you have developed some automated solution along the lines described above. 

1) Install the AT Pro downloadable Desktop trading platform (if you haven't already).

2) Login with your account credentials and open a chart of the market that you want historic prices.

3) Click the Automated Trading menu option > Copy Prices then select whether you want the entire date range or your user selected range.

4) You are prompted to save the data into a text file. This is a standard comma separated file.

5) Import the file into Excel where you now have historic price data for the market in Excel.

Kind Regards, PM!

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
×