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.
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.
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.
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.
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).
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.
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
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.
I've have two cell with data as below: CELL1: X1, X2, X5, X7, X8, X12 CELL2: X2, X5, X12
My question: 1. using macro to identify different data in the cell using color (for same data will be blue and different data will be red). 2. How to count the word in the cell (sample CELL1: after count will become 6 words)
I have plotted a chart through VBA and I am appending some information to certain data labels. This appended part is being turned a different color depending on if a corresponding value is less than or greater than 0.
Code: For i = 1 To cht.SeriesCollection(1).DataLabels.Count If ws.Range("C" & i + 1).Value < 0 Then[code]....
In stepping through the code, I notice that it works until the color switches twice. So for instance, if the first 3 points are below 0, it turns the appended part of the data label red. Then the next point is above 0 and it turns it green. Then the next point is below 0, and at this point it turns the appended part of EVERY data label red (overwriting the previous green one). It keeps going like this until all of the data labels are either red or green, depending on whatever the last value is as it just keeps overwriting.
I need this chart to show 3 bar colors based upon values from b5:b14 (data column). This chart is based upon the last 10 draws, so since there are 3 positions, the hit frequency expectancy would be (10 draws/3 positions = 3.33 rounded to 3). So now I would like my bar graph colors RED for any values above "3" in b5:b14 (data column), GREEN for any values equal to "3" in b5:b14 (data column) and BLUE for any values below "3" in b5:b14 (data column).
I had this question earlier and I dont know if it just isnt a regularly answered section so I though i would ask it here.....better yet I will just link to the other page so you can see whats up. Hopefully i will get a few more hits on it in this thread.
Is it possible to set a formula or something up so that if a row contains certain information, then the row will turn a certain color? For instance, if cell B2 contain the number "3" and cell B5 contains the number "5", then row B will turn yellow. Then if cell C2 contains the number "4" and cell C5 contains the number "7", row C will turn blue. This is just an example. In this example, I'd like a formula all the way down column's 2 and 5, but only the rows would change color (not columns) according to their information.
I have got a Pivot table with makes and their sales in 2008 and 2009 like this 2008 2009 BMW 100 90 Audi 80 80 Mercedes 200 150 Fiat 80 120 Honda 100 110 Peugeot 70 120 (unfortunatley the formatting is deleted when posting).
I have created a stack column chart. In that chart I want certain makes to have certain colors like BMW = blue, Audi = yellow, Mercedes = black, Fiat = green, Honda = red and Peugeot = purple When I update the Pivot with a new month and a make changes position the colors go wrong. I would like to create a script that fixes the makers to their colors, so that after an update I can just run the script and am done .
Unfortunately I do not really know how to work with VBA (I can just steal macros and modify them a bit to my needs or use the recorder).
I need to coloring percentage values in excel 2000. What I would like to do is have percentages greater than zero shown in green, while negative values are red, and zero values are black. I currently have the custom formatting so negative values are red and all others are black, but I cant' seem to find a way to change it so greater than zero numbers are green and zeros are black. All I can do is chang positive and zero at the same time. Here is what I currently have:
Cells in column B need to be colored red if they are equal to 50% of the cells in column C in the same row, or if the cell in column C in the same row is 100.
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??????
With a range of cells (a1:ao41) I want to color several cells depending on the value of that particular cell and another color when that particular and a corresponding cell have both the same value. It's some sort of sociogram The first row and column both contains names of students. Let say the students have to choose the three most populair ones to work with and the three most unpopulair ones. Positive ones get the color blue, negative ones get the color yellow. That's easy to do with conditional formatting. But now comes the tricky part. If for instance Student 3 chooses Student 7 and Student 7 chooses Student 3 the color of both cells have to be green if both positive and red if both negative. That's also do-able with conditional formatting, but it most be much easier to do with VBA. I just don't know how .
I have reviewed many a thread today looking up how to color in cells c11 through c20 without conditional formating.
if these cells have a time less than 01:00 then they turn green if these cells have a time greater than 01:00 then they turn red if the time is equal to 01:00 then they turn white.
I have this code that colors the lines in my Line Chart
Sub ColorLines()
Application.ScreenUpdating = False
Set chrt = ActiveChart 'Here you want to put your own graph vals = chrt.SeriesCollection(1).Values 'These are the values to determine later whether the line moves up or down. In this example, I just colored 1 seriescollection (= one line in your line graph), with an extra loop you'd do all lines.
It is very simple: When the lines goes up they turn black. When they go down they turn red.
The code work, but only if all cell in the series is filled. If one cells is blank the code error. I'm using #NA() in all cells that are blank, so the chart skip that cell and continue from the next cell's value.
i hav sheet containing list of employee number, think it is a labour camp, each room have four labours, each have a unique code. I made sketch in excel and enter all labour number in each cell, border them look look like a room, i have 120 rooms like that.
Now the problem :D, i hae different national labors
I have seperate list having their national according to their employee code.
Now i wanna color thier cell according to their national
I am searching a code and formula for last 45 days...
