Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Problems using RTD (real time data) function from VBA

Hi,

I am triying to use real time values from VBA, so I type:

WorksheetFunction.RTD("RTDEngine", "",BLJun - 9, "T") which works perfectly well from excel, but I can't access the value from VBA.

Can you please help me to sort this out?

Many thanks,

Valeria


Post your answer or comment

comments powered by Disqus
Hi,

I have a problem. I'm using a real-time data provider for stock data. That is not the problem. The excel sheet effectively can give a time to one cell and a price quote to another cell. This updates in real time. What I would like to do is produce and a sheet of price data throughout the day as follows:

Time High Low
9:00 1523 1517
9:05 1527 1520
9:10 1525 1523

So between 9:00 and 9:05 the high and low would update depending on the real time quote for that time period. The sheet would update for each 5 minute period. My problem is how to achieve this. I do have VBA experience so if that is the way to go it would be ok. I managed to get a high and a low for a time period only by changing the calculation mode to iterative and limiting to 1 iteration.

Can anyone think of how to solve my problem? I can explain more if things aren't clear.

I am experiencing a lag on a spreadsheet that contains some real-time data when I use the following function on a cell:


	VB:
	
 Range) 
    Application.Volatile 
    FmtText = rng.Cells(1).Text 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there any way to avoid this? I find the FmtText function extremely useful for formatting cells to display the correct number of decimal places when I concatenate cells to form sentences.

Hi guys,

I have a question about RTD graphing/charting in Excel. Currently I have a RTD feed working from our company's trading software into Excel. That's working perfectly fine. I just have absolutely no idea how to graph this real time data, nor do I have much experience with macros/VBA. Could anyone lend any insight?

I basically just want to make a simple line graph, with the horizontal axis being the time and the vertical axis being the RTD (stock price). I want the graph to update every minute.

Any help would be greatly appreciated! Thanks!

I recently posted a topic that required the use of NETWORKDAYS within a UDF.

Although I can use application.worksheetfunction. to use inbuilt functions from VBA, It would not let me use the analysis toolpak function.

I also could not use them using the evaluate method.

In the end, A solution was provided that recreated the functionality of NETWORKDAYS.

Although this solved the problem at that time, I am curious how you can use analysis toolpak functions from VBA.

Can anyone help?

Thanks in advance.

Mark Perkins

I would like to use my existing broker streaming quotes program (Scottrade) to paste real time data to an excel spreadsheet. Is this possible?

I already know about the delayed MSN quote thing.

Thanks a lot.

Hi all,

Does anybody have examples of using the Bloomberg API with Excel VBA?

Or any other examples of "real time" systems built using Excel VBA?

Alternatively, can any of the professionals give me some advice about
the way in which Excel development takes place in Investment banks?

I have never worked in this sector and was curious about how the nature
of projects differ, from other sectors I have worked in.

Please advise,

Many thanks for your help in advance,

Hi,

I am an amateur VBA programmer and need some help with real time data .
I am using the reuters tool to get the real time data. I intend to
capture this real time data in Excel every ten seconds for all the
stocks that i want to monitor and store it in an array(probably in a
different sheet) of size ten. This would mean that after 100 seconds
when all the cells of all the arrays are filled up the first cell in
each array should be cleared and assigned the current stock value.
This way each cell in the array should get a new value every 100
seconds.

Problem:- I have tried using Application.Ontime and Application .Wait
in a loop to capture these values every ten seconds. But when used in a
loop , the worksheet freezes meaning it stops recieving real time data.
And each cell of any particular array is filled with the same value as
the first cell. I need to somehow figure out how to introduce a delay
in the Sub without freezing the real time data feed . Here is a simple
code i'm trying to use. Please help me with this. I'll greatly
appreciate it.
---------------------------------------------------------------------------------------------------------------------------------------
Sub calling()
Dim i

For i = 1 To 10
Call dataCopy(i)
Call delay
Next

End Sub

--------------------------------------------------------------------------------------------------------------------------------------
Sub dataCopy(rec)
Dim i, j

j = rec

For i = 1 To 6
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
4)
Next

Exit Sub

End Sub
---------------------------------------------------------------------------------------------------------------------------------------
Sub delay()

Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub
---------------------------------------------------------------------------------------------------------------------------------------

Regards,
Paresh

Hi there,

I was wondering if anyone knows a way for me to extract foreign exchange prices to excel? I've tried Bloomberg but it seems like it is not working. There's an error to it. Are there other source that I can extract from that are reliable and has real-time data?

Thank you so much! Would appreciate your help. :D

I receive real time data from a quote service for index futures. The data is
constantly changing. How do I store that data into a pivot table?

I have a spreadsheet that pulls in some real time data. I would like an Alert Box to pup up if the RTD value exceeds a threshold. I need to do it without a macro, as the RTD are stock prices, so the data would be fast and continous. Any ideas? TIA
Paul

Hi!

I have a spreadsheet with real-time data refreshing every few seconds in one cell (say, in A2). I would like each data point to be sent to the B column, i.e., every time I start collecting data, the "live" A2 cell should write me a string B2....Bn.

Is there any simple way to do this?

Thank you folks in advance for your help,

GusL

I have written an excel macro in a spreadsheet that subscribes to real
time market data. The marco loops through a list of symbols. For each
symbol it places the symbol on a summary sheet in cell A1. this sheet
downloads some specific data for that symbol. The code then takes some
specific cells C5 D5 D7 and takes these values and puts them on the
"final" sheet next to the symbol. this creates a sheet for all the
syumbols with all the relavant data needed for rankings. However when
the Macro executes it does not allow Excel to get the real time data.
Instead it runs through the whole macro before alling the outside data.

When it does call the outside data it makes a call for each symbol.
However this is done after the macro completes. I think I am missing
some system call to allow the macro to execute outside calculations
before looping through the next symbol. any help would be greatly
appreciated.

Thanks

Hello

I have small problem with calling vlookup function from VBA. I am looking for values in sheet 2 and look them up in sheet 1. all the found one are retured to sheet 3. Unfortunately I got error while running macro: It only get to point where it found a matching value and then it stoppes.
"Unable to get the VLookup property of the WorksheetFunction class"
Can someone advice how to adjust the code so that it continue to look for the next values. ?

Sub lookup()
Dim lk3 As Variant
Dim r As Integer
Dim rng1 As Range, rng2 As Range
r = 3

Do Until Worksheets(2).Cells(r, 1).Value = ""

Set rng1 = Worksheets(2).Cells(r, 1)
Set rng2 = Worksheets(1).Range("$A$2:$A$7000")

            lk3 = Application.WorksheetFunction.VLookup(rng1, rng2, 1, False)
        
            With Worksheets(3)
                .Cells(r, 1).Value = lk3
            End With

r = r + 1
Loop

End Sub


I've written an Addin, added it to the list of available addins and installed
it. I can use the functions within the AddIn within Excel worksheets. What
I would also like to be able to do is call the function from VBA. How can I
do this?

Thanks,

Graham

Hello,

This is my first post on the Forum I have been using VBA for about 3 months and I am struggling to get my code to function fully I would appreciate all comments/suggestions.

I have used a web query to drag traffic and weather information into Excel which is set to auto update at regular intervals. I have then linked the cells to objects in powerpoint to create a weather station/traffic incidents monitor . I was hoping to run the slide show and for it to be regularly updated with the real time data. My vba code has been written in the powerpoint vbe and whilst in edit mode I can run it and it works fine albeit with a rather annoying flashing effect on the updating objects. The problems comes when the data updates in excel - the shapes do not move although the updating code is clearly cycling. Also if I put it in to slide show mode the cycling effects dissapears and so seemingly does the updating.

I would be happy to post my code if this helps


	VB:
	
 MoveFinal() 
    Dim objWorkbook As Object, objSheet As Object, strData As String, i As Long, j As String, a As String, winSp As String 
    Dim ShapeName As String 
    Dim oshp As Shape 
    Dim WordArt As String 
    Dim vArray(5, 11) As Long 
    Dim k As Integer 
     
    Dim line1 As String 
    Dim line2 As String 
    Dim line3 As String 
    Dim line4 As String 
    Dim line5 As String 
    Dim TextBox As String 
     
    Dim objExcel As Excel.Application ' early binding
    Set objExcel = GetObject(, "Excel.Application") 
     ' reference an existing application instance
    objExcel.Application.Visible = True 
     ' go to worksheet 1
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 
    vArray(1, 1) = 16 
    vArray(1, 2) = 21 
    vArray(1, 3) = 23 
    vArray(1, 4) = 25 
    vArray(1, 5) = 27 
    vArray(1, 6) = 28 
    vArray(1, 7) = 29 
    vArray(2, 1) = 12 
    vArray(2, 2) = 13 
    vArray(2, 3) = 14 
    vArray(2, 4) = 15 
    vArray(2, 5) = 17 
    vArray(2, 6) = 18 
    vArray(2, 7) = 19 
    vArray(2, 8) = 20 
    vArray(2, 9) = 24 
    vArray(2, 10) = 30 
    vArray(3, 1) = 5 
    vArray(3, 2) = 6 
    vArray(3, 3) = 7 
    vArray(3, 4) = 8 
    vArray(3, 5) = 9 
    vArray(3, 6) = 10 
    vArray(3, 7) = 11 
     
    vArray(4, 1) = 22 
    vArray(4, 2) = 26 
    vArray(4, 3) = 31 
    vArray(4, 4) = 32 
    vArray(4, 5) = 33 
    vArray(4, 6) = 34 
    vArray(4, 7) = 35 
    vArray(4, 8) = 36 
    vArray(5, 1) = 1 
    vArray(5, 2) = 2 
    vArray(5, 3) = 3 
    vArray(5, 4) = 4 
     
     
     
    Dim win As String 
    win = Time 
    While win < "23:99:00" 
         ' Iterate through the sheets
         
        For p = 1 To 5 
             ' go to worksheet 1
            Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 
             
            i = 1 
             ' Iterate through the shapes in the workbook that are on the current slide and their corresponding values
            While vArray(p, i)  0 
                 
                 ' Set shape name
                ShapeName = Trim(objSheet.Cells(270 + vArray(p, i), "O").Value) 
                 ' get value of wind direction to apply to shape
                strData = Trim(objSheet.Cells(270 + vArray(p, i), "R").Value) 
                 
                 ' move shapes to required angles
                Set oshp = ActivePresentation.Slides(p).Shapes(ShapeName) 
                 
                 
                 
                oshp.Visible = msoTrue 
                 
                 
                If strData = "S" Then 
                    oshp.Rotation = 90 
                     
                ElseIf strData = "N" Then 
                    oshp.Rotation = 270 
                ElseIf strData = "E" Then 
                    oshp.Rotation = 0 
                ElseIf strData = "W" Then 
                    oshp.Rotation = 180 
                     
                ElseIf strData = "NW" Then 
                    oshp.Rotation = 225 
                ElseIf strData = "NE" Then 
                    oshp.Rotation = 315 
                ElseIf strData = "SW" Then 
                    oshp.Rotation = 135 
                     
                ElseIf strData = "SE" Then 
                    oshp.Rotation = 45 
                ElseIf strData = "NNE" Then 
                    oshp.Rotation = 292.5 
                ElseIf strData = "ENE" Then 
                    oshp.Rotation = 337.5 
                ElseIf strData = "ESE" Then 
                    oshp.Rotation = 22.5 
                ElseIf strData = "SSE" Then 
                    oshp.Rotation = 67.5 
                ElseIf strData = "SSW" Then 
                    oshp.Rotation = 112.5 
                ElseIf strData = "WSW" Then 
                    oshp.Rotation = 157.5 
                     
                ElseIf strData = "WNW" Then 
                    oshp.Rotation = 202.5 
                ElseIf strData = "NNW" Then 
                    oshp.Rotation = 247.5 
                Else: oshp.Visible = msoCFalse 
                     
                     
                End If 
                 ' show wind speed
                If Trim(objSheet.Cells(270 + vArray(p, i), "S").Value) = 0 Then 
                    ActivePresentation.Slides(p).Shapes(ShapeName).Visible = msoFalse 
                     
                    ActivePresentation.Slides(p).Shapes(ShapeName).TextFrame.TextRange.Text = Trim(objSheet.Cells(270 +
vArray(p, i), "S").Value) & " mph" 
                    winSp = Trim(objSheet.Cells(270 + vArray(p, i), "S").Value) & " mph" 
                End If 
                 
                 ' Add update ripple changing shape name variable to ripple circles
                 ' If the speed or direction of the wind changes apply update ripple
                If strData = Trim(objSheet.Cells(270 + vArray(p, i), "W").Value) Or winSp = Trim(objSheet.Cells(270 +
vArray(p, i), "X").Value) Then 
                     
                     ' Three concentric ripples to fade in and out one by one to show an update has occured on that shape
                    k = 1 
                    While k

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

OK, I am thoroughly confused and need some professional help.

I am trying to create a program that is small but fully functional to the end user. This workbook is posted to my company’s intranet. However, it takes forever (a minute or so) to open. Many of my end users are impatient, so this creates a problem.

The end user file is connected to a large Excel file I have stored on a website. The reason it takes so long is that on opening the file, the report goes out, opens the external excel database from the website and hides it in the background. Now, once the files loads, there is no problem and the program works perfectly.

Is there a way to improve the performance? To improve performance, I was thinking that it might be better to create addins, then that lead me to look into XLLs (I can’t find any good examples out there) and that lead me to look into DDE links, but that looks like it is fading into RTD (real time data) servers… You see where I am going with this? I have no idea what to do…

I have played with Addins, and I can get the functions to work, but I don’t know how to reference the external database (excel file) in a seamless fashion -- I would love to create addins (XLAs) that reference an external database because that seems like the best method, but I can’t find examples anywhere.

HELP! Can someone point me in the right direction? Or offer any possible solutions I may not be aware of… Thank you very much…

If you have Excel 2000 or earlier you can use DDE links (Dynamic Data Exchange). If you have Excel 2002 you can use RTD links (Real Time Data). RTD is better than DDE. DDE may lose some data if Excel is busy (macro, user interaction, keystrokes..). RTD will store incoming data until Excel is available.
Does this help or you're looking for more detailed implementation info?

Hi All,
I am trying to use the Import External Data function from the Data Menu.

I have one workbook which will import data from several source files in
different locations updated at different times. The objective is to bring
together all of those workbooks (or worksheets) in different folder locations
inside one workbook for easy reference. So I have created several worksheets
for each of the source file and use the External Data Import Function to
Bring them together to the reference file.

I have no problem in Doing the 1st Time Import, But it seems that the
function for the Refresh of Data (when any changes were made to the source
file) does not give results in all the cells. It refreshes data only in cells
where data existed at the time of initial import. The import function also
doesn't give me results in cells where there is a "funtion" in the source
file.

Can anyone tell me how to refresh the whole worksheet. So that i can see
actually a copy of the source file worksheets, in my reference file.
--
M Imran Buhary

Hi

working on a project to get real time data to and from comm ports

the idea is

- to get readings live from the comm port
- Cut out the required variables from the string
- Convert from the ascii code to decimal
- putput the results to fields in a n excel sheet

Currently im using a form button to extract the current reading from an imu (innertial measurement unit) attachted to the pc by usb.

The present setup allow for 1 value to be extracted at any time - ideally I need this to happen regularly ie 1-50 hz.
Any suggestions as to how to call function ie from command button on excell sheet welcome

Runs okay as is but ocasionally delivers message
"run time error '5':
Invalid procedure call or argument

not quite sure why - could be cant get output properly - output field c26 is always blank at this point

Output stage is very much a work in progress - only stuck a few useful functions in there for when needed

idea for that is

- takes values from fields in excel
- converts into ascii format
- merges together into a string
- sends through comm port

For this I used comm32 - http://www.comm32.com/download.html

2 samples of comm output:

#dkQ=>o=Dz|==A=OY
#dkS=@`=Dz|==A=ON

'-----------------------------
'input stage
'-----------------------------

Private Sub Comm1_OnComm()

    
    Dim strwork As String
    Dim bytAry() As Byte
    Dim char1 As String
    Dim char2 As String
    Dim char3 As String
    Dim char1out As Integer
    Dim char2out As Integer
    Dim char3out As Integer
    Dim test As Integer
    Dim x As Integer
    
    
    Do Until Comm1.InBufferCount = 0
    bytAry() = Comm1.Input
    Loop

    strwork = StrConv(bytAry, vbUnicode)
    Label1.Caption = strwork
 
       
    Range("c26") = strwork
    
    
    char1 = Mid$(strwork, 4, 1)
    Range("c30") = char1
    char1out = Asc(char1)
    Range("d30") = char1out
 
    char2 = Mid$(strwork, 5, 1)
    Range("c31") = char2
    char2out = Asc(char2)
    Range("d31") = char2out
    
    char3 = Mid$(strwork, 6, 1)
    Range("c32") = char3
    char3out = Asc(char3)
    Range("d32") = char3out


    Comm1.PortOpen = False


End Sub

'-----------------------------------

Private Sub CommandButton1_Click()


Comm1.CommPort = 12
Comm1.Settings = "57600,n,8,1"
Comm1.InputMode = comInputModeBinary



Comm1.PortOpen = True

End Sub

'----------------------------------
'output stage
'----------------------------------

Private Sub CommandButton2_Click()

Comm2.CommPort = 5
Comm2.Settings = "57600,n,8,1"
Comm2.OutputMode = comOutputModeBinary

Comm2.PortOpen = True


End Sub

'---------------------------------------

Private Sub Comm2_OnComm()


Dim sumstring As String
Dim bytAry() As Byte
Dim midout1 As String
Dim out1 As Integer
Dim midout2 As String
Dim out2 As Integer
Dim midout3 As String
Dim out3 As Integer
Dim midout4 As String
Dim out4 As Integer

out1 = Range("C35")
midout1 = Chr(out1)

out2 = Range("C36")
midout2 = Chr(out2)

out3 = Range("C37")
midout3 = Chr(out3)

out4 = Range("c37")
midout4 = Chr(out4)



Comm2.Output = bytAry2

Label2.Caption = bytAry

Comm2.PortOpen = False

End Sub


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

I have been using excel for a long time. But when it comes to programming and VBAs, I would say I am a novice. I am clear about what I want to do, but don't know how to go about it. Let me know if any of you can help me....

The below link gives the realtime backfill data which most of the softwares are probably using it as a source for realtime datafeed

http://www.google.com/finance/getpri...=1266701290218

where

q= stock symbol on Google finance
x= exchange symbol
i= interval (here 60 means 60 sec (1 minute interval))
p= no of period(here 5d denotes 5 days of data)
f= parameters (day, close, open, high and low)
df= difference (cpct is may be in % change )
auto =1,
ts = time start… if you cut the last 4 digits…the rest gives the start day in seconds

you can choose your own set of value depending upon your need.

The following link gives the 1 min Realtime data for Nifty

http://www.google.com/finance/getpri...&f=d,o,h,l,c,v

Using the above explanation we can build any formats(1min,5min, 15min,hourly) of real time data and can be use as a backfill to feed charting softwares like Amibroker, Metastock, Ninja Trader etc

To get 1 min Data
http://www.google.com/finance/getpri...&f=d,o,h,l,c,v

To get 5 min Data
http://www.google.com/finance/getpri...&f=d,o,h,l,c,v

to get 15 min Data
http://www.google.com/finance/getpri...&f=d,o,h,l,c,v

to get Hourly Data
http://www.google.com/finance/getpri...&f=d,o,h,l,c,v

Here is the link where the yahoo finance offers real time data updates in realtime in csv format at free of cost. http://www.marketcalls.in/amibroker/...data-feed.html

I am trying to create a sheet so that I have to key in the scrip name in cell A1 and the duration (1 min / 5 min / 15 min / 60 min) in cell B1.

I would need exactly 300 datapoints whether 1 min / 5 min / 15 min / 60 min.

Then using VBA (which I want someone to help me) I should be able to get the data in Excel format on realtime basis..... the above links give the data in CSV...

Finally the file should be designed in a way so that if I want real-time data for 50 - 100 stocks simultaneously, I should be able to get it.....

The most important aspect is getting the data in excel and have it updated on realtime basis….

Can this be managed????

Thanks in advance....

Cheers,

Hi,
I was wondering if it is possible to conect to MySQL database and transfer only the data in a cell (in Excel) to MySQL , without using VB script?

Let me explain.

I actually want to create a form in excel. The form will be connected to a MySQL database.
I want to define a cell that shall take the input and send it to MySQL. I don't want to use any script. Is this possible????

I am writing a macro that completes a number of operations and formatting with my worksheet. One issue I am having is with trying to create data validation with the data. I have a column, K, where I am trying to create the DV. I have column H, which contains titles...there are named ranges in another sheet that are based off of these titles (i.e. one of the Titles is Concrete Section, while I have a named range called nr_Concrete_Section). What I want is for the data validation in column K to be based off of the named range, derived from the title in column H. I can do it manually by just doing...

	VB:
	
=INDIRECT("nr_"&SUBSTITUTE(H6," ","_")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is that I can't get it to work for all the rows when it runs in the macro, because I don't think I can use R1C1 notation with data validation? I have been searching around for a while and can't find anything. This is what my code currently says...my issue is with that $H$6 term, I want it instead to be something like RC[-3] (which would refer to that row's column H), but it gives me an error when I do that.


	VB:
	
 kRng.Item(hx).Validation 
    .Delete 
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
    xlBetween, FormulaR1C1:="=INDIRECT(""nr_""&SUBSTITUTE($H$6,"" "",""_""))" 
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .InputTitle = "" 
    .ErrorTitle = "" 
    .InputMessage = "" 
    .ErrorMessage = "" 
    .ShowInput = True 
    .ShowError = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas?


No luck finding an answer? You could always try Google.