I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...
How to modify the code a bit...
VB:
Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown
I tried to change the code, but it did not worked.
I am not good at excel. I would like to split buy and sell orders from the live feed and updating when new feeds come in.
LIVE FEED
BUY/SELLQTYPRICE BUY QTY PRICESELL QTY PRICE
B 4150
S 6200
S 4300 CHANGE TO
B 3350 B 4 150
B 2180 B 3 350 S 6 200
S 2220 B 2 180 S 4 300
B 1120 B 1 120 S 2 220
I have set an excel spreadsheet set up to update live data from a URL every 5 minutes. However I am wanting to do try and save a bit of time if possible.
Basically I am monitoring horses bought on a game website. Each horse has the same URL except for the number at the end which goes up in 1 increments. The URL is already set up and when the horse is bought it changes from being blank to having the horses details etc.
At the minute I am able to autofill cells with each uinique URL using fill, then series menu. This is so I dont have to manually enter each URL, as hundreds may be bought each day so it would take too long to do this.
My question is this, am i able to have a automatic series linked to the 'data from web' function in data tab, instead of having to manually adjusting each URL for each individual horse.
I new to excel and vba , and i have live stock feed data in excel.
The live feed has 17 rows..of ticker names and more column about values.live feed starts at 9.00 to 3.00
I want only 13th column data to save for every 5 min..in new spreedsheet.
I am also including a sample : live crude.xls‎
I am looking to get a Live Clock changing every minute..
Lets say I put the Total Time in the Cell A2 = 15 minutes and I have a Command Button next to it "Start"..
On the Click Event I want the Actual Time time-stamped at that moment in the Cell A3 and in Cell A4 the Time should keep on Ticking...
Can someone attach a file in Windows 2003 i.e .xls format and also explain the code if possible.
I need to calculate how much time I've got left before the earliest order needs to be despatched (it might be 2 or more on every day). This wouldn't be a problem if orders would be placed daily. But for every item it varies. For example: for "X" product there is an order in 3 days time to be despatched at 19:00. I have built a live clock in the spreadsheet but I can't work out the formula.
P.S. Also I need two time formats, first - days(text) hh:mm second - just a numeric value that can be formated as [hh:mm].
create a Macro that runs when system clock changes minute and then at every change of minute of system clock.
For example, if I give the execute command to start the macro at 09:14:45 (HH:MM:SS), then its first run should be only at 09:15:00, then next run at 09:16:00 so on...
I already have a Macro that runs every minute from initial run time, using
Code:
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
but it seems to be unreliable, because in case I open another sheet that takes lets say 20 seconds to open, the next macro run is delayed by 20 seconds.
Is There a formula for current Month based on system clock....
View 10 Replies View RelatedHow do I run this loop 10 times ....Then using a timer or system clock to restart it.
Code:
Public Sub Workbook_Open()
Dim ws As Worksheet
Do
For Each ws In ThisWorkbook.Worksheets
[Code]....
I want the following macro to be used as an excel addin, how to create addin and feed this macro on that particular addin so that its available each time I open a new workbook.
The macro is : [Code] ...........
Can a group of cells or more be locked once they reached or passed a certain time frame and date that follows your PC clock, and can it be set to a time that you decide on in a macro. The other question, Can this lockout macro clock that was set to a pacific time frame can it stop people from altering their PC clock in anyway for e.g.; 'I tried to turn back the time but I got this message - "This cell is Locked due to your lateness".
i try create formula or macro, which will search in data during writing in cell.
F.e. i have column
A
1 dano black
2 dodo red
3 phil blue
4 black jack
I want something, what will be look in this column "A" up, during my writting in cell B1 and show results in C1. When i write „b“, it will show in B1 „dano black“, if i add „l“ (it will be written „bl“), it shown again „dano black“. If i add „u“ (it will be written „blu“) , it shows „phil blue“
I would like to create the clock function.
This is my function name. - Clock()
If i put this function in Cell A1. Then from now onwards cell A1 will display system current time. It will keep showing the every seconds changes.
And clock will run itself independenly.
I need to develop a System using Macro in MS Excel 2007.. but i don't know how to do it..
I got 2 file in MS Excel
File number 1 : Demand - file name : ( got 55675 row of data)
File number 2 : Supply - file name : ( got 6774 row of data )
File number 1 : The data is starting from 0110111101 ( commodity code ) until 9999999999 ( commodity code )
File number 2 : The data is starting from 1532423007(commodity code) until 9119199901 (commodity code )
I need to categorize all this data in 10 sheet ( sort by the first 2 digit of the commodity code ):
Sheet 1 : 00 -09
Sheet 2 : 10- 19
Sheet 3 : 20 - 29
Sheet 4: 30- 39
Sheet 5: 40 - 49
Sheet 6 : 50 - 59
Sheet 7 : 60 - 69
Sheet 8 : 70- 79
Sheet 9 : 80- 89
Sheet 10 : 90 - 99
I have no experience with macros at all but i need to create a macro for work that will keep the original data entered but work out the difference between new data collected each day ...
View 10 Replies View RelatedI export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted
I've been using the following bit of code to run a macro at the specified time:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application .OnTime TimeValue("12:00:00"), "GetData"
End Sub
The GetData sub executes a shell script and then pulls data from the result. The problem is that the macro runs 140+ times, and so I get 140 windows popping up and the system practically stops. I can't figure out why this is happening, as there are no loops or any sort of repetition in the code. Any help is greatly appreciated as this problem occurs with more than just the one spreadsheet.
I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
I am thinking of commanding macro to select worksheet tab based on the system date to do some pasting.
My worksheet tab is as follow:
Nov-12, Dec-12, Jan-13, Feb-13 and .........
What should be the macro script required to select the worksheet tab based on today's date?
i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
If I have a database of 1000 records that are logged and counted as to which of these records are successful and unsuccessful sales,
can excel be programmed to give an accurate forecast of what will be the number/ percentage of successful sales records over the next 100, 50, 30 and 10 new record entry ranges, using the database history?, then which of the new records within each selected range are most likely to be successful sales using the database history?
If so, what components or data references are needed in a worksheet to meet these requirements to allow for creating an accurate forecasting application within excel?
I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.
I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.
Here's a sample of the data....
I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.
For example I have two months and I want to know how to calculate the forecasted Jun 09 total: ...
I am trying to come up with the appropriate formula that can be used for possible forecasting purposes. Countif proves to be futile due to more than one criteria that needs to be met. Sumproduct seems to be more feasible but I am unable to get the correct formula or maybe I am wrong.
Here's the objective...I need to be able to plot the total number of people working on a project on a daily basis - # of people working on a Monday, Tuesday and so forth by means of referring to start and end date column..I am open to revisions so as to streamline everything.
I do forecasting for my company. We have a forecast every quarter. Any good templates (simple or complex) for presenting different quarters (or time periods) and the actual results for management?
View 5 Replies View RelatedHave to forecast/create a trend for the current year based on the data available for last two years. Below is the format of data. The current year data is kept blank as the data needs to be filled based on forecasting/ trending.
30-Apr-0931-May-0930-Jun-0931-Jul-098/31/20099/30/200910/31/200911/30/200912/31/20091/31/2010
2/28/20103/31/20106075443580901511256025304030-Apr-1031-May-1030-Jun-1031-Jul-108/31/20109/30/2010
10/31/201011/30/201012/31/20101/31/20112/28/20113/31/2011100150756070859611045663544
30-Apr-1131-May-1130-Jun-1131-Jul-1131-Aug-1130-Sep-1131-Oct-1130-Nov-1131-Dec-1131-Jan-1228-Feb-1230-Mar-12
I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).
I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2
>D2),(B2-C2)*F6+F2,IF((B2
I have a base model for site openings and opening schedule. I need to build a 5 yr model which reflects the base model for each opening. I have matched the month in the model to the opening month but how do I get the base model subsequent months to follow the opening month?
View 10 Replies View RelatedForecasting using historical seasonally effected data
I organize my thoughts in numbers so it will be easier to understand.
[1] Say I have this data from January 2009 to December 2009:
1-4-7-10-13-14-15-16-18-20-22-25
As you can see, the growth is not linear.
[2] So then, I start with this data point for Janaury 2010.
26-a-b-c-d-f-g-h-i-j-k-l
[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).
[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:
26-a-b-c-d-f-g-h-i-j-k-50
So how do i do this now?