Real Time Data Collection Brainbuster
Dec 14, 2009
Objective: Collect data into the table below on a weekly basis coming from a real time data feed.
Can this be done formulas in the table?
The current time is running in cell D1.
The current date is in cell C2.
When the hour closes for the matching date, I would like rows to show the last value for that hour.
Example: ROW 3 to show the value of the currency pairs at the close of 7:00 hour for 12/13/09, etc.
My real time data is on another sheet. For reference call it: Main!G25:Main!G38 (14 currency pairs of data running, listed vertically)
ABCDEFGHIJKLMNOPQR1Week of:12/13/091:22:00AUDUSDNZDJPYGBPCHFEURUSDCHFJPYEURCHFUSDJPYUSDCHFEURGBPNZDUSDGBPUSDEURJPYAUDJPYGBPJPY2Today:12/13/2009Open Price0.911864.521.67941.462886.11.512489.051.03390.90030.72461.6242130.2681.2144.65312/13/200908:00 GMT7:004SUNDAY12/14/200909:00 GMT8:00512/14/200910:00 GMT9:00612/14/200911:00 GMT10:00FILLIN TABLEHERE712/14/200912:00 GMT11:008MONDAY12/14/200913:00 GMT12:00912/14/200914:00 GMT13:001012/14/200915:00 GMT14:001112/14/200916:00 GMT15:001212/14/200917:00 GMT16:00
View 9 Replies
ADVERTISEMENT
Feb 9, 2007
I have an API, which inputs real-time stock data into an excel book. Does anyone know how to make the Last Price cell change coloer (Greem=up, Red=donw) depending on whether the price moves up or down, relative to the last price?
ie, over the course of a minute a stock may price at 35.00 => 35.01 (cell turns green) => 35.00 (cell goes red) => 34.95 (cell stays red) => 34.97 (cell goes green).
VBA, conditional formatting, or any other means of doing this is perfectly acceptable.
View 9 Replies
View Related
Jun 8, 2014
I need a forex excel spreadsheet that shows me the last 60 min of usd/jpy in real-time. It has to update every 60 minutes automatically.
View 1 Replies
View Related
May 19, 2008
Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:
a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm
b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.
c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.
View 2 Replies
View Related
May 8, 2007
What I have:
I have 12 months in a year and each number represent a month. I have generated lists of data mainly interest and principle payments from 12 amortization sheets which i have just copied into the attached spreadsheet and will change accordingly based on the loan terms which doesn concern us. For our purposes we wont make distintion between principle and payment as i just need to see how to make what i need work.
What I need:
I need to offset data vertically automatically once i the user selects an option value (Number in this Case) from the validation list located within the Payment control box. Going from left to right you see the raw data but in this example i need the data on the right hand side to be offset down the number of rows which may be found in the control box as a validation list.
IE:
So if i were to use "Loan I" [L6] and select a "2"[J6] from the "Payment Control" then the data on the right hand side [L6[ would reflect a $0 but instead the new value for L7 would be "$24.96" and would continue down for about 374 rows. This will have to be done for Loans I - Loans N [L6:Q6] respectively. This is all i need in order to compile and make use of in my spreadsheets.
Thanks and cannot be a formula as forumlas will be overwritten and cannot be a macro so it must be a module in itself so it an run when the list is changed.
the single digit values in coulmns M6:Q3 are there mainly so you can see the difference between the base and the position at which the data is placed at. its not need but is there to stress and to show the number rows in offset of data.
View 9 Replies
View Related
Feb 25, 2013
I have 300 records in one table (A:I) and would like to create another table where column I is equal to a specific value. I understand that this is possible with an advanced filter, but I would like the second table to populate in real-time instead of needing to reapply the filter each time.
I have been trying to do this with conditional vlookups, but cannot get it to give me all the unique values. Everything comes back to circular references, which I still haven't been able to get to work.
Reasoning: I have created a fantasy baseball "cheat sheet", ranking the top 300 players. Column I is a dropdown menu for the team that drafts each player. As I update this throughout the draft, I would like my "team" to auto-populate in another table as I draft players and select my name in column I.
View 4 Replies
View Related
May 20, 2014
I wonder if it is possible in real time, to select a name from the combobox (Userform) still open Userfomr pass this value to a Dropdown in the tab?
View 3 Replies
View Related
Aug 23, 2008
I'm playing a game that requires me to keep track of money that is increased by a variable amount (pre-calculated and in a cell) and in a fixed time interval of 51 minutes weather I play it or not so I want to be able to keep track of the progress of the money gain outside of the game.
So, I need to know the macro coding for a real time counter that will increase the total money amount in one cell based on the variable income (declared in another cell) in the 51 minute intervals.
now... I also need excel to keep track of the value increase even if excel is closed (by the difference in time from when excel was closed last).
View 9 Replies
View Related
Aug 1, 2014
Currently I am using MS office 2000 premium. I would like to implement real-time stock prices (i.e. ticker MSFT) from the Nasdaq website (NASDAQ Stock Market - Stock Quotes - Stock Exchange News - NASDAQ.com) into a single cell, so that this cell shows the current stock price only.
By a click on the hyperlink symbol in the excel sheet I entered the following information... Microsoft Corporation (MSFT) Real-Time Stock Quote - NASDAQ.com and clicked the OK button.
Instead of receiving the real-time stock price information directly into the single cell, a new window opens with the Nasdaq website and all kinds of information that is not needed.
View 2 Replies
View Related
Mar 6, 2012
I am an Excel 2007 user.
I have a formula (below) that looks only at records based on specific filtering criteria or conditions; one of those conditions is the month of the year: ('Data Dump'!$Q:$Q,Summary!$A$26). This portion of the formula would change as a month is selected.
Opposed to having to rewrite the formula every time that I want to view a specific month, I want to automate the formula to change the first condition when a user selects a month from Column A. This would be a real time, automatic refiltering and recalculation as the user selects different months from Column A
Current Formula that filters and shows only January records.
=COUNTIFS('Data Dump'!$Q:$Q,Summary!$A$26,'Data Dump'!$D:$D,Summary!$A$2,'Data Dump'!$M:$M,Summary!C$2)
Column A (January is in cell A26)
January
February
March
April
May
June
July
August
September
October
November
December
View 2 Replies
View Related
Apr 22, 2008
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
View 2 Replies
View Related
Aug 7, 2012
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies
View Related
Oct 27, 2006
I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.
For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti
Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?
View 3 Replies
View Related
Oct 31, 2009
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.
The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.
I was wondering is there a formula that will collect the data automatically.
As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.
Is there a formula or something that will enter the per day data?
I have just added some false data to show you how some things get recorded.
View 14 Replies
View Related
Dec 15, 2007
I will try to keep this as short as possible. I have a huge amount of stringed data from dry kiln runs all well organized. I want to make something that will look at all this data (or at lease the ones that I specify) and give me a prediction of what would happen if I was to make a change somewhere in the schedule. I have alot more info if needed.
View 10 Replies
View Related
Mar 2, 2008
I have a UserForm function which accepts as a string the name of a TextBox control on the form. The function returns a reference to the named TextBox control (or Nothing if the control does not exist). I have the function header defined this way:
Public Function TextBox(byval strName as String) as TextBox
The function returns a reference to the TextBox control like this:
set TextBox = Me.Controls.Item(strName)
(I know, the .Item is not required.)
The code which calls the above function first declares a TextBox object with this Dim statement:
Dim txtTextBox as TextBox
The routine then goes on to call the TextBox function like this:
set txtTextBox = TextBox(strTextBoxName)
When run, the above statement genrates a Type Mismatch error. I'm confused, because if I 'TypeName()' the TextBox function's returned value, it's "TextBox,".
I thought I could work around this problem by changing the TextBox function's return value's data type to Variant, but that produced the same result.
The only "solution" I've come up with is changing the TextBox function's return value's type (and any reference to the functions' return value) to Control.
I expected that the Controls object would behave similar to a Collection object in that it's items can be different types (TextBox, Label, CommandButton, ListBox, etc.) and no Type Mistmatch erros occur so long as the type of the item returned matches the type of the variable referencing that item. But is seems that Controls requires that any reference to one of its items must be type Control, not the actual type of the item returned.
I'd prefer not to use Control data rypes in my applcation, as that would require additional code in all of my subs and functions to ensure that any Control object passed to it is the correct type of control (TextBox, Label, etc.).
Can anyone explain what's going on here? Why shouldn't I be able to assign a TextBox type variable to Controls.Item("xyz"), so long as the item returned by Controls.Item("xyz") is type TextBox?
View 9 Replies
View Related
Jun 23, 2006
I can create my own new collection which is handy as it accepts uniques only and i can access using its "key"
But can i have a collection of "user defined data types"
View 5 Replies
View Related
Sep 8, 2007
This is all taking place in vb6 and excel 2003.
I am making a userform that is activated once the user highlights a bit of spreadsheet and clicks a button on the command bar, here is what I want it to do:
1. (copy data from the spreadsheet
this is tricky because I am trying to have it be a conditional situation where the user highlights a place on the spreadsheet and whatever that place is at the moment, it will get copied to a variable on the form.
There are 8 fields to highlight and copy, two with info that wont be copied onto the new spreadsheet but will be used to update fields and make the filename.)
2. make a new spreadsheet:
a. the filename is made from cell values in the 'from' and 'to' columns and the date.
b. new spreadsheet needs to have a template section from a7:f7 that has 4 fields that will be filled in from the fields on the form.
c. copied data is to be moved from old spreadsheet to new spreadsheet to cell a8. There should be 6 columns that will be filled with data.
3. on the form there will be a browse button to save the file in a location specified by user and 'last saved location' name should be saved to a textbox.
I read about a browse button here that I would like to use, but I have to adapt it so it can be used in the 'browse' button. [url]
I dont even know if it is possible to copy a user-specified range. It seems that it should be, because when a user highlights an area, that area is being held in memory as a position... I tried passing these values to another sheet but it wasnt successful. I tried to dim 'selection' as string and pass it into a variable, but I am new to variables and this project is a big experience for me in vb.
I also am still learning about how to make values in a field pass to another field on a spreadsheet.
View 9 Replies
View Related
Jan 14, 2014
I'm trying to collect specific data from a collection of different (.xls) files into 1 master file using the following code (which runs in the master file):
Code:
Sub FolderPick()
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
[Code].....
I'm having trouble with the Name-variable in the 'red line'. This variable is set in the 'purple lines' however, the code (which I got online) keeps adding ".pdf" to the name.
I think it has to do with the settings of the 'purple/underlined line' but I don't know if this is true and if so, how to change it.
View 2 Replies
View Related
Oct 5, 2009
Is there a place on the net where I can work on, or take part of real Excel
project ( for training purpose ). If not, do you think that is possible to make such a place were people can learn and practice excel working on simulation of a real Excel projects??????
View 4 Replies
View Related
Apr 19, 2007
I need to create a waterfall structure to analyze a real estate problem. How do I do it?
View 2 Replies
View Related
Dec 3, 2009
I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.
My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3.
I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.
View 3 Replies
View Related
Feb 22, 2010
I am not quite getting how to test a value if it is an integer or a real.
If A1 = 10.4 I want to skip that cell, if A1 is 10 I want to process that cell.
View 12 Replies
View Related
Jul 26, 2009
I have 200+ XY pairs I want to chart and see a visual representation of their distribution. Then I want to cursor over exceptional points and see their identity. I don't need (nor want) data labels, disparate markers, nor a legend. I just want the points scattered on the xy plane. Also, the data set changes depending on user-controllable filter criteria.
There are two problems:
1. If I build it through the wizard as a single data series, everything is fine except that the charting process seems to replace the X value of the XY pair with a sequence number once I swap in a new data set, and ...
2. I seem to loose the individual point identities for cursor-over purposes.
The only solution I can think of for these issues is making each XY pair a one-point Series, but that's a hugely laborious way to do it. If that's it though, is there a way to globally load the independent data series and set every data point marker and color to be the same?
However, if I could do it as a single series, then I still have the issues above. Is there a way to prevent Excel from converting the X values to sequence numbers once a new data set is swapped in, and is there a way to identify the individual points for cursor-over?
View 9 Replies
View Related
Feb 18, 2007
I have two lists of names and I need to identify the people that appear on both lists. Often it is difficult to match the common records because a nickname may be used for either of the first names and not the other.
I've collated an extensive list of nicknames to help match the records, but I'm stumped on how to use it. I have a feeling that there is a sumproduct formula in my future. I attached a samples workbook.
View 9 Replies
View Related
Oct 25, 2007
I am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.
(Sample of the spreadsheet is attached)
I know this is wrong but it expresses what I am trying to do.
=IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)
1Cell C5 can either be Gross or Net from a drop down list.
2Cell B9 lists 3/1/2008
3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start).
B11 would then be the following year from B10
(following month/day pattern).
4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????
View 2 Replies
View Related
Oct 5, 2013
I have a workbook which i really want to protect. I don't want any formulas to be seen and tampered with.
Simply hiding/locking cells and protecting the sheet is not secure enough. within minutes of googling crack password, i was able to find a macro that unlocks the sheet.
Is there something out there that can really secure your workbook. I found something called XLS Padlock [URL].... seems great.
View 2 Replies
View Related
Jan 31, 2007
I designed a macro by gathering information from few posts from here it is supose to go to a website and convert a set of coordenates to another system and copy and paste info in to active workbook. It always ran as expected. My Boss asked me to load this program on his computer (he loved what it was capable of doing). And that was my worse mistake, The code would do all but nothing that it was programed to do. To make the story short, I almost got fired on the spot..
know I need help doing what I was always adviced to do: Avoid using the "Key send event". I am new to programmimg but I would hate to start looking for another job.
Sub ConvertToNAD27()
'reset query
On Error Resume Next
Range("A1").Select
'Dim IE As New InternetExplorer window
Dim ie As Object
Set ie = CreateObject("InternetExplorer.application")...................
View 9 Replies
View Related
Apr 14, 2007
see attached image. Chart is recognizing the 20 dates but not using the real dates listed in the column. Changed settings multiple ways to try to get it to use the actuals - no luck. I realize 'month's is checked in the image - but I tried 'days' and still no luck. What needs to be changed to make it report actuals listed in the 2 columns?
View 3 Replies
View Related
Jan 6, 2010
I trying to code a SeriesCollection in vba, below is the code I have:
' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"
However it it giving the error "Object denfined or Appication denfined"
View 9 Replies
View Related
