hey everyone! I've created an excel spreadsheet that does some basic calculations and I want to convert this to an interactive online calculator, can anyone recommend a good conversion program, or does anyone have any tips/advice? thanks a lot in advance, Ellen xxxx
I only need sheet 1 of the attached file
Javascript?
i don't know any javascript, i guess now is the time to learn!
How's this:
https://jsfiddle.net/r02389Lr/embedded/result/
PHP Code:var t;
var workings = function (options) {
var currentDate = new Date(new Date().toJSON().slice(0, 10));
function getInterestAmount() {
return ((options.debtOwed * options.interestRate * daysLate()) / 365.000) || 0
}
function daysLate() {
return (((options.datePaid || options.dateDue) - options.dateDue) / (1000 * 60 * 60 * 24)) || 0;
}
function dailyInterestRate() {
return (getInterestAmount() / daysLate()) || 0;
}
function getCompensation() {
return options.debtOwed > 1000 ? 100 : options.debtOwed >= 1000 ? 70 : 40;
}
function getTotalOwed() {
return getCompensation() + getInterestAmount() + parseFloat(options.unpaidDebt || 0, 10);
}
return {
daysLate: daysLate(),
interestAmount: getInterestAmount(),
dailyInterestAmount: dailyInterestRate(),
compensation: getCompensation(),
totalOwed: getTotalOwed()
}
};
var dataBind = function () {
var config = {
datePaid: new Date($('*[data-bind="paidDate"]').val()),
dateDue: new Date($('*[data-bind="dueDate"]').val()),
interestRate: ($('*[data-bind="interestRate"]').val() / 100),
debtOwed: $('*[data-bind="debtOwed"]').val(),
unpaidDebt: $('*[data-bind="unpaidDebt"]').val()
}
t = new workings(config);
$("#compensation").html(t.compensation.toFixed(2));
$("#interest").html(t.interestAmount.toFixed(2));
$("#dailyInterest").html(t.dailyInterestAmount.toFixed(2));
$("#totalInterestComp").html((t.interestAmount + t.compensation).toFixed(2));
$("#totalOwed").html(t.totalOwed.toFixed(2));
}
$(document).ready(function () {
$(".date").datepicker().on("changeDate", function (e) {
dataBind();
});
$(document).on("keyup", "table input", function () {
dataBind();
});
});
Last edited by Kyle123; 06-16-2015 at 04:28 AM.
Thanks so much Kyle123 that's brilliant!
I'm looking through your code and I'm new to this so bear with me, but I don't see how you got it to look so nice on the browser! id your html bit not included there?
oh actually no worries I found it on the jsfiddle editor, i love this program its so user friendly!
You got everything you need?
yeah i think so! i am now trying to edit it to incorporate interest rates as a function of the inputted date rather than the user having to input the interest rate themselves and I have accidently stopped the program from actually producing answers somehow but im working on it!
Dates a a pain in JavaScript since they often include times unless you explicitly prevent them from doing so. If you've got any questions, feel free to post back - if you save your work on jsfiddle and post the link I can see where you're up to and update
Hey do you have any ideas about how to deal with the dates?
this is where I'm at now https://jsfiddle.net/r02389Lr/8/
basically I want to be able to write a function called getInterestRate which calculates the interest rate from the date input using these interest values and dates:
3/5/2009 0.500 %
2/5/2009 1.000 %
1/8/2009 1.500 %
12/4/2008 2.000 %
11/6/2008 3.000 %
10/8/2008 4.500 %
4/10/2008 5.000 %
2/7/2008 5.250 %
12/6/2007 5.500 %
7/5/2007 5.750 %
The dates mean date that interest rate changed.
So the function would take the input date, and give the corresponding interest rate at that time, added to the base interest rate of 8%.
I.e. if date input was 05/05/2015, the output interest rate would be 0.500% +8.000% = 8.500%.
I've been searching google but no dice so far.
Ellen xxx
In this context, which date would tat relate to? - Wouldn't you need a start date?
well this is a more complete list of the base rate interest changes http://www.moneyworld.com/bank-base-rates.htm but i dont think that the program needs to work for debts older than 2007.
the data above tells you when the interest rate was changed and what it was changed to and it is assumed that the interest rate then stayed the same until the next change date.
the function would need to look at the input date "date due" and work out between which interest rate change dates this falls, and hence tell you the interest rate on that date.
This is so hard to explain i feel like the word date isn't even a word anymore!
Is it possible to therefore have multiple interest rates applying should more than one period have passed, or is it just the interest rate at the time the loan/debt was due?
yes, the interest rate will also change, so the date would need to be sliced into different sections.
First step I guess is to assign the dates numerical values.
Also I have noticed that this isn't working on internet explorer, only on google chrome which is strange, I will look into it
not really, Internet Explorer is crap. Which version are you using and which versions does it need to work in?
Also, this seems to be an issue with jsFiddle rahter than the code, try running it locally - you can't keep it on jsFiddle
Also, try: https://jsfiddle.net/r02389Lr/13/
Though it doesn't tier the rates, that's more complex
Last edited by Kyle123; 06-16-2015 at 10:10 AM.
I'm not sure which version it is! how can i find out? I guess it needs to work in all versions because the idea of the project is that it will go on the company's website as a free debt calculator for people to use with the hope of bring in business.
I know this will make me sounds like such an idiot, but I'm new to coding (2nd day on the job!!), how can i run it locally, as there are 2 parts to the code, the HTML bit and the Javascript bit?
Thanks I will have a look at the link
you're really fast at this! does this assume the rate stays the same since the debt has become due?
I hope so, it's my job
Yes, I'd need to know how the calculation works before adding the tiers, if you upload a workbook with the desired logic, I'll have a look
Hey Kyle, So I've made a new excel sheet which does everything, including calculating the tiered interest.
Do you know how one would make a web form which can take input data, put it into a spreadsheet, and then produce output values?
Sorry here is the spreadsheet https://docs.google.com/spreadsheets...it?usp=sharing
That depends what you mean. To write to a workbook, you'd usually need a server side language like PHP, C#, Python etc - it's a bit more involved than just JavaScript. If you mean that you want to put values into the spreadsheet from a webform, run the Excel calculations and return the result, then no, it's not. Firstly, you'd need to be running a windows server - you can't run Excel on Linux so that would rule out about 70% of hosing solutions and put your costs up, but secondly, and more importantly, Excel is not supported in a server Environment as it can't handle simultaneous connections and MS explicitly tell you not to do it.Do you know how one would make a web form which can take input data, put it into a spreadsheet, and then produce output values?
Ok I understand, I guess I will have to try and code up the tiered interest rate stuff in Javascript then! I was trying to avoid it as it will be very difficult!!
Try this: https://jsfiddle.net/r02389Lr/19/
Wants refactoring a bit, but you should be able to follow the logic. To note, it's important that the dates and interest rates are put in descending order as in your spreadsheet.
Actually there's a bug in this that isn't right if the due date is later than the last interest rate - I'll fix in the morning
Last edited by Kyle123; 06-17-2015 at 12:55 PM.
Thanks a lot kyle
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks