Evaluate Text String As A Function 


Evaluate Text String As A Function  Excel 
View Answers 
I have a number of formulas used to pull data from another system into
Excel.
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the  is the
argument specifying what
data to pull into Excel. And you might recognize the argument after the
! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string
(e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as
text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!
Excel.
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the  is the
argument specifying what
data to pull into Excel. And you might recognize the argument after the
! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string
(e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as
text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!
Similar Excel Tutorials
Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
OR Function  Check if Any Argument is True
The OR function in Excel checks if ANY argument in it evaluates to TRUE. If anything evaluates to TRUE, then the f ...
The OR function in Excel checks if ANY argument in it evaluates to TRUE. If anything evaluates to TRUE, then the f ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Extract the First Word from a Cell in Excel  User Defined Delimiter Text Extraction  UDF
 This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
 This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Display The Actual Link / Email Address From Links in Excel  UDF
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Similar Topics
I have a number of formulas used to pull data from another system into Excel.
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
Systemx is the other system. After the  is the argument specifying what
information to pull. And you might recognize the argument after the ! as a
stock ticker. I want to replace the ticker with a cell reference so that I
can change the value in the cell from GOOG to YHOO and the formulas will
recalculate with the data from YHOO instead of GOOG. If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string (e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my problem?
Thanks for your help!
Three example formulas a
=SystemxBid!GOOG
=SystemxAsk!GOOG
=SystemxLast!GOOG
Systemx is the other system. After the  is the argument specifying what
information to pull. And you might recognize the argument after the ! as a
stock ticker. I want to replace the ticker with a cell reference so that I
can change the value in the cell from GOOG to YHOO and the formulas will
recalculate with the data from YHOO instead of GOOG. If I try to just
replace the ticker with the cell reference, the resulting formula,
=SystemxAsk!'A13', doesn't work.
I'm think there should be a way to construct the formula as a string (e.g.
="SystemxAsk!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as text
in the cell rather than evaluate it as a function. I know I can do a
workaround using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.
Is anyone familiar with DDE links or otherwise know a way to fix my problem?
Thanks for your help!
Here's what I am basically trying to do:
There is a list of data like this:
Ticker Date
goog 1/2/07
goog 1/3/07
goog 1/4/07
goog 1/5/07
goog 1/8/07
goog 1/9/07
ibm 1/2/07
ibm 1/3/07
ibm 1/4/07
ibm 1/5/07
Basically I need is VBA code to do the following:
Identify the first instance of goog, give me the the row#
Identify the last instance of goog, give me the row#
Identify the first instance of ibm, give me the the row#
Identify the last instance of ibm, give me the row#
I guess what is getting complex for me is the following:
There will not always be two unique stocks, sometimes it could be 2, sometimes it could be 10, or whatever, so do I need to build an array of some sort to get all the unique values in column A (ticker column) and then do some search for first and last instances.
Also, from the example, there won't necessarily be the same number of instances of each stock either.
I hope this makes sense and any help would be much appreciated.
~Gooser
There is a list of data like this:
Ticker Date
goog 1/2/07
goog 1/3/07
goog 1/4/07
goog 1/5/07
goog 1/8/07
goog 1/9/07
ibm 1/2/07
ibm 1/3/07
ibm 1/4/07
ibm 1/5/07
Basically I need is VBA code to do the following:
Identify the first instance of goog, give me the the row#
Identify the last instance of goog, give me the row#
Identify the first instance of ibm, give me the the row#
Identify the last instance of ibm, give me the row#
I guess what is getting complex for me is the following:
There will not always be two unique stocks, sometimes it could be 2, sometimes it could be 10, or whatever, so do I need to build an array of some sort to get all the unique values in column A (ticker column) and then do some search for first and last instances.
Also, from the example, there won't necessarily be the same number of instances of each stock either.
I hope this makes sense and any help would be much appreciated.
~Gooser
Need some quick help!
I'm trying to reference cells from a collection of separate files without using names. I have a master file with an input cell in which you can enter the ticker symbol of a company. For each ticker symbol, there is a related model. For instance, say that I want to enter cell D15 of my Google model (named GOOG). The Excel syntax would be as follows:
='[GOOG.xls]Commodity Sensitivity'!D15
Is there a way to combine the input of the ticker symbol in my master file with the actual file name? The models are identical in format such that each cell references the same type of metric. For instance, cell D15 refers to the P/E ratio of each company. Rather than having to name each cell, is it possible to utilize my master file ticker input with the generic file cell reference? I tried using the concatenate function but it appears that it only ends up creating a text string which doesn't create a file reference. Please let me know if this sounds wrong.
Thanks!!!
I'm trying to reference cells from a collection of separate files without using names. I have a master file with an input cell in which you can enter the ticker symbol of a company. For each ticker symbol, there is a related model. For instance, say that I want to enter cell D15 of my Google model (named GOOG). The Excel syntax would be as follows:
='[GOOG.xls]Commodity Sensitivity'!D15
Is there a way to combine the input of the ticker symbol in my master file with the actual file name? The models are identical in format such that each cell references the same type of metric. For instance, cell D15 refers to the P/E ratio of each company. Rather than having to name each cell, is it possible to utilize my master file ticker input with the generic file cell reference? I tried using the concatenate function but it appears that it only ends up creating a text string which doesn't create a file reference. Please let me know if this sounds wrong.
Thanks!!!
I want to create a form where I input the stock ticker into a cell (say, GOOG) and click a button to import GOOG stock information from the web. I could start simple, so at this point it is not so important what gets imported as long as it works. I know how to create macros, and I know how to import from the web, but I am unsure how to combines these to get what I am trying to do. Any help is appreciated.
does anybody have any further information on the ILX function in excel? i have searched far and wide but haven't found much...
i know i can get things like last price and yield  was wondering if it is possible to retrieve values like 52 week hi's and low's for a stock symbol. perhaps any other functions? is there a list available anywhere?
i currently use lines like:
=ILXQ!'GOOG,last'
=ILXQ!'GOOG,yield'
was also wondering if there is a way to retrieve my stock symbols from another column in my spreadsheet, for example:
=ILXQ!'A2,last'
where GOOG is the value in A2  i think i need something along the lines of
="ILXQ!'"&A2",last"
is that anywhere close? i cannot get any sort of syntax to work...
any help is much appreciated! thanks!
i know i can get things like last price and yield  was wondering if it is possible to retrieve values like 52 week hi's and low's for a stock symbol. perhaps any other functions? is there a list available anywhere?
i currently use lines like:
=ILXQ!'GOOG,last'
=ILXQ!'GOOG,yield'
was also wondering if there is a way to retrieve my stock symbols from another column in my spreadsheet, for example:
=ILXQ!'A2,last'
where GOOG is the value in A2  i think i need something along the lines of
="ILXQ!'"&A2",last"
is that anywhere close? i cannot get any sort of syntax to work...
any help is much appreciated! thanks!
Hi, and thanks for your help! An xls file is attached, in case the data below looks wonky.
I'm trying to take the following data:
Ticker Type Date Percent Change in Stock
APL Blue 10/20/2013 0.02
APL Green 9/5/2013 0.04
APL Green 8/1/2012 0.03
GOOG Blue 10/5/2013 0.06
GOOG Green 5/1/2012 0.04
MMM Blue 10/10/2013 0.02
MMM Blue 5/8/2013 0.02
MMM Blue 4/2/2013 0.01
MMM Blue 4/2/2012 0.06
And turn it into a chart, with Type/Color in rows, Ticker in the columns, and a list of % changes in stock/dates in the past 12 rolling months.
APL GOOG MMM
Blue +2.0% 10/13 +6.0% 10/13 +2.0% 10/13, 2.0% 5/13, +1.0% 4/13"
Green 4.0% 9/13
Example is attached, for better formatting. I tried doing this with a pivot table, but it seems that pivot tables demand to summarize your data. I'm hoping for an easier way, without VBA, to set this up. I can code VBA, but I'm setting this up for some colleagues, and if they have to make changes, they won't be able to sort through the VBA code for this, unless it's pretty simple.
Can anyone help?
In a workbook if this function does not find the string its looking for then it returns #Value, but in VBA it doesn't seem to even recognize it as an error.
Here are a few quick examples: The sub breaks, but the function returns "#Value". What I need is a work around so that I can loop a search. Right now 1 failure returns "#Value" even if the first 10 searches in the loop worked. I've been trying to use worksheetfunction.iferror but it only works on the error if its in the workbook environment not while its still in vba.
Sub Macro1()
x = WorksheetFunction.Search("Goog", "Google")
MsgBox (x)
y = WorksheetFunction.Search("Goog", "Yahoo")
MsgBox (y)
End Sub
Function Macro1()
x = WorksheetFunction.Search("Goog", "Google")
Macro1 = x
End Function
Function Macro2()
y = WorksheetFunction.Search("Goog", "Yahoo")
Macro2 = y
End Function
Anyone know how I can get a failed search (y) to return 0, so I can keep a counter going like
x= x+y
Here are a few quick examples: The sub breaks, but the function returns "#Value". What I need is a work around so that I can loop a search. Right now 1 failure returns "#Value" even if the first 10 searches in the loop worked. I've been trying to use worksheetfunction.iferror but it only works on the error if its in the workbook environment not while its still in vba.
Sub Macro1()
x = WorksheetFunction.Search("Goog", "Google")
MsgBox (x)
y = WorksheetFunction.Search("Goog", "Yahoo")
MsgBox (y)
End Sub
Function Macro1()
x = WorksheetFunction.Search("Goog", "Google")
Macro1 = x
End Function
Function Macro2()
y = WorksheetFunction.Search("Goog", "Yahoo")
Macro2 = y
End Function
Anyone know how I can get a failed search (y) to return 0, so I can keep a counter going like
x= x+y
MS EXCEL 2003
The formula to generate the internal rate of return for a dated series is
=XIRR(values, dates, [guess])
The following array formula using XIRR produces the correct result for Values in D4:D9 and the Dates in E4:E9 and satisfying the criteria "Buy" in Column C and "goog" in column B
{=XIRR((C4:C9="Buy")*(B4:B9="goog")*D4:D9,(C4:C9="Buy")*(B4:B9="goog")*E4:E9)}
This is fine for 6 rows.
When I apply this to a longer list of 250 rows the formula does not produce the correct result.
When I step through the Evaluate Formula using the Formula Auditing toolbar, it seems as though the correct process occurs for the first argument of XIRR, but not for the second. Maybe this is to long for the evaluate formula?
In any event what is going wrong?
The formula to generate the internal rate of return for a dated series is
=XIRR(values, dates, [guess])
The following array formula using XIRR produces the correct result for Values in D4:D9 and the Dates in E4:E9 and satisfying the criteria "Buy" in Column C and "goog" in column B
{=XIRR((C4:C9="Buy")*(B4:B9="goog")*D4:D9,(C4:C9="Buy")*(B4:B9="goog")*E4:E9)}
This is fine for 6 rows.
When I apply this to a longer list of 250 rows the formula does not produce the correct result.
When I step through the Evaluate Formula using the Formula Auditing toolbar, it seems as though the correct process occurs for the first argument of XIRR, but not for the second. Maybe this is to long for the evaluate formula?
In any event what is going wrong?
I have a spreadsheet that looks like the below that monthly can expand and contract both vertically and horizontally:
1/1/1999 1/2/1999 1/3/1999 GOOG 1 3 4 MSFT 6 7 9 EBAY 5 7 8
I am looking to use some series of functions that will allow me to automatically create something like the below automatically when I put in a new set of data.
Ticker date price GOOG 1/1/1999 1 GOOG 1/2/1999 3 GOOG 1/3/1999 4 MSFT 1/1/1999 6 MSFT 1/2/1999 7 MSFT 1/3/1999 9 EBAY 1/1/1999 5 EBAY 1/2/1999 7 EBAY 1/3/1999 8
I was thinking I may be able to use some combination of indirect and counta (for the columns) but have been unable to piece it together.
Thanks
Christian
1/1/1999 1/2/1999 1/3/1999 GOOG 1 3 4 MSFT 6 7 9 EBAY 5 7 8
I am looking to use some series of functions that will allow me to automatically create something like the below automatically when I put in a new set of data.
Ticker date price GOOG 1/1/1999 1 GOOG 1/2/1999 3 GOOG 1/3/1999 4 MSFT 1/1/1999 6 MSFT 1/2/1999 7 MSFT 1/3/1999 9 EBAY 1/1/1999 5 EBAY 1/2/1999 7 EBAY 1/3/1999 8
I was thinking I may be able to use some combination of indirect and counta (for the columns) but have been unable to piece it together.
Thanks
Christian
Hi,
I need help in with my excel workbook.
I've created a sheet which contains text data,
a)Company Name(goog,yhoo etc),
b)Time Frame(Daily,Weekly,Monthly),
c)Ratio(P/E,EPS etc),
d)Price(Open,Close,High,Low)
I want a vba code, which will see the e.g goog ,daily,p/e,close from a range of cells and find the workbook in the name of goog,checks the sheet for ratio (P/E) and in the sheet checks for the column close.and copy the whole column infrom that workbook into my existing sheet. this process should be automated, means when the range e.g a1:d4 will contain any value e.g goog ,daily,p/e,close, it must automatically repeat the whole process and each the range is changed with the values, it must delete the old records or eaither save them in the new sheet.
Regards
I need help in with my excel workbook.
I've created a sheet which contains text data,
a)Company Name(goog,yhoo etc),
b)Time Frame(Daily,Weekly,Monthly),
c)Ratio(P/E,EPS etc),
d)Price(Open,Close,High,Low)
I want a vba code, which will see the e.g goog ,daily,p/e,close from a range of cells and find the workbook in the name of goog,checks the sheet for ratio (P/E) and in the sheet checks for the column close.and copy the whole column infrom that workbook into my existing sheet. this process should be automated, means when the range e.g a1:d4 will contain any value e.g goog ,daily,p/e,close, it must automatically repeat the whole process and each the range is changed with the values, it must delete the old records or eaither save them in the new sheet.
Regards
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
eros
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
Here's my abbreviated code:
Private Sub Worksheet_Change(ByVal Target As Range)
For each cell in Target
If cell.column = 1 and cell.row > 1 Then
ticker = CStr(UCase(Trim(cell.value)))
Call LinkRef (ticker)
End if
Next cell
End Sub
Sub LinkRef(ticker)
MsgBox ticker
End Sub
Of course the full procedure does more than display the message box.
The problem I'm having is that if the value in the target cell begins
with a special character, like $ or % as in $DJI or %GOOG, the sub
routine does not seem to execute. I've tried removing the UCase(),
Trim() and Cstr() to see if any of those could be the culprit, but no
such luck. Any insight into what might be the cause here? Any
solutions?
Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
For each cell in Target
If cell.column = 1 and cell.row > 1 Then
ticker = CStr(UCase(Trim(cell.value)))
Call LinkRef (ticker)
End if
Next cell
End Sub
Sub LinkRef(ticker)
MsgBox ticker
End Sub
Of course the full procedure does more than display the message box.
The problem I'm having is that if the value in the target cell begins
with a special character, like $ or % as in $DJI or %GOOG, the sub
routine does not seem to execute. I've tried removing the UCase(),
Trim() and Cstr() to see if any of those could be the culprit, but no
such luck. Any insight into what might be the cause here? Any
solutions?
Thanks.
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
VB:
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Kadir
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
VB:
Function FunctionEvaluate(Symbol, Field) ' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with ' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price Dim Command As String Command = "=APPDATA!'" & Symbol & "." & Field & "'" FunctionEvaluate = Evaluate(Command) End FunctionIf you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Kadir
Dear all,
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APPDATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from an external stock application currently running under windows7. The application reads realtime stock data from a server and I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10 details info for each stock symbol arranged in an Excel table. I monitor up to 2050 stock symbols and add and delete new sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so that by changing the onlychanging bit of the above string, say YHOO (for Yahoo) for the first row in my table, then subsequent cells of the first row would be filled with automaticallyupdated formulas like "=APPDATA!YHOO.Ask", "=APPDATA!YHOO.Bid", "=APPDATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol, then comes cells which send these DDE strings as parameters to several functions for various ifthen calculations. If I cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newlycreated DDE strings accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells then the respective functions work. However, when I try to automate the DDE strings, then the functions return "N/A".
One interesting observation of mine is that if I manually type "=APPDATA!YHOO.Ask" in any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE strings as opposed to automatically getting them calculated.
How I make the function call to get the realtime data is:
A1 YHOO
B1 other stock symbol
C1 another stock symbol
A2 =FunctionEvaluate($A1,"Bid")
A3 =FunctionEvaluate($A1,"Ask")
A4 =FunctionEvaluate($A1,"Lot")
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' ApplicationTopic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APPDATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APPDATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
End Function
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new realtime data to make and return numerous ifthen calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the realtime data below failed:
=INDIRECT("APPDATA!'" & $A1 &".Ask'")
=INDIRECT("=APPDATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APPDATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APPDATA!'",$A1,".Ask"))
="=APPDATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APPDATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APPDATA!YHOO.Ask") tried to send the whole string as one parameter in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
=SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APPDATA!'xx.Ask'","xx",$A1))
Since the oldmacro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return
Eros
Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.)
For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"
Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1
What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2  GOOG and cell B2 508.9. I don't even want the rest of the stuff.
Is there any way or formula I can use to just pull the information out of cell and have it separated for me?
For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"
Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1
What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2  GOOG and cell B2 508.9. I don't even want the rest of the stuff.
Is there any way or formula I can use to just pull the information out of cell and have it separated for me?
Cannot reliably predict whether Excel will treat cell as a string or formula when I have a string: '=Sum(A1:A4) [say]
...and proceed to Search/Replace '=Sum with =Sum;
Sometimes will evaluate and sometimes will not.
>>My VBA solution is as follows 
but I would like to know a native Excel solution (if it exists).
'
Sub Toggle_Formula()
Dim oCell As Range
'This sub will toggle a cell between string/formula
'Sometimes you can achieve this with a search replace...sometimes not
'...and I cannot determine exactly when or why this Excel behaviour changes.
'Hence this function  which seems to work as required.
For Each oCell In Selection
If oCell.Formula = oCell Then
'Note this expression will force Excel to evaluate!!
'=============================
oCell.Formula = oCell.Formula
'=============================
Else
oCell.Formula = "'" & oCell.Formula
End If
Next oCell
End Sub
...and proceed to Search/Replace '=Sum with =Sum;
Sometimes will evaluate and sometimes will not.
>>My VBA solution is as follows 
but I would like to know a native Excel solution (if it exists).
'
Sub Toggle_Formula()
Dim oCell As Range
'This sub will toggle a cell between string/formula
'Sometimes you can achieve this with a search replace...sometimes not
'...and I cannot determine exactly when or why this Excel behaviour changes.
'Hence this function  which seems to work as required.
For Each oCell In Selection
If oCell.Formula = oCell Then
'Note this expression will force Excel to evaluate!!
'=============================
oCell.Formula = oCell.Formula
'=============================
Else
oCell.Formula = "'" & oCell.Formula
End If
Next oCell
End Sub
Hello,
I need a macro that will collect information from different worksheets and consolidates it on one tab.
In each worksheets, the number of columns is the same, but the number of rows differs. What I need to macro to do is to take the data + column headings from the first of the 5 source files and paste them into the final worksheet.
For example,
Worksheet 1
Ticker date cusip description ratings funds
aapl 2Feb13 aaaaaa apple aaa 1000
goog 15Feb13 bbbbbb google aaa 1500
yhoo 2Mar13 ccccccc yahoo AA 120
ko 1May13 dddddd coke BB+ 125
GM 12Jul13 eeeeee gen mtrs aa 1550
worksheet 2
date cusip description ratings funds Ticker
2Feb13 aaaaaa apple aaa 1000 AAPL
15Feb13 bbbbbb google aaa 1500 GOOG
2Mar13 ccccccc yahoo AA 120 YHOO
1May13 dddddd coke BB+ 125 KO
12Jul13 eeeeee gen mtrs aa 1550 GM
Now in worksheet 3, which will be the final worksheet, I want to create a macro which basially pulls in all the data from different worksheets and paste them in the following order.
Description Ticker ratings Funds Date Cusip
This example only has about 5 data sets, the actual file will have thousands of rows. Sheet 1 could have 1000, sheet 2 could have 500 and sheet 3 can have 1500. And this will wary on a monthly basis, which is why I don't want to copy and past the macro. This month I might have 1000 rows and next month I'll have 2000, so the macro will only pick up the 1000 not 2000, if I copy and paste.
The heading are the same in all the sheets. Can you please help me create a macro which will copy data from the different sheets using column heading and paste them into the my final sheet. So if I have a heading called "Ticker" i want the final sheet to grab all the information from each sheet under "ticker" and past them into the final sheet.
Also, each work sheet has names (sheet1=BNY sheet 2=UBS..etc..)
Can you please create a macro and show me where I can insert the names of the heading and sheets.
Thank you very much for all of your help. I hope I made sense.
Hello,
I currently use a free addin written by Randy Harmelink to retrieve the price of a stock at a given date. In my spreadsheet, I have a large number of dates listed in date format (ie: 1/1/1998) in Column A, but the actual data for the dates is listed by Excel in the form (ie: 28491) if I change the formatting to general.
For the addin, I typically use the function:
=RCHGetYahooHistory("GOOG",1998,1,1,etc...) which is in the form Year,Month,Day.
What I would like to do is to be able to essentially do this:
=RCHGetYahooHistory("GOOG",A1,etc...) or in other words, pass the date in Column A to the function in the correct format. But i'm not sure how to do this when Excel stores the date in the form '28491' in A1.
How I might accomplish this?
I currently use a free addin written by Randy Harmelink to retrieve the price of a stock at a given date. In my spreadsheet, I have a large number of dates listed in date format (ie: 1/1/1998) in Column A, but the actual data for the dates is listed by Excel in the form (ie: 28491) if I change the formatting to general.
For the addin, I typically use the function:
=RCHGetYahooHistory("GOOG",1998,1,1,etc...) which is in the form Year,Month,Day.
What I would like to do is to be able to essentially do this:
=RCHGetYahooHistory("GOOG",A1,etc...) or in other words, pass the date in Column A to the function in the correct format. But i'm not sure how to do this when Excel stores the date in the form '28491' in A1.
How I might accomplish this?
I am trying to create a UDF that takes a range as input and outputs the same range reference as a string:
for example in an excel file I would enter
=MyFunc(Sheet2!$F6:F$14, arg2)
into a cell. In the VBA code i would like the function to return the range entered in the first argument as a string, so the cell which contains this formula would read:
Sheet2!$F6:F$14
I know I can use the .address function to return the range reference as a string. However, I need to be able to detect if the reference is originally absolute or not so I can specify it in the .address arguments. Further, the .address function does not allow you to specify different column and row absolute styles for either side of the : as is shown above.
I also tried extracting the cell formula as text using the cell.formula argument. I could then trim the string to get the range i required. However, in this case I do not know how to have the function reference the cell that the function is written in (its own cell) as apposed to say the active cell.
There may be a much easier way as well?
Does anyone know how to return an arguments name as written in string form. Really struggling here and any help would be great!
for example in an excel file I would enter
=MyFunc(Sheet2!$F6:F$14, arg2)
into a cell. In the VBA code i would like the function to return the range entered in the first argument as a string, so the cell which contains this formula would read:
Sheet2!$F6:F$14
I know I can use the .address function to return the range reference as a string. However, I need to be able to detect if the reference is originally absolute or not so I can specify it in the .address arguments. Further, the .address function does not allow you to specify different column and row absolute styles for either side of the : as is shown above.
I also tried extracting the cell formula as text using the cell.formula argument. I could then trim the string to get the range i required. However, in this case I do not know how to have the function reference the cell that the function is written in (its own cell) as apposed to say the active cell.
There may be a much easier way as well?
Does anyone know how to return an arguments name as written in string form. Really struggling here and any help would be great!
Hi there everyone,
I have a spreadsheet with a column of "ticker codes" for listed companies, i.e. "YHOO" for Yahoo!. I have approximately 30,000 individual codes in random order in the column. For example;
ABLSQ
ATVI
ARXX
AKAM
ARTC
I have coded 5,600 of these "ticker codes" 1  5,600 (For those "ticker codes" that are NOT in the list of 5,600 coded "ticker codes", I want to code them 9). For example the first 4 are;
ID Ticker
1 AIR
2 ADCT
3 ALO
4 AMR
My goal is to put some sort of formula in the "ticker code" column to identify if a particular cell is one of the 5,600 and otherwise code it 9. I thought about an "if statement" but I can't construct it.
Any thoughts on where to start?
Thank you!
Liv
I have a spreadsheet with a column of "ticker codes" for listed companies, i.e. "YHOO" for Yahoo!. I have approximately 30,000 individual codes in random order in the column. For example;
ABLSQ
ATVI
ARXX
AKAM
ARTC
I have coded 5,600 of these "ticker codes" 1  5,600 (For those "ticker codes" that are NOT in the list of 5,600 coded "ticker codes", I want to code them 9). For example the first 4 are;
ID Ticker
1 AIR
2 ADCT
3 ALO
4 AMR
My goal is to put some sort of formula in the "ticker code" column to identify if a particular cell is one of the 5,600 and otherwise code it 9. I thought about an "if statement" but I can't construct it.
Any thoughts on where to start?
Thank you!
Liv
This is an example of what my project is. Basically each fund has an ID Tag that I need. My problem is explained in the picture below. Basically, I can't use VLOOKUP since I have multiple times I invest in the same fund in different portfolios. So excel doesn't know what to look at. I need to find some way where I can connect my program to excel and make the extraction easy.
My program gives me all my funds in a list by portfolio, but without spaces. In my portfolio view tab I have extra rows that are empty because some portfolios have 10 funds while others have 4. I have to include the maximum amount of rows for the biggest possible combination of the portfolios.
So my question is how could I link up what the program gives me with my tab in a way that it understands I have some rows that are empty and needs to take that into account when searching for the ID's.
The funds are broken up too. So like there is a section for InvestmentGrade and another for HighYield. When it notices a name like this, it needs to stop with the ID's etc...
I know this is difficult to explain and understand without the actual file, but I can't supply this due to confidentiality at my work.
Thanks if anyone can help with this. I'm just trying to bounce ideas around right now. I have it working, but I rigged it in a way that isn't very beneficial to us and a bit complex. I'm hoping to have a better system in place than what I did which was manually typing in the ID's with empty rows where they were needed.
Sheet1
A B C D E F G H I J K L M N 1 2 Portfolios Portfolio Views 3 4 View A View B View C View D I have a delete rows button which removes any row with 0's. So when you view port A you don't have to see the 0's. 5 A B C D Portfolio Portfolio Portfolio Portfolio 6 INTC AAPL CSCO PALM INTC AAPL CSCO PALM 7 AAPL BBY BBY INTC AAPL BBY BBY INTC 8 BBY PALM GOOG BBY PALM GOOG 0 9 GOOG AAPL GOOG 0 AAPL 0 10 INTC 0 0 INTC 0 11 12 13 14 Program 15 16 A There are no spaces in between the portfolios. I can't make the program have empty rows for what the maximum portfolio could hold. 17 INTC 18 AAPL 19 BBY So how do I build a formula where I can lookup the fund's info by portfolio and it can understand that I have empty rows in a few places? 20 GOOG 21 B 22 AAPL I'm having trouble because it doesn't know which AAPL to look up sine AAPL is in 3 of the portfolios. It is also causing problems because some portfolios have 3 funds while others have 5 or 2. I can't do a standard VLOOKUP. 23 BBY 24 PALM 25 C 26 CSCO 27 BBY 28 GOOG 29 AAPL 30 INTC 31 D 32 PALM 33 INTC
Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
My program gives me all my funds in a list by portfolio, but without spaces. In my portfolio view tab I have extra rows that are empty because some portfolios have 10 funds while others have 4. I have to include the maximum amount of rows for the biggest possible combination of the portfolios.
So my question is how could I link up what the program gives me with my tab in a way that it understands I have some rows that are empty and needs to take that into account when searching for the ID's.
The funds are broken up too. So like there is a section for InvestmentGrade and another for HighYield. When it notices a name like this, it needs to stop with the ID's etc...
I know this is difficult to explain and understand without the actual file, but I can't supply this due to confidentiality at my work.
Thanks if anyone can help with this. I'm just trying to bounce ideas around right now. I have it working, but I rigged it in a way that isn't very beneficial to us and a bit complex. I'm hoping to have a better system in place than what I did which was manually typing in the ID's with empty rows where they were needed.
Sheet1
A B C D E F G H I J K L M N 1 2 Portfolios Portfolio Views 3 4 View A View B View C View D I have a delete rows button which removes any row with 0's. So when you view port A you don't have to see the 0's. 5 A B C D Portfolio Portfolio Portfolio Portfolio 6 INTC AAPL CSCO PALM INTC AAPL CSCO PALM 7 AAPL BBY BBY INTC AAPL BBY BBY INTC 8 BBY PALM GOOG BBY PALM GOOG 0 9 GOOG AAPL GOOG 0 AAPL 0 10 INTC 0 0 INTC 0 11 12 13 14 Program 15 16 A There are no spaces in between the portfolios. I can't make the program have empty rows for what the maximum portfolio could hold. 17 INTC 18 AAPL 19 BBY So how do I build a formula where I can lookup the fund's info by portfolio and it can understand that I have empty rows in a few places? 20 GOOG 21 B 22 AAPL I'm having trouble because it doesn't know which AAPL to look up sine AAPL is in 3 of the portfolios. It is also causing problems because some portfolios have 3 funds while others have 5 or 2. I can't do a standard VLOOKUP. 23 BBY 24 PALM 25 C 26 CSCO 27 BBY 28 GOOG 29 AAPL 30 INTC 31 D 32 PALM 33 INTC
Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Does Excel have any 'builtin' ability to interpret a string as a formula, or would I need to implement a VBA function?
In it's simplest form I am thinking of something along the lines:
Cell A1 contains: 5
Cell A2 contains: "*100"
Cell A4 contains: =Evaluate(A1 & A2)
and hence displays 500
I know evaluate doesn't work, but is there something that does this?
In it's simplest form I am thinking of something along the lines:
Cell A1 contains: 5
Cell A2 contains: "*100"
Cell A4 contains: =Evaluate(A1 & A2)
and hence displays 500
I know evaluate doesn't work, but is there something that does this?
Hi.
I'm trying to set up a validation tool for a multidimensional account sequence.
My approach has been to set up the validation as text strings containing IF's and using a UDF to convert the strings to excel formulas.
VB:
My problem boils down to Evaluate() not being able to process the IF or OFFSET functions that I am using, and returns a #VALUE! error.
Am I misunderstanding something about the use of this UDF, or is the problem elsewhere?
Thank you for you time
I'm trying to set up a validation tool for a multidimensional account sequence.
My approach has been to set up the validation as text strings containing IF's and using a UDF to convert the strings to excel formulas.
VB:
Function Evalu(ByVal S As String) As String Evalu = Evaluate(S) End FunctionIf you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
My problem boils down to Evaluate() not being able to process the IF or OFFSET functions that I am using, and returns a #VALUE! error.
Am I misunderstanding something about the use of this UDF, or is the problem elsewhere?
Thank you for you time
I have a problem that recurs every few weeks. Specifically, sometimes Excel will not evaluate certain formulas on a spreadsheet. For instance, cell A1 could have the formula "=2+2" and instead of evaluating and displaying "4", it would simply display "=2+2".
Assume the file is set to AutoCalculate, that I've hit F9 several times, and even that I've hit F2 (to enter the cell) and then Enter, and none of the techniques work. Assume I don't have an apostrophe before the formula (which would turn it into a text string and display precisely as the formula is now displaying). Also assume that I have not used the Ctrltilde (or is it Alt?) shortcut for making visible all formulas on the tab. Does anyone know what is happening?
Often I can input the exact formula in another cell (say, cell B1) and then it evaluates just fine. Or if I put the formula in cell B1 and then Cut+Paste over A1 then the new formula will typically evaluate.
I'd love to know if it is just a bug in Excel (I use 2003) or if there is something I do to trigger it. Any information would be welcome. Thanks in advance.
Assume the file is set to AutoCalculate, that I've hit F9 several times, and even that I've hit F2 (to enter the cell) and then Enter, and none of the techniques work. Assume I don't have an apostrophe before the formula (which would turn it into a text string and display precisely as the formula is now displaying). Also assume that I have not used the Ctrltilde (or is it Alt?) shortcut for making visible all formulas on the tab. Does anyone know what is happening?
Often I can input the exact formula in another cell (say, cell B1) and then it evaluates just fine. Or if I put the formula in cell B1 and then Cut+Paste over A1 then the new formula will typically evaluate.
I'd love to know if it is just a bug in Excel (I use 2003) or if there is something I do to trigger it. Any information would be welcome. Thanks in advance.