Click here to Login





                                                   An example for Excel COM automation - Further joint development?

  3

0
Alexander Horn
2014-01-16 21:25:28



In the forum there are several posts re Excel automation, seems that for one or the other reason we all use good old Excel for either debugging purposes, import of data, or interaction with excel number crunching....

As up to my knowledge there has been little shared knowledge so far, here a script that shows some automation using the standard Excel COM functionality, e.g. new file, write, read, chart, close, etc.
"Excel_Com_Demonstration.azf" on http://drive.google.com/folderview?id=0B2nAWp8wo_B6eXpPcVYxVXRSOXc&usp=sharing
See instructions for usage in script.

I'd suggest that either QS or we, the community, develop some further standard classes / functions in QS for the basic interactions with Excel, so we can use in basic and special AMM / Chart and Simulation scripts more easily..

Any interest?

Alex,

P.D: Also former invite to collaborate on TAA / Asset rotation features like Charts, strategies, etc is still open, just let me know...
http://www.quantshare.com/title-1117-custom-index-relative-chart-for-taa-sector-rotation




Dave W.
2014-01-21 03:28:27

  0

Alexander - Just wanted to say thanks for posting this. I'd love to contribute to classes / standard functions, but don't currently have the C# skills. That said, if there are things that would be of value to me as well as the community, I'd be willing to pay to have things coded.

I currently don't have a clear vision of how I'd use the Excel integration. If you have specific ideas of how it might be used, maybe we could start a list and see what applications would have broad appeal.



QuantShare
2014-01-21 13:15:18

  0

Hi Alexander,

We just added a new blog post about Excel COM Automation, please check it here:
http://www.quantshare.com/sa-570-reading-and-exporting-data-from-quantshare-to-excel-programmatically




Alexander Horn
2014-01-21 21:38:31

  0

Dear QS team,

thanks for taking this up and improving my laymen code example! Let's see if somebody els jumps on the train to further develop.

Excel also accepts range to array / array to range like this, that seems faster and more convenient for large arrays:
Example using your code from blog:

public void FillExcelRange(Excel.Worksheet sheet, string[,] data)
{
Excel.Range range = sheet.get_Range("A1", "A1").get_Resize(data.GetLength(0),data.GetLength(1));
range.Value2 = data;
}

Filling up the data arrays over several colums..

Thanks again,
Alex



QuantShare
2014-01-21 22:43:03

  0

You are completely right, updating cells one by one is very slower.


Gilari Decosta
2014-01-22 21:53:04

  0

I wish if I could join this joint development.But my programming knowledge is nil.So Let me provide a link where this programming aspect is explained in simple step ,with a hope that it may help somebody to contribute to this thread meaningfully

http://www.sapnaedu.in/how-to-read-an-excel-file-in-c/

Decosta



Gilari Decosta
2014-01-24 20:59:09

  0

In India,No Broker provide Trading Terminal with DDE capabilty and tick data is not available easily.So only mean to get RT tick data to QS is through excel that is connected to TT through RTD.
QS has provided a sample code to retrieve data from excel in last blog
I hope somebody with programming skill may develop it further such that it may replace DDE pluggin
(MICRO SOFT HAS DISCONTINUED DDE)

DECOSTA



No more messages
0




Reply:

No html code. URLs turn into links automatically.

Type in the trading objects you want to include: - Add Objects
To add a trading object in your message, type in the object name, select it and then click on "Add Objects"










QuantShare

Trading Items
Relative Performance/Return Chart - Compare Several Assets/Stocks...
Equity Export To Excel
Price to Sales Ratio for US Stocks
Random Value for Each Trading Bar
Average Stock Performance for each U.S Exchange

How-to Lessons
How to display the number of stocks per day for different RSI gro...
How to download EOD quotes for active and valid stocks only
How to get stocks for a particular index using the global script
How to add future bars to plan for market scenarios
How to screen for stocks having a high correlation with the Dow J...

Related Forum Threads
List of Dow 30 or Sp500 for example
Excel COM Addin Not Loaded
Export Aggregate Case Results for Optimization
Obtain symbols for a particular index
Logic for GapUp() & GapDown() functions

Blog Posts
Reading and Exporting Data from QuantShare to Excel Programmatica...
How to search for a download item
How to Send Commands to QuantShare from Excel or Other Applicatio...
How to Download Trading Data for Certain Securities Only
How to Backtest Your Trading System for Each Industry









QuantShare
Product
QuantShare
Features
Create an account
Affiliate Program
Support
Contact Us
Trading Forum
How-to Lessons
Manual
Company
About Us
Privacy
Terms of Use

Copyright 2022 QuantShare.com
Social Media
Follow us on Facebook
Twitter Follow us on Twitter
Google+
Follow us on Google+
RSS Trading Items



Trading financial instruments, including foreign exchange on margin, carries a high level of risk and is not suitable for all investors. The high degree of leverage can work against you as well as for you. Before deciding to invest in financial instruments or foreign exchange you should carefully consider your investment objectives, level of experience, and risk appetite. The possibility exists that you could sustain a loss of some or all of your initial investment and therefore you should not invest money that you cannot afford to lose. You should be aware of all the risks associated with trading and seek advice from an independent financial advisor if you have any doubts.