Moving Average Of Real Time Data At Timed Intervals?
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
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
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
View Related
Oct 10, 2009
I am trying to do is calculate (sum) results data for time intervals. For example, between 0500-0559hrs then 0600-0659hrs and so on. Data is collected at 1 minute intervals and may have a result of 0 or anywhere up to 30.
Column A has all time data e.g. 0500,0501,0502 etc
Column B has sum of instances e.g. 0,1,2,3 etc
View 9 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
Dec 14, 2011
I have 100 Rows of Data with 2 Columns.
The left contains data input and the right Columns contains certain formula.
How do I set it up so that the worksheet can eg. Calculate 5 rows and rest 1 Second and continue this way until it reached the end of the rows? (Number of rows is not fixed).
View 5 Replies
View Related
Oct 2, 2006
I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).
Example: Average of all numbers in the bracket 28-35, in column B2-B??
By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).
View 13 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
Dec 7, 2007
Below is the code i want to be run evey 2 minutes Thanks
Sub WebData()
Dim wSU As Worksheet
Dim wSR As Worksheet
Dim wSS As Worksheet
Dim iForRow As Integer
Dim iLastRow As Integer
Dim sURL As String
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
May 26, 2014
Count the number of occurences on 1 day within a 60 minute timeframe. data includes dates and times
View 5 Replies
View Related
Nov 7, 2009
I'm the Held desk manager . I'll have my folks logging in every time interval and work for 9hrs. Now I wanted to see at any given point a day the number of folks I have on my desk .
I have created table " A " and I'm looking for a formulae so that I get my data like table " B " .
To give more insight about table '" B " - During 00:00 - 00:30 , I'll have 41 people slowly goes down as people log off..
View 13 Replies
View Related
Jan 29, 2008
I need to compare how many incidents are created within a 15 min interval and populate the sum.
I currently have the create time of the incident in the format below, assume each date below is an incident. I have a column with 96 intervals (24hours) ie 8:00,8:15,8:30,8:45,9:00 etc.... Using a formula how can I say "if create time is between *:00 and *15 count 1 and put the number into the cell and do that for each interval?
A B
------|-------------------|
Int Incident
0 | 8:00 | 1/28/2008 8:32 AM
0 | 8:15 | 1/28/2008 8:35 AM
3 | 8:30 | 1/28/2008 8:41 AM
1 | 8:45 | 1/28/2008 8:46 AM
View 9 Replies
View Related
Mar 27, 2008
The code from this page works perfectly when I am running one macro at a set interval: [url]
However, when I try to run multiple macros at different set intervals the order of execution gets messed up.
For example, I need macro1 to run at 00:00:30, macro2 at 00:00:45, macro3 at 00:01:00, then macro1 at 00:01:30, macro2 at 00:01:45, macro3 at 00:02:00 and so one.
View 4 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
Nov 20, 2006
Office 2003 with Windows XP
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
[Sample]
a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email
12:00AM
to 2 3
12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View 9 Replies
View Related
Feb 20, 2008
I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.
To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.
Sub time_intervals()
Dim x As Integer
Dim lastrow As Long
Dim timeA As Date
Dim timeB As Date
Dim timeC As Date
Dim timeD As Date
Dim timeE As Date
The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"
View 4 Replies
View Related
May 7, 2008
I am trying to continually refresh a spreadsheet (it is a countdown timer), and i am looking for a way to do this other than hold "F9". It is excel 97.
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
Feb 18, 2007
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
View 2 Replies
View Related
Dec 8, 2006
this User Defined Function (UDF) would operate on any specified data (ignoring blanks) over a range. Inputs to the UDF are range and period.
View 9 Replies
View Related
Aug 29, 2008
I have obtained a function (from this site at Exponential Moving Average) which is supposed to help calculate simple mathematical values but it's not working on spreadsheet. assist with taking a look at this as I have attached the spreadsheet?
View 6 Replies
View Related
Apr 12, 2008
In the table below I'm trying to work how trend is being produced but I can't figure it out.
I have found how to find the moving average =AVERAGE(C2:C5)
I've tried a few methods using the trend function but I really don't understand what I'm doing. how its being calculated by looking at the table?
View 9 Replies
View Related
Sep 1, 2008
I have a set of data in column A consisting of over 1200 numeric values. The problem is that there are some blank cells in this column:
Colums A data:120, blank, 135, blank, etc
I need to calaculate the average for the first 25 data and then claculate the average for the next 25 data set dropping the first 5 and adding new consecutive 5 data ignoring all the bank cells!, so if the first statemnet is : averagre (a1:a24), the second statmnet should read (a6:a:29) not counting the blank cells. I don't know what array formula can be used to do this job for me.
View 9 Replies
View Related
Jul 8, 2013
I am trying to do a moving average and running into an issue with the count part of the formula.
Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.
My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.
My average should be 31 but I am getting 33.
This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))
I have tried using different Counta and ISnumber still not working
I would rather use a formulas then VB code.
View 5 Replies
View Related
Nov 25, 2006
I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
View 9 Replies
View Related
Mar 30, 2007
I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
View 14 Replies
View Related
