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



## twiceborne (16 July 2010)

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.


----------



## skyQuake (16 July 2010)

twiceborne said:


> 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()
> 
> ...




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


----------



## twiceborne (16 July 2010)

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.


----------



## skyQuake (16 July 2010)

twiceborne said:


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


----------



## skc (16 July 2010)

skyQuake said:


> 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




Is that you on the video SkyQ?


----------



## skyQuake (16 July 2010)

skc said:


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


----------



## The_Bman (23 September 2010)

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


----------



## The_Bman (27 September 2010)

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.


----------



## Gringotts Bank (2 January 2012)

skyquake, do you know which broker's versions of webiress allow RTD into excel?

Thanks!


----------



## skyQuake (2 January 2012)

Gringotts Bank said:


> skyquake, do you know which broker's versions of webiress allow RTD into excel?
> 
> Thanks!




I think they all should. webIRESS is whitelabel and brokers just put their banners on.

http://web.iress.com.au/help/Felix ...ta_Excel_Interface_-_Creating_Price_Links.htm

Suppose each broker has their own addin. eg CommsecIressExcel DDE

http://www.commsec.com.au/public/toolssupport/IRESS_TechnicalSupportFAQ.pdf


----------



## skc (3 January 2012)

Gringotts Bank said:


> 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).


----------



## Gringotts Bank (3 January 2012)

Cheers sky,skc.


----------



## Gringotts Bank (3 January 2012)

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?


----------



## Gringotts Bank (3 January 2012)

Gringotts Bank said:


> This is a slightly different topic to the OP's question.
> 
> Say I have a saved spreadsheet like this, with some historical data :
> 
> ...




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


----------

