Macro- Data That Is Exported Our House Investment System
Oct 27, 2008
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....
View 9 Replies
ADVERTISEMENT
May 5, 2012
What formula to use to show the Annual ROI for a real estate investment property (single family). I believe I have all my figures to calculate WITH (acquisition amount, annual net income, etc.) -- but I can't figure out how to determine the ROI.
Or - is determining that % even possible/logical?
View 3 Replies
View Related
Mar 26, 2007
I want to create a kind of "Housekeeping Macro" that will run based on one single piece of information a number.
I want an inputbox to ask me for a number.
Based on that number I want the macro to do the following -
Go to Ws1 - Membership
Seach down column A until it finds the number put into the Inputbox
When it finds it do the following
Go to Column N and enter todays date as a value.
Go to Column Q and Enter No
Delete the data in R:S and U:V and X:Y and AA:AB and AD:AE
Go to Column J This contains the name of another worksheet i.e. Data1
Go to the worksheet Data1
Search down Column C and find all instances of that the the number entered into the inputbox (there could be several).
Go to each instance and delete the number.
View 9 Replies
View Related
Jun 14, 2013
I 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
View 6 Replies
View Related
Jul 4, 2008
I have some monthly sales data (max 200 lines per month). The sales are from numerous suppliers (we sell on behalf of others and take a commission). Each month the sales/commission are reported back to the suppliers that have actually sold something!
The problem I have is that some supplier may have sold one item and others may have sold ten (so a simple mail merge is out of the question).
I can create the data in Excel quite easily but currently have to copy and paste this into Word. I am sure this is quite simple, but I cannot get anything to work.
View 9 Replies
View Related
Oct 12, 2012
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
View 7 Replies
View Related
Mar 13, 2014
I have a spreadsheet where the data held in column A is an export from another system and the exported data looks like this:
??????? Stn ??????? ??????? ??
What i would like to be able to do is to split out the data in column A so that the text before the 'Stn' (but including Stn) are shown in column C with text after 'Stn' is shown in column D. i have looked at using =LEFT, =MID or =RIGHT with =FIND but the problem i have is that the text prior to the letters Stn could be one, two, three or four words and the text after could be similar.
View 2 Replies
View Related
Jun 9, 2014
sample pic.png
I have two spreadsheets of data I export regularly from an outside source. I want to consolidate the data from these two sheets into one large sheet with all the data in a more concise form bringing in only the data related to certain parts I am conducting analysis on. I have a list of these parts already. My first thought on how to do this would be to use the list in an entry for an input box and have that list be the index for a for loop in which i searched for a part code, copied the entire corresponding row, and pasted it back into the master sheet. I have gone through and tried to do this but I am running into problems because sometimes I have multiple rows of data that correspond to a single part number. I have attached a template of the way that the data should look in the master file with the multiple rows of details that correspond to just one part entry.
View 1 Replies
View Related
Jun 19, 2014
I have a set of exported data from a Project Management SW (activecollab). The result is an excel .xslx file with a table inside with a lot of cells I am not going to use. Additionally, what I would like to do is creating new tables on other sheets that use only the data I want from the export. For example, I have two projects and three employees. I want to create three tables with the names of the three employees. In each table I want the tasks done by them and the time they will spend on them in order to create a Gantt chart. I need a solution that allows me to create new tables with selected data from a bigger cluster (the export). you do not need to give me the exact solution, I only need to know whether it is possible or not and where could I get the info to do what I want.
View 1 Replies
View Related
Jul 3, 2007
I basically need to add the ongoing totals for each house team-so when scores are inputted, the house teams score is automatically being added up.....I think I would need to mail someone the sheet to show how far I have got as I dont think it is a simple count formula.
View 11 Replies
View Related
Apr 5, 2013
I think I'm just missing something obvious here but I want to calculate commission based on £1 Plus 10% of Hammer Price. That bit's easy but I need it to ignore the hammer price if blank. In other words if I leave the hammer price empty, at the moment the sheet adds £1 to the commission cell.
In Summary:-
F3 is Hammer Price
K3 is Commission (=(F3*.1)+1)
K3 should NOT state £1 if F3 is empty
View 6 Replies
View Related
Dec 12, 2007
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
View 14 Replies
View Related
Jan 7, 2009
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 Related
Jan 29, 2007
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.
View 9 Replies
View Related
Sep 21, 2013
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.
View 8 Replies
View Related
Nov 21, 2012
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?
View 2 Replies
View Related
Oct 24, 2007
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.
View 2 Replies
View Related
Jan 30, 2012
I'm trying to format an exported excel database.
I'm currently running into difficulties with a text field full of user comments.
At present, there are cells with the #NAME error on them, and the only way I've been able to get rid of them is by double-clicking on them and pressing ENTER. This is also the only way I've found to remove excess white space in each cell.
Double-clicking each entry in the database would take far too long, and I'm assuming that there must be an easier way.
View 6 Replies
View Related
Oct 12, 2007
I imported a file of National Historic sights which imported fine with the exception that it's all out of order. Here is how it currently looks:
ResnameFIELDFIELDVALUE
Lake Norconian ClubApplicable CriterionARCHITECTURE/ENGINEERING
Lake Norconian ClubApplicable CriterionEVENT
Lake Norconian ClubArchitectGibbs, Dwight
Lake Norconian ClubArchitectWilson, G. Stanley
Lake Norconian ClubArchitectural StyleMISSION/SPANISH REVIVAL
Lake Norconian ClubArea of SignificanceARCHITECTURE
Lake Norconian ClubArea of SignificanceEXPLORATION/SETTLEMENT
Lake Norconian ClubCurrent FunctionDEFENSE
Lake Norconian ClubCurrent FunctionGOVERNMENT
Lake Norconian ClubCurrent SubfunctionCORRECTIONAL FACILITY
Lake Norconian ClubCurrent SubfunctionNAVAL FACILITY
Lake Norconian ClubFederal AgencyDEPARTMENT OF THE NAVY
Lake Norconian ClubHistoric FunctionCOMMERCE/TRADE
Lake Norconian ClubHistoric FunctionDOMESTIC..........................
View 9 Replies
View Related
Feb 14, 2007
I want to easily determine the max value of the stocks I own by only changing the daily value but i cannot bypass the circular reference problem. Example:
Stock|Today's Value| Max Value
A | 1 | 1
B | 3 | 3
Tomorrow i'm going to change today's value and my goal is that the max value remains unaltered if today's value is smaller. like this:
Stock|Today's Value| Max Value
A | 2 | 2
B | 1 | 3
View 4 Replies
View Related
Oct 20, 2008
I was working with automatically exporting the contents of a textbox to an image file and found this post which I was able to modify slightly for my purposes.
http://www.excelforum.com/excel-prog...ml#post1982405
Is it possible to improve the quality of the output image. I need to print out the resulting image and the quality of the image this method creates is too poor for my purposes.
View 7 Replies
View Related
Jul 1, 2014
I have a big system of data, me not knowing better i have it such that the categories is listed in the start of the row, and new data for periods are listed in the columns following. when i mark the data and click table, it puts the scroll down thing on top of the columns, which is of no use to me at all, i need them at the start of the row. is this possible, or do i need to reorganize all the data, and VBA code used to sort and collect it ?
View 7 Replies
View Related
May 14, 2009
I have Given Name for Data Column as Below
[Data_Table=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A$2:$A$28),COUNTA(Data!$1:$1))]
Now If I delete Raw 2 System is giving Error #Ref!
I may have to delete Raw-2 frequently
View 4 Replies
View Related
Dec 17, 2008
I have a specific problem on irr calculations. In the excel file, I have following data.
date of investment - 1st May 2008
Investment Value - 1000000
Investment close date - 15th Dec 2008
Closure value - 1055000
I want to calculate IRR for the investment for the days the money got invested.
How do I calculate this in excel.
View 2 Replies
View Related
Oct 20, 2009
I was looking for how to calculate the value from on cell F11 from
this excel table but my formula didn't work so I'm looking for other solutions?
ur Cell F11 indicates $104
and cell F12 $320
and F13 $658
but from my formula only works with on cell F11
I used this formula on this sheet.
=PMT(C7/12,$A$11*12,,E11)*-1
Enter Retirement Parameters as IndicatedSummary of Retirement CalculationsDate plan began1/1/2008Projected retirement date1/1/2052Number of years to contribute45TRUEAccount value at retirement$542,654Annual contribution$1,300 Total contribution over life of plan$58,500Expected rate of return8%Investment Gain$484,154Type of planRoth IRAPercentage through investment89%$0.00 $1 Contribution NumberDateValue prior to contributionContributionTotal Invested Over Life of PlanInvestment GainValue at end of the year11/1/2008-$1,300 $1,300 $104$1,404$104 21/1/2009$1,300 $2,600 $320$2,920$320 $216 31/1/2010$1,300 $3,900 $536$4,436$658 41/1/2011$1,300 $5,200 $5,200$1,127 51/1/2012$1,300 $6,500 $6,50061/1/2013$1,300 $7,800 $7,80071/1/2014$1,300 $9,100 $9,100
View 9 Replies
View Related
Apr 21, 2007
If I want to obtain a future value of $500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum? I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/ Name/Define) the answer to this function as Pmt1.
By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing $500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works. Worse, I do not know whether it is the correct answer. I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: $1000 per annum is invested for 20 years. The interest earned on the $1000 is 10% per annum. The $1000 increases by 5% each year - i.e. 19 increases)
=Pmt1* SUMPRODUCT((1+5%)^(ROW(OFFSET($A$1,0,0,20,1))-1),(1+10%)^(20-ROW(OFFSET($A$1,0,0,20,1))+1))
Assuming the payment does not increase, I simply replace the 5% with 0%. When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0". What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do? This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!
View 5 Replies
View Related
Oct 24, 2007
I am part of an investment sales team and was wondering if the use of specific excel based investment calculators will assist me in my task of advising clients of the best options. Also; if these tools do help, I wanted to create a calculator that displayed the interest schedule depending on the term that its invested for. I have searched and found various threads on this topic; though not too sure how
View 2 Replies
View Related
Jul 1, 2013
I've just exported a list from SharePoint to Excel, which gave me as a result a owssvr table, so far so good cause every time the list in SP is updated I can do a refresh and get the latest values; however once I opened the table I realized that the columns where not correctly order they were all mixed and the information does not look as it is required so I need to rearrange them. I do not need to delete a column I just need to be able to move them between themselves so that they will follow certain order: Product ID, Name, Amount of pieces, Place where they are stored, etc. this is very important because later on I use "vlookups" to do a series of reports.
I've tried cut- paste to move the columns to the correct position, unfortunately once I close the excel file and try to open it again, I got a message saying that the content is not readable and when Excel repairs it, my owssvr table loses the link to SP so I cannot update refresh the table anymore.
I cannot edit the list in SP as this site does not belong to me and I only got access to export the data and be able to refresh the table, all I want is to be able to move them within my ovssvr table so that locally I can work with them better.
Here is a pic of what I am talking about: cmms.JPG
So for example in the pic I put, I need that instead of Comments in Column E, Product ID can be in Column E, then Name in column F and so on..
View 1 Replies
View Related
May 19, 2009
I have a blank template spreadsheet that is copied every time I need to work on some data exports from another system (the data needs to be kept, hence the copying of the original blank template first).
I have set up conditional formatting using;
=MATCH($A1,range,0)
This formula is always in the entire column of row A in Worksheet1 (for the sake of argument)
Problem is in Worksheet2 where the range it's using to compare is always a different number of rows deep...it can be 70, it can be 2000. I always call the range 'interest'.
Is there anyway (in the blank template) of setting column A as a dynamic range called 'interest' so that after data import it automatically sets the range at the desired size?
View 2 Replies
View Related
Aug 12, 2013
I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.
View 1 Replies
View Related
