Click here to Login





                                                   Send Symbol from Excel cell to active Quantshare chart window

  0

0
Seeker
2019-11-26 02:15:23


I would be extremely grateful if someone can help me out with this. I have the following Visual Basic code. Idea is when I double click on an Excel cell which has symbol name, the chart of that symbol opens in Quantshare.
The procedure to be followed inside Quantshare is implemented as explained in the Quantshare post:
https://www.quantshare.com/sa-515-how-to-send-commands-to-quantshare-from-excel-or-other-applications

Vb code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol: " & ActiveCell, 1
Cancel = True
End Sub

But I am getting the error, "run-time error '5' invalid procedure call or argument"





QuantShare
2019-11-26 10:46:08

  0

I do not have a lot of experience regarding this but have you tried something like (as described in the article)
res = Shell ("c:\......" & ActiveCell, 1)



Seeker
2019-11-26 13:40:04

  0

Yes. Tried the code as it is also, but gets the same error.


Seeker
2019-11-26 14:06:33

  0

Is there any OLE object with Quantshare for Excel automation? So that the shell command can be avoided by calling that OLE object.


Seeker
2019-11-27 00:04:18

  0

To summarize what I have tried:

1. Initially, I tried vba code as it was given in the article which was a button click activating the macro. This was supposed to open in QS the chart of the symbol which is in Excel cell A2.

Sub Button1_Click()
res = Shell("C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol:" + Range("A2"), 1)
End Sub

Here, it was giving 'Run-time error '13' Type mismatch. When I replaced the + sign with &, that error changed to Run-time error '5': Invalid procedure call or argument.

This error continued in every attempt. Lastly, I used it this way where the macro is fired when there is cell selection change in Excel.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe TaskManager ChangeSymbol newsymbol:" & ActiveCell.Value, 1
Cancel = True
End If
End Sub

If this could get to work, it would be beautiful as I just need to move down or up in the Excel column which has the symbol name and the chart of that symbol will be opened in the currently active Quantshare chart window. Here the macro will be fired whenever there is a selection change anywhere in the range A1:A2000.

Main objective of this Excel to QS interest is to paste a Screener output into this Excel sheet and just keep browsing through the column which has the symbol name. In Excel, I can use secondary sort which has added benefit. Also if I have to run through a client's portfolio which i have in Excel, i just need to browse through the column which has symbols. If I can get it to work, I will do a detailed article on this for everyone's consumption.



Seeker
2019-11-27 09:32:51

  0

I have tried with the simplistic version as follows but same error:
res = Shell("C:\Program Files\CTrading\QuantShare\QuantShare64.exe TaskManager ChangeSymbol newsymbol:70001247", 1)

I also tried it in Windows Run as follows:
C:\Program Files\CTrading\QuantShare\QuantShare64.exe TaskManager ChangeSymbol newsymbol:70001247
This works.

So Shell is not able to send commands to Quantshare somehow.



QuantShare
2019-11-27 10:37:12

  0

Unfortunately, I do not have excel here to test this but this may help you fix this:
http://www.vbaexpress.com/forum/showthread.php?63237-Shell-Command-results-in-Runtime-Error-5




Seeker
2019-11-28 04:21:13

  0

Thanks. But still not getting solved.

Shell call works with some programs that i have but does not work with others.

Shell "C:\Program Files\qBittorrent\qbittorrent.exe", 1 'works
Shell "C:\Program Files\AmiBroker\Broker.exe", 1 'works

Shell "C:\Program Files\CTrading\QuantShare\QuantShare.exe", 1 'Run-time error '5': Invalid procedure call or argument
Shell "C:\Program Files (x86)\Equis\MetaStock\MsWin.exe", 1 'same error



QuantShare
2019-11-28 10:27:28

  0

In this post, a lot of people reported that the Shell used to work but does not work anymore or that it works for some programs and not for others.
https://bytes.com/topic/visual-basic/answers/695711-shell-run-time-error-5-invalid-procedure-call-argument

Here are some suggestions:

1/ Shell """C:\Program Files\CTrading\QuantShare\QuantShare.exe""", 1

2/
QS = "C:\Program Files\CTrading\QuantShare\QuantShare.exe"
Shell QS, 1

3/ Try using only one Shell command in all your code

4/ If this still doesn't work then the problem may come from your Antivirus who is blocking the call. Try to disable it to see.



Seeker
2019-11-28 20:23:36

  0

Thanks for your solution search. Unfortunately, same error. Removed Kaspersky completely and disabled Windows Defender. Still same error. Mostly, something to do with Windows whereby something in some programs no longer lets Shell calls to proceed . i rolled back recent updates to no avail. Tried other machines. Same issue.
Only solution in future is if Quantshare can have OLE object so that Shell is not needed at all.



Seeker
2019-12-01 09:40:16

  0

Its a problem with Windows 10 whereby Shell call does not proceed with some applications. I could mange the job (probably, not the best way) with ShellExecute. Here is the VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Set objShell = CreateObject("Shell.Application")
param = "TaskManager ChangeSymbol newsymbol:" & ActiveCell.Value
objShell.ShellExecute "C:\Program Files\CTrading\QuantShare\QuantShare.exe", param, "", "open", 1
Set objShell = Nothing
Cancel = True
End If
End Sub

When we move through the Excel cells (within the Range A1 to A2000) which has symbol name, the chart of that symbol is opened in QS. One issue is every time the QS opening prompt (UAC) appears. So, I keep the UAC to 'Never Notify'. Shell call was the best for this as it will just activate the window of the currently opened Quantshare and pass the commands.



QuantShare
2019-12-02 09:51:58

  0

Please note that there is a way to disable UAC for an application. Please Google this.


Gregg Egregg
2020-06-29 12:01:57

  0

Hi...I am wondering if anyone has found a way to do the same thing with Google Sheets Vs. Excel?
IOW, double click on a Google Sheets cell which has symbol name, the chart of that symbol opens in Quantshare.



QuantShare
2020-06-29 12:21:51

  0

Google sheets is web based and thus it is not possible to launch a program locally. There may be a solution for this but it would probably requires developing a program using Google APIs


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
Symbol Information on Chart
News data for US companies from Google RSS feed
Equity Data to Ticker Symbol
Equity Export To Excel
Relative Performance/Return Chart - Compare Several Assets/Stocks

How-to Lessons
How to quickly add a ticker symbol
How to print a chart
How to rename a ticker symbol
How to hide a trading indicator from a particular time frame
How to create a chart with a black background

Related Forum Threads
STATIC WATCH LIST-add a symbol to existing static watch list from...
Creat a Composite Index from few stock symbol
Portfolio - opening a chart from portfolio screen
First time here. Questions about Excel and Quantshare
update a new symbol from a csv file

Blog Posts
How to Send Commands to QuantShare from Excel or Other Applicatio...
Reading and Exporting Data from QuantShare to Excel Programmatica...
Update Chart Layout based on Active Ticker Symbol
Update Chart Layout based on Active Ticker Symbol - Part 2
Update Chart Layout based on Active Time Frame









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 © 2024 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.