Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Evaluate Text String As A Function

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a number of formulas used to pull data from another system into
Excel.
Three example formulas a

=Systemx|Bid!GOOG
=Systemx|Ask!GOOG
=Systemx|Last!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,
=Systemx|Ask!'A13', doesn't work.

I'm think there should be a way to construct the formula as a string
(e.g.
="Systemx|Ask!" & 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
work-around 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 Video Tutorials

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

Similar Topics







I have a number of formulas used to pull data from another system into Excel.
Three example formulas a

=Systemx|Bid!GOOG
=Systemx|Ask!GOOG
=Systemx|Last!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,
=Systemx|Ask!'A13', doesn't work.

I'm think there should be a way to construct the formula as a string (e.g.
="Systemx|Ask!" & 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
work-around 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


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

=ILX|Q!'GOOG,last'
=ILX|Q!'GOOG,yield'

was also wondering if there is a way to retrieve my stock symbols from another column in my spreadsheet, for example:

=ILX|Q!'A2,last'

where GOOG is the value in A2 - i think i need something along the lines of

="ILX|Q!'"&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


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?


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


Hey everyone,

This forum has been great in the past so I thought I'd asked another question.

Lets say I want to retrieve the balance sheet of a company using a dynamic web query that references a cell with a ticker symbol.

For example here's the balance sheet for google:
http://www.zacks.com/research/report...ype=abs&t=goog

If I replace goog with ["Ticker"] and click import, I'm stuck with a completely different screen from Zachs that returns an error for invalid lookup. The problem is that none of the tables I can select on the page turn into the balance the sheet after I set the parameter to my ticker cell. If I import the entire page it works, but that would be hell to deal with.

I know one way to deal with this is by making a macro of me importing the balance sheet for a particular security and then modifying the URL in VBA to reference the ticker cell on my worksheet. Is it possible to do it another way? Preferably a way where the moment I enter a new ticker symbol and click enter, it automatically retrieves the new balance sheet (like normally would happen if I click the parameter check boxes)?

Thanks a ton for any help!


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

Dear all,


I really could not find a simiar problem to mine, so I highly welcome any guidance from you:


String Example: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.

Currently, it does not because the FunctionEvaluate above returns "N/A".


Lastly, my attempts to formulate DDE strings and get the real-time data below failed:

=INDIRECT("APP|DATA!'" & $A1 &".Ask'")

=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")

=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))

=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))

="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here

=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))


Since the old-macro 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: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.

Currently, it does not because the FunctionEvaluate above returns "N/A".


Lastly, my attempts to formulate DDE strings and get the real-time data below failed:

=INDIRECT("APP|DATA!'" & $A1 &".Ask'")

=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")

=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))

=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))

="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here

=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))


Since the old-macro 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.




Dear all,

I really could not find a simiar problem to mine, so I highly welcome any guidance from you:

String Example: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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) 
     '  Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
     '  APP|DATA!'Yhoo.Ask'  Example for Yahoo Ask price
    Dim Command As String 
    Command = "=APP|DATA!'" & Symbol & "." & Field & "'" 
    FunctionEvaluate = Evaluate(Command) 
End Function 


If 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 real-time data to make and return numerous if-then calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".

Lastly, my attempts to formulate DDE strings and get the real-time data below failed:
=INDIRECT("APP|DATA!'" & $A1 &".Ask'")
=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))
="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))

Since the old-macro 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: "=APP|DATA!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 real-time 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 20-50 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 only-changing 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 automatically-updated formulas like "=APP|DATA!YHOO.Ask", "=APP|DATA!YHOO.Bid", "=APP|DATA!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 if-then 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 newly-created 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 "=APP|DATA!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 real-time 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)
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & 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 real-time data to make and return numerous if-then calculations.
Currently, it does not because the FunctionEvaluate above returns "N/A".

Lastly, my attempts to formulate DDE strings and get the real-time data below failed:
=INDIRECT("APP|DATA!'" & $A1 &".Ask'")
=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")
=INDIRECT(CONCATENATE("APP|DATA!'",$A1,".Ask"))
=INDIRECT(CONCATENATE("=APP|DATA!'",$A1,".Ask"))
="=APP|DATA!'" & $A1 &".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is not recognized by Excel here
=eval("=APP|DATA!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("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))

Since the old-macro 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?


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


Here is a sample UDF that determines if the varible is a number Code:

Function isnum(temp As Variant)
Evaluate ("isnumber(temp)")
End Function


This is just a "sample" UDF to ask my question. What I want to do is have the evaluate function use the UDF varible (temp) in the isnumber function. I have tried using replace with the evaluate to no avail.

This problem seems even more difficult to me as temp could be cell reference, a string or a number. If temp is a string replace should put the string wrapped with "" in its place and if temp is a number or a cell replace should put the number or cell w/o "" .

Thanks in advance,

Tom




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 2-Feb-13 aaaaaa apple aaa 1000
goog 15-Feb-13 bbbbbb google aaa 1500
yhoo 2-Mar-13 ccccccc yahoo AA 120
ko 1-May-13 dddddd coke BB+ 125
GM 12-Jul-13 eeeeee gen mtrs aa 1550


worksheet 2

date cusip description ratings funds Ticker
2-Feb-13 aaaaaa apple aaa 1000 AAPL
15-Feb-13 bbbbbb google aaa 1500 GOOG
2-Mar-13 ccccccc yahoo AA 120 YHOO
1-May-13 dddddd coke BB+ 125 KO
12-Jul-13 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 add-in 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 add-in, 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 need some help creating a UDF that will evaluate the results of a
string that has been created using concatenate() that includes an
argument to be substituted in the resulting text string.

Concatenate results with the following text string:

(LogCCoef*LN(x))+LogBCoef

LogCCoef and LogBCoef are named excel formulas that return values. The
"x" is the placeholder for the argument that I would also pass to UDF
in the form a cell reference. The UDF would look like:

EvaluateThis(ModelFormula,b12)

Assuming:
ModelFormula (a named formula) is:
=concatenate((LogCCoef*LN(x))+LogBCoef)
LogCCoef (a named formula) returns: -6.35
LogBCoef (a named formula) returns: 55.8
Value in cell b12 is: 400

The UDF would return 17.75 [(-6.35*ln(400))+55.8]
Could someone provide some guidance?

GH




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!


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





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 Investment-Grade and another for High-Yield. 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.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4