Australian (ASX) Stock Market Forum

WebIRESS Excel add-in: Refreshing historical prices through VBA macro

Joined
22 February 2010
Posts
2
Reactions
0
I'm looking for some specific assistance regarding the webiress excel add-in feature. Those familiar with the add-in would know you can import historical time series of share prices and these can be refreshed or updated by double clicking the particular cell which is set to retrieve the information. As your spreadsheet increases in size, this double clicking on a daily basis becomes tedious. I've attempted to create a vba macro to automate the process. Here's a generic example with commentary.

Sub RefreshRawData()

Worksheets("HistoricalData").Activate
'Activates the spreadsheet tab containing the data to be refreshed

Range("A1").Select
'Targets the particular cell to be double clicked

Application.DoubleClick
End Sub

There appears to be a problem. I believe it's related to the cell not being identified as an object, so although the double click command occurs it doesn't provide the desired result. Thus, it doesn't refresh. I'm wondering if other users have found a way around this.
 
I'm looking for some specific assistance regarding the webiress excel add-in feature. Those familiar with the add-in would know you can import historical time series of share prices and these can be refreshed or updated by double clicking the particular cell which is set to retrieve the information. As your spreadsheet increases in size, this double clicking on a daily basis becomes tedious. I've attempted to create a vba macro to automate the process. Here's a generic example with commentary.

Sub RefreshRawData()

Worksheets("HistoricalData").Activate
'Activates the spreadsheet tab containing the data to be refreshed

Range("A1").Select
'Targets the particular cell to be double clicked

Application.DoubleClick
End Sub

There appears to be a problem. I believe it's related to the cell not being identified as an object, so although the double click command occurs it doesn't provide the desired result. Thus, it doesn't refresh. I'm wondering if other users have found a way around this.

RTD (real time data not time series) is what you're looking for

Have a look at this vid that explains it: http://www.youtube.com/watch?v=TvnfssGGmlA
 
I have a real time data. There's no issue with that. I'm asking about historical time series, and any known methods to streamline updating them, specifically relating to the WebIress Excel add-in. I don't want to refresh them manually each day. Many people would appreciate knowing this.
 
I have a real time data. There's no issue with that. I'm asking about historical time series, and any known methods to streamline updating them, specifically relating to the WebIress Excel add-in. I don't want to refresh them manually each day. Many people would appreciate knowing this.

Ahhh k, misunderstood your post. Not sure for webIRESS, but desktop iress excel addin has a "Re-execute requests without prompting".
Think you can also set excel to refersh data sources on open - which will force iress to update the historical stuff.
 
Is that you on the video SkyQ?

haha no, i shall remain elusive and mysterious..

only that way will my guru newsletter sell for $29.95/month! 1 month free if you sign up today!
Additional fees and charges may apply.
 
I too have been trying to automate the double click action without joy.

The sendmessage API sounds like the go but I haven’t got it working. I now think the issue is the Add-in VBA IRESS have created isn’t being triggered by Sendkeys, Sendmessage or application.doubleclick.

Still working on it :banghead:
 
Found it:
- Select the Cell holding the "=WebIressCell(...." formula and follow with the line:

Application.Run "'WEBIRESS.xla'!Execute"

Here is an example I use to automate an ATR calculation after entering a security into my money management system.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$D$14" Then
'Run the ATR update
Range("B42").Select
Application.Run "'WEBIRESS.xla'!Execute"
Else: End If

End Sub

So, when the security is entered into D14, the Worksheet_Change event runs the WebIressCell function as if it was double clicked.

Another effective use of the WebIressCell function is to refresh any open position stops with current data to create a trailing stop. I.e. automatically provide the stop points and manually amend Contingent Order(s) Stop Loss as market moves - Soooo much easier with multiple open positions now it can be automated. :D
 
skyquake, do you know which broker's versions of webiress allow RTD into excel?

Thanks!

Often you need to call up the broker to have it enabled. It's not a default... (and call to choose your colour scheme as well).
 
This is a slightly different topic to the OP's question.

Say I have a saved spreadsheet like this, with some historical data :

BHP 32 35 37
RIO 60 59 53
ANZ 20 18 12

And I want to get the RTD/DDE link with WebIRESS to create new columns with its fields (like OHLCV etc) alongside my current columns with historical data. Can the RTD link recognize my rows (BHP RIO ANZ) and add its data alongside?
 
This is a slightly different topic to the OP's question.

Say I have a saved spreadsheet like this, with some historical data :

BHP 32 35 37
RIO 60 59 53
ANZ 20 18 12

And I want to get the RTD/DDE link with WebIRESS to create new columns with its fields (like OHLCV etc) alongside my current columns with historical data. Can the RTD link recognize my rows (BHP RIO ANZ) and add its data alongside?

Answer to this is in the link. Didn't read it proper like.
 


Write your reply...
Top