Jump to content

Welcome to the new Traders Laboratory! Please bear with us as we finish the migration over the next few days. If you find any issues, want to leave feedback, get in touch with us, or offer suggestions please post to the Support forum here.

  • Welcome Guests

    Welcome. You are currently viewing the forum as a guest which does not give you access to all the great features at Traders Laboratory such as interacting with members, access to all forums, downloading attachments, and eligibility to win free giveaways. Registration is fast, simple and absolutely free. Create a FREE Traders Laboratory account here.

friva

EXCEL: Downloading Market Data in Excel

Recommended Posts

Hi everybody,

 

I am making a little investigation on how to best pull market data for stocks and index futures into Excel, with an eye on minimising costs of course.

 

I have a special interest on German and Italian stocks and futures (eg DAX, FTSE-MIB) but I care for shares/futures in general.

 

I want to be able do two things:

A) download up to 12 months of High-Low-Last prices on an excel worksheet.

B) have the corresponding live quote on another worksheet.

 

To clarify what I mean I enclose a zipped excel file.

Sheet "Data" and "Data1" give u an idea on how dowload A should look like.

 

On sheet "Data" u can see Last daily prices while on "Data1" u can see High-Low-Last prices for one minute time intervals.

 

Sheet "Last" gives u have an idea of how download B looks like (Last daily prices = live quotes).

 

I am fine updating the prices on the sheet by pressing a "refresh" button.

 

I gathered some info about vendors. See below, but double check with them if u need to be sure.

 

Pls share any suggestion or experience about downloading market data into excel.

 

 

Best

F

 

CHECKED:

 

*E-SIGNAL: can download quotes for italian and german stocks/futures for USD 249/month (including 20% tax and exchange fees). 12 months in advance yield some discount. It's USD 2779/year for 12 months (includes tax and exch fees). Billing is direct to customer, no way to get the service through a broker that then rebate to E-signal some of the comms.

 

 

*IQ-FEED: they do not offer market data from european exchanges. Ref USA they charge USD 63/month without exchange fees to download raw prices for up to 500 US securities (unclear if tax is iuncluded). To download into Excel one can use software DTNIQ that comes at an additional USD 40/month. Also sofware Qcollector and Quotecore can be used to download into excel. Links to the software vendor can be found on IQ-Feed website.

 

 

*KINETICK-NINJA: I understand one can NOT download into Excel. They offer european market data only if one funds a IB account.

 

 

NOT CHECKED YET

 

*CQG: I have heard it is very good stuff, but haven't checked yet with them. Do you know if I can download market data into Excel ? Any idea of the costs ?

 

*INTERACTIVE BROKERS: can I download market data into Excel ? costs ?

 

*BARCHART.COM: download into Excel ? costs ?

 

*RANORDER: download into Excel ? costs ?

 

*TRADEBOLT: download into Excel ? costs ?

 

 

OTHERS ?

 

*DIRECT PURCHASE FROM THE EXCHANGE:

anybody managed to download market data into excel directly from the relevant exchanges ?

Share this post


Link to post
Share on other sites

InteractiveBrokers:

you can download any data you subscribed.

there is a minimum monthly commission fee.

some data are free,

some are almost free (eg. $1/month)

but all are below normal exchange cost or eSignal fees.

 

 

ps. IB provides an excel worksheet for you to pull data from them. You don't need to do any coding.

Share this post


Link to post
Share on other sites

I would like to pick up data (high, low, open, close) for a project that I have in work.

 

What I would be interested in: S&P 500 / 10min / back to 2004. I visited the CQG site and for what I want the price tag is $700 plus. I'm not far enough along in the project to justify that kind of cost.

 

If someone else was interested in the same data, possibly we could split the cost… it would hurt my sensibilities, but I could possibly justify half. If interested, send me a PM. I'm not tied to the CQG idea, but they have what I want… possibly we can collaborate research and find a better deal.

Share this post


Link to post
Share on other sites

if you just need historical data that you are then going to then build on, there are free options around (they might not be accurate enough)

and IB does offer such abilities but unless they have really improved in this regard they are not great for mass downloading - too many throttling issues and I have had bad data from them. They are good for on going recording data daily.

 

Esignal - the advantage they have is that they offer a broad range of instruments, and you will need Qlink to help gather that data unless you want to do it manually. A lot will depend on how many instruments you want to download.

 

The thing you will be wanting to take into consideration as well are issue with the on going data collection.....

eg; are you going to be able to download and add to your data base every day....

what happens if out of say 200 stocks - 5 of them have dubious data, or are closed.

what happens if you go on holiday and cant download - how do you fill in the missing data

 

How manual is it - and how many instruments are you going to use. You dont want to spend hours downloading and checking data every day.

what happens when you have public holidays - how easy is it to adjust the data?

What is the range of instruments - futures, FX, equities?

if its for back testing you will need dividend and futures adjusted data otherwise you are wasting your time doing inaccurate backtests. If its just for charting its not as important maybe.

 

Personally I use Esignal as it gives me a charting package and enough other facilities that make it worth while to not double up on things....and it supplies the data. To be honest I think there are better charting systems out there, but it gives me enough for what I need now.

Share this post


Link to post
Share on other sites

hi TAMS, good to know about IB's competitive pricing for live data.

 

I understand they charge EUR 56/month for DAX stocks. This is not directly comparable to e-signal's (USD 246/month including taxes for all german stocks) but it seems quite low.

 

For live prices I understand IB charges EUR 25/month for Level 1 DAX future prices, EUR 15/month for level 1 FTSE-MIB futures prices.

 

Cooms are low on futures: DAX and FTSE-MIB futures (EUR 1.41-2.00 / leg) compared to local brokers (EUR 3-10/leg).

 

Comms for stock trading are relatively high (avrg 10bips compared to locals charging as low as EUR 5 per trade).

 

thank u

 

F

Share this post


Link to post
Share on other sites

QuoteLink

 

XLQ

 

don't bother reading the rest of this post - it is just some pointless words 'cos apparently my message without these words is too short to allow me to post it. there. that should be enough waffle to do it.

Share this post


Link to post
Share on other sites

At first sight XLQ looks pretty user-friendly.

They say: "if u can use Excel you know how to use XLQ".

 

On the QuoteLink site I did not find comparable attempts to make it sound "easy": it might be the target users whit stronger coding capabilities.

 

XLQ can be used through VBA, so it can be automatically activated by an Excel routine.

The program provides a list of functions that can be run in Excel. Some of these return fundamental data like "Dividend Pay date" or "Institutional %". The functions offered in the lite version are in black font, the ones added on in the full version are in blue (scroll down in "XLQ Overview").

 

XLQ comes at USD 139/year in its full version (including xlqCompanion: an application that gives alerts and calculates trailing stops) and USD 84/year in its lite version.

 

 

QuoteLink I understand charges CHF 120/years for "quotes only" and CHF 300 for "professional". It is unclear to me what is the difference between the two,

Share this post


Link to post
Share on other sites

both offer (or at least offered) a decent free trial.

 

both have templates for the kind of thing you want to do from where you can copy the excel functions you want - if i remember i may have had to search around or even ask one of them to get the template so if you can't find it contact them. Both provide good support.

 

xlq works out less expensive but you pay one year upfront (i think you need the pro version of quotelink).

 

xlq seems to only let you have one timeframe (i.e. 5 mins or 60 mins but not both at the same time and may not offer timeframe less than 5 mins for interactive brokers - i can't remember exactly). quote link is more flexible in this respect. this is really the only major difference I found between them.

 

you need to be comfortable using excel functions. both have a learning curve putting the excel functions you require together, but I got the hang of it pretty quick and the templates help.

 

one, i can't remember which - xlq i think, had a problem with interactive brokers which meant the current close price was not showing the correct value but with a little help from the programmer it got sorted. i also think I had some similar kind of problem with quotelink and again it was sorted by the programmer.

 

I don't use either any more but when I did I found both good.

 

the best thing is to download a trial of both (ask if you can't get the historical ohlc you want in the trial or just pay one month 20 or 30 bucks) and see which you like best. you will need a datafeed (iqfeed or interactive brokers - they may also offer other datafeeds).

Share this post


Link to post
Share on other sites

I moved on to other things - still trying to piece together something that works for me consistently. but for the things I was trying both xlq and quotelink did the job well.

 

thank u sr100m.

I understand you do not download prices in Excel any more.

May I ask what replaced Excel in ur trading setup ?

 

best

F

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Topics

  • Posts

    • re TikTok Recently metafakebook made what was apparently a move to stay aligned with ‘culture’ - no more fact ‘checking’, no more censorhip... basically ‘Zucker’ was shown that his mission was failing because they were only building profiles on ‘useful idiots’ instead of those who oppose the great centralization  (... just like long ago he only saw campus potential and had to be shown the promise and rewarded for fronting the great spyware and social engineering project called Fakebook)... ie they could have replaced him long ago In the same vein, who holds ‘title’ to tiktok doesn’t matter either... it will remain a spyware project regardless of who ‘buys’ it... and the data will forever be available to the CCP Just sayin’
    • Omobola,  As an engineer surely you have money to buy a ticket to Monterey, Mexico... just a hop and a jump from there to Texas...  hth zdo 
    • Date: 20th January 2025.   The NASDAQ Rises As Trump Inauguration Edges Closer!   US indices increased in value for the first time after struggling for 5 consecutive weeks. Of the main US indices the NASDAQ witnessed the strongest gains (4.12%). Risk indicators point to a higher risk appetite under the new US President, Donald Trump. President Trump's inauguration will take place this afternoon and has promised to sign over 100 consecutive orders within his first week. NASDAQ - Higher Investor Confidence! NASDAQ traders begin to stomach less frequent interest rate adjustments, the market turns its attention to earnings and Trump’s presidency. Investors are becoming more bullish under expectations that Trump will apply policies to support the US economy and entice further investment into the US stock market. A "risk-on" sentiment is evident in today's sessions, reflected in risk indicators like the VIX, High-Low Index, and Bond yields.     Investors this week will concentrate on two factors. The first factor is Trump’s consecutive orders which he has advised will be signed within his first week. Investors will closely monitor how and if these policies influence the US economy and stocks. The second factor is earnings season, which will start to gain momentum this week. Tomorrow, Netflix will release its quarterly earnings report after the market closes. Netflix is the NASDAQ’s 10th most influential company and 11th most impactful stock. Analysts expect the company’s earnings per share to drop from $5.40 to $4.21, but for Revenue to rise to $10.11 Billion. If Netflix is able to beat the earnings per share and revenue expectations, fundamental elections would indicate a rise in the price. Over the past 12 months the price has risen 76%. A further increase would further support the NASDAQ. Thereafter, investors will turn their attention to Intuitive Surgical’s earnings report. Currently, investors believe the company’s earnings per share and revenue will rise compared to the previous quarter. Intuitive’s stock has risen by more than 9% in the past week alone indicating that investors believe the company will continue to beat earnings expectations. The company has beat expectations over the past 12-months. How are Markets Reacting to Trump's inauguration? Trump pledged to issue executive orders aimed at advancing artificial intelligence programs and establishing the Department of Government Efficiency (Doge). Analysts expect these two alone to support US stocks. However, investors are not yet certain to what extent upcoming tariffs will pressure the NASDAQ and stocks. During the previous trade wars, the NASDAQ fell by 25% over a period of 4-months. Traders also should note that the NASDAQ rose in the 6-weeks after Trump won the elections. Over the past week, the VIX index fell by more than 12% indicating that the market believes US stocks will perform well under a Trump presidency. Simultaneously, US Bond yields have fallen from 4.80% to 4.58% which is known to positively influence the US stock market. Both the VIX and lower bond yields indicate higher investor confidence as Trump advises that policies will prompt more employment, US made products and more pro-US policies. NASDAQ - Technical Analysis The price of the NASDAQ trades above the 200-bar Moving Average on a 5-minute Chart indicating bullish price movement. Moving Averages have also crossed over upwards and the price trades above the VWAP indicating that the asset is maintaining its bullish momentum. Price action is also forming clear higher highs and higher lows, but investors will be cautious if the price does not find resistance at the $21,637 resistance level. In order to break above this level, investors will be hoping for positive earnings data from Netflix and Intuitive.     Key Takeaways: President Trump's inauguration will take place this afternoon with promise to sign over 100 consecutive orders within his first week. US indices rise after 5 weeks of declines, with the NASDAQ leading at 4.12%. Trump pledged to issue executive orders aimed at advancing artificial intelligence programs and establishing the Department of Government Efficiency. Analysts expect Netflix earnings per share to drop from $5.40 to $4.21, but for Revenue to rise to $10.11 Billion. Investors are becoming more bullish under expectations that President Trump will apply policies to support the US economy and entice further investment into the US stock market. Always trade with strict risk management. Your capital is the single most important aspect of your trading business.   Please note that times displayed based on local time zone and are from time of writing this report.   Click HERE to access the full HFM Economic calendar.   Want to learn to trade and analyse the markets? Join our webinars and get analysis and trading ideas combined with better understanding of how markets work. Click HERE to register for FREE!   Click HERE to READ more Market news. Michalis Efthymiou HFMarkets Disclaimer: This material is provided as a general marketing communication for information purposes only and does not constitute an independent investment research. Nothing in this communication contains, or should be considered as containing, an investment advice or an investment recommendation or a solicitation for the purpose of buying or selling of any financial instrument. All information provided is gathered from reputable sources and any information containing an indication of past performance is not a guarantee or reliable indicator of future performance. Users acknowledge that any investment in Leveraged Products is characterized by a certain degree of uncertainty and that any investment of this nature involves a high level of risk for which the users are solely responsible and liable. We assume no liability for any loss arising from any investment made based on the information provided in this communication. This communication must not be reproduced or further distributed without our prior written permission.
    • Consider: some 80% of small to medium-sized businesses around the world don’t have a website.   Many businesses in emerging economies rely on social media platforms (e.g., WhatsApp, Facebook) as their primary digital presence instead of formal websites.   But even in more digitally advanced economies, the number can hover around half.   Why? Simple answer: although we’ve made it easier to make a website, it’s still not easy enough.   Let’s say a yoga instructor wants to offer online classes but lacks tech skills or a budget.   Instead of struggling with confusing platforms, she tells her AI agent, “Set up a website for me to host yoga classes.”   The AI handles everything.   It integrates Stripe for payments, Zoom for live classes, scheduling services for in-person classes, and a chat module for inquiries.   It even suggests templates.   When the instructor picks one and asks for a purple and white color scheme, the AI updates it instantly.   No coding. No frustration. Just results.   And the best part? She didn’t have to touch a single screen or key.   This is the future Wilson describes in Age of Invisible Machines.   And, as mentioned, it’s powered by three core technologies:   Conversational User Interfaces (CUIs): Say what you need; the system handles it. From building websites to booking flights, it’s fast and human-like.   Composable Architecture: Traditional business solutions become “modules”. Like LEGO bricks, modular tools—payments, chats, scheduling—snap together to create custom solutions without starting from scratch.   No-Code Programming: AI agents code for you, empowering anyone to create without needing a developer. It’s not just a better way to interact with technology…   It’s a complete reimagining of how industries operate.   As Harvard Business School’s Marco Iansiti says, “This isn’t disruption—it’s a fundamental shift in production and interaction.”   And, the thing is…   It’s not just possible. It’s already happening.   Early examples are already here. – Chris Campbell, AltucherConfidential Profits from free accurate cryptos signals: https://www.predictmag.com/ 
    • Question: My name is Omobola Sikiru from Lagos, Nigeria. I am mechanical engineering. Where can I find someone that can be my helper to relocate me to the USA?   Answer: According to your own profile, you are trying to enter other countries through deception and immigration fraud.   You are an engineer in Nigeria, but you are not licensed as an engineer in any other country.   There are no helpers, no sponsors, and nobody is going to give you money, get you an engineering job, or get you a visa.   You must qualify to immigrate. Nobody can help you with that.   Either you qualify and have settling in money, or you don’t.   You need to improve your English before trying to get a job in a Western, English speaking country. Engineers write reports. You wrote, ‘I am mechanical engineering’. Nobody will hire you if you write like this. Rathkeale Source: https://www.quora.com/My-name-is-Omobola-Sikiru-from-Lagos-Nigeria-I-am-mechanical-engineering-Where-can-I-find-someone-that-can-be-my-helper-to-relocate-me-to-the-USA   Profits from free accurate cryptos signals: https://www.predictmag.com/  
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.