Get updated data from another sheet in Excel

shravank30 · Oct 31, 2012

Hi

I have 2 Excel data sheets in my system
1 data sheet is created by a stock market program (Indiabulls) in which it enters the rates for the scripts on its own every few seconds, each entry below the last entry.
Thus there are a number of entries each below one another in the column
I can take data from that sheet, if i want, but cannot make any changes in that sheet

I have a 2nd data sheet in which i want to capture the last data entered in the indiabulls sheet & should be updated everytime the indiabulls sheet is updated

Can anyone explain how i can do this using the lookup function or any other ?

Thanks for helping

agantuk · Oct 31, 2012

You probably need a mix of the OFFSET and LOOKUP functions. Assuming your source data looks as follows:

[TABLE="width: 256"]
[TR]
[TD="width: 64"][/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="class: xl66, width: 64"]Time[/TD]
[TD="class: xl66, width: 64"]Scrip[/TD]
[TD="class: xl66, width: 64"]Price[/TD]
[/TR]
[TR]
[TD="width: 64"]2[/TD]
[TD="class: xl66, width: 64"]11:00[/TD]
[TD="class: xl66, width: 64"]RIL[/TD]
[TD="class: xl66, width: 64"]1200[/TD]
[/TR]
[TR]
[TD="width: 64"]3[/TD]
[TD="class: xl66, width: 64"]11:05[/TD]
[TD="class: xl66, width: 64"]SBI[/TD]
[TD="class: xl66, width: 64"]2000[/TD]
[/TR]
[TR]
[TD="width: 64"]4[/TD]
[TD="class: xl66, width: 64"]12:00[/TD]
[TD="class: xl66, width: 64"]RIL[/TD]
[TD="class: xl66, width: 64"]1190[/TD]
[/TR]
[TR]
[TD="width: 64"]5[/TD]
[TD="class: xl66, width: 64"]12:00[/TD]
[TD="class: xl66, width: 64"]ICICI[/TD]
[TD="class: xl66, width: 64"]9000[/TD]
[/TR]
[TR]
[TD="width: 64"]6[/TD]
[TD="class: xl66, width: 64"]13:00[/TD]
[TD="class: xl66, width: 64"]IDEA[/TD]
[TD="class: xl66, width: 64"]150[/TD]
[/TR]
[/TABLE]

If you want to find the latest value of a particular scrip, say RIL, the formula would be

=OFFSET($B$1,LOOKUP(2,1/($B$2:$B$6="RIL"),ROW($B$1:$B$6)),1)

shravank30 · Oct 31, 2012

Thanks for the solution
You have correctly given the data above except the script remains same in the file, the time & rate changes

I want the last entry in the particular column so i want c6, c7 & so on as they are added by Indiabulls
Also how would i indicate in the formula the source file ?
The formula has to be written in a separate excel file where only the latest values of column c would be displayed

Thanks for advising

agantuk · Oct 31, 2012

For the last column, you can put the maximum value of the column in your Excel version (for older versions it would be 65536, for newer ones it is 1048576), so instead of $B$6, you could put $B65536 or $B10487576 based on your Excel version.

For cells in different workbooks, the format is

[<BookName>]<SheetName>!<CellValue>

In this case, if your IndiaBulls file is called IndiaBulls.xlsx and the sheet name is LiveData, you would access cell $B$1 in the example above as

[IndiaBulls]LiveData!$B$1

asingh · Oct 31, 2012

So you want to pull the last data. How many cells you want to pull in. Just a single cell value right at the bottom of a column, or are is it a full row [more than one cell]. Tell me that, and can help out.

EDIT:
Also the column we will inspect for "last value"; will it have any gaps in the rows. IE....cells with no data. Or are they all contiguous and populated. Or does the last value cell, have a value next to it [right or left] which demarks that it is the last value, and should be pulled in.

shravank30 · Nov 1, 2012

I want to pull in the last single cell value right at the bottom of a column
all cells will be populated..no blanks
no right or left cell value is required

Thanks for advising

bigbyte · Nov 1, 2012

I do not remember exactly the syntax or script but I did a similar thing long back. But you can have a real time update of data from any excel sheet or any other windows program by netDDE or DDE protocol. Google for it for more info. This is built in to MS operating system and excel support it. Using this you can take data from an excel sheet to any other dbase program supporting this protocol.

asingh · Nov 1, 2012

shravank30 said:
I want to pull in the last single cell value right at the bottom of a column
all cells will be populated..no blanks
no right or left cell value is required

Thanks for advising

Ok...the below will help:

=INDIRECT(ADDRESS(COUNTA([Book1]Sheet2!$A$1:$A$10000),1,1,TRUE,"[Book1]Sheet2"))

With reference to color:

1. [Book1]Sheet2!$A$1:$A$10000 : Is the range / Column where you want to pick up data from. Right now it is set to 10000, but you can extend that. Note how the external workbook is referenced. This is the row reference.
2. 1 : This is the column which is being referenced. Ideally it should match what is above in red. So A=1, B=2, C=3...and so on.
3. [Book1]Sheet2 : This is the name of the external workbook and sheet which is used to retrieve the data from

All of the above will work for text/non-text data pull, and give you the last value in the range specified.

shravank30 · Nov 1, 2012

There is an excel file in the folder which I can see when i go directly using my computer navigation
However, when i copy paste your formula & try to go to that folder, it shows no files

Any solution ?

Thanks for advising

asingh · Nov 1, 2012

shravank30 said:
There is an excel file in the folder which I can see when i go directly using my computer navigation
However, when i copy paste your formula & try to go to that folder, it shows no files

Any solution ?

Thanks for advising

The source file, should be open.

shravank30 · Nov 1, 2012

Then what i will do is, I will do the editing after the market has closed & the files are closed
Will check tomorrow if it works or not

Thanks for advising

- - - Updated - - -

Even after closing all my Indiabulls software, I am still unable to see the files in the Indiabulls folder when I go through the link in the formula

Please advise alternate, if any

Thanks for advising

asingh · Nov 1, 2012

shravank30 said:
Then what i will do is, I will do the editing after the market has closed & the files are closed
Will check tomorrow if it works or not

Thanks for advising

- - - Updated - - -

Even after closing all my Indiabulls software, I am still unable to see the files in the Indiabulls folder when I go through the link in the formula

Please advise alternate, if any

Thanks for advising

No, it will not work like that. You have to know the target file. See below:

[Book1]Sheet2!$A$1:$A$10000

Book1, is the name of the file, and Sheet2 is where the data resides in column A. That file has to be open on your system in the same Excel version. (2003/07) and the destination file (where you are going to paste the formula). Open the source file first, then the target file, and paste in the respective function I gave you. You must be getting a pop-up window on pasting the function, cause Excel cannot find the file name "Book1Sheet2"; you have specified. The hunting mode method will not work. Open both files and try it. Of course you will have to modify the file name/tab name as I showed above.

shravank30 · Nov 1, 2012

asingh
The source file folder location is
c:Users\Shravan\AppData\Roaming\Indiabulls Securities Ltd\Power Indiabulls\16705\data
The file name is FUTSTK-PFC-29NOV2012_NSE112101
sheet 1
I want the last entry in column B

I am using Excel 2002
I tried changing the file name in the formula, but i got an error

I shall be grateful if you can give me the formula based on the above inputs

Thanks for advising

asingh · Nov 1, 2012

shravank30 said:
asingh
The source file folder location is
c:Users\Shravan\AppData\Roaming\Indiabulls Securities Ltd\Power Indiabulls\16705\data
The file name is FUTSTK-PFC-29NOV2012_NSE112101
sheet 1
I want the last entry in column B

I am using Excel 2002
I tried changing the file name in the formula, but i got an error

I shall be grateful if you can give me the formula based on the above inputs

Thanks for advising

=INDIRECT(ADDRESS(COUNTA('[FUTSTK-PFC-29NOV2012_NSE112101.xls]Sheet1'!$B$1:$B$10000),2,1,TRUE,"[FUTSTK-PFC-29NOV2012_NSE112101]Sheet1"))

Just remember: The tab has to be called "Sheet1". Else it will fail, cause it is hard coded above (in red). That is important. Have set it for column B. Basically the file name: "FUTSTK-PFC-29NOV2012_NSE112101.xls" is throwing the nativity off, since there are "-"s in the file name, so have to pass as a string, and accommodate using '********** ' on both sides of the file name. This should work.

shravank30 · Nov 2, 2012

Nope

As soon as I paste the revised formula, again the window opens asking me the location of the file, and the file cannot be seen, when i drill down to that folder

Please advise

Thanks for advising

asingh · Nov 2, 2012

shravank30 said:
Nope

As soon as I paste the revised formula, again the window opens asking me the location of the file, and the file cannot be seen, when i drill down to that folder

Please advise

Thanks for advising

Empty the file contents, and mail me the file on Gmail. Keep the name, as you want it.

agantuk · Nov 2, 2012

Your source file location is in the tmp folder. Are you by any chance opening it each time from within Outlook or some other software? If so, your temporary file name is prone to change. That must be the reason you don't see the file the next time. Can you confirm if your source file name remains constant over time?

Also the formula that asingh has provided refers to a sheet name 'Sheet1', not 'Sheet 1' (note the space). Your source should have the sheet name exactly as in the formula, or vice versa

shravank30 · Nov 2, 2012

the file name is constant
just noticed that the source file is in .csv format

asingh · Nov 2, 2012

Okay, it is .CSV file, we cannot have hard coded references pointing to it. Will need to think of another approach.

- - - Updated - - -

EDIT:
How frequently you want the pull to happen. I can set up some VBA which can pull in from the closed .CSV file. But it will not be a hook, since it will not pull each time the .CSV is updated.

agantuk · Nov 2, 2012

If it is a .csv file, I am curious how it is updated. Programmatically behind the scenes?

Any case, what can be done is, have the csv loaded as data into one of the other sheets in your workbook, and set it to refresh every minute. Now just use one of the formulae above (given by asingh or me) and refer to the data in the other sheet. No VBA or other stuff would be required