|
Hi all, newbie here, but having a go
I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for from a broker).There is no add-in or plug-in, I just CTL ALT & drag each code from a watchlist in the program I am using and place it in a cell in excel, however can only choose one data field at a time. There are 14 data fields and over 150 codes in my list which makes 2100 cells. (My guess is about 3 days work)
The 14 data fields are the same for each column, however the stock codes change. I have a list of stock codes in column A, all data fields are in columns to the right.
I would like to just enter the stock code in column A (A3) and enter each DDE data field (e.g. lastprice, open, cose, high etc) in subsequent columns to the right and have it lookup the stock code in cell A3 and combine with the first 2 parts of the formula, based on the code in column A, rather than entering each cell individually.
Is it possible to write a macro or vba code to create the cell formula so I can just fill down and save myself 3 days work?
The formula syntax for the DDE server in excel is :
=PROGRAMNAMElDATAFIELD!STOCKCODE
example
=MISDATAlLASTPRICE!BHP.ASX
The way I understand it, a DDE formula will not read a cell value as part of the string and so I was wondering if there was a way to concatenate the first 2 parts of the formula with the stock code in the following format:
=MISDATAlLASTPRICE!BHP.ASX
where BHP.ASX has been looked up from column A
eg.
=MISDATAlLASTPRICE!$A3
I have tried brackets, quotes, etc but it does not seem to work. I have also tried
="="MISDATAlLASTPRICE&$A3 and the concatenate function, which does not work either.
I was thinking maybe there was some vba code that I could use to combine the two parts (=MISDATATAlLASTPRICE! with $A3) and paste the formula in a cell so excel reads it as a formula.
Could anyone help please, I'd be very greatful
| 
