如何设计一个程序来使用 .NET 执行复杂的财务计算
好吧，我希望这不是一个太宽泛的问题，但我的好奇心战胜了我。 我在一家大型保险公司工作。 我们正在构建 Excel 电子表格来处理人寿保险保单未来现金价值的预测。 这些是相当大的工作簿（40-50mb），有大量的工作表和列，必须考虑大量的变量才能准确地预测现金价值。
如何将其构建为桌面或业务应用程序？ 对于初学者来说，我对 .net 相当了解，但我在电子表格思维方式之外思考以及如何将 .net 应用到类似的事情上时遇到困难。 在电子表格中，需要 3 或 4 页、60 列、每行 1300 行的代码，您将如何跟踪计算？ 以及根据年龄、性别、吸烟者/非吸烟者等输入动态改变利率、费用和其他影响政策的因素......
Alright, I hope this isn't too broad a question but my curiosity got the better of me. I'm working for a large insurance company. We are building excel spreadsheets that handle projection of future cash value for life insurance policies. These are pretty large workbooks(40-50mb) with a ton of sheets and columns that have to take an asinine amount of variables into account in order to accurately project cash values.
How would this be built as a desktop or business application? I know .net fairly well for a beginner but I'm having trouble thinking outside of the spreadsheet mindset and how you could apply .net to something like this. How would you track a calculation in code, that takes 3 or 4 pages with 60 columns at 1300 rows each in a spreadsheet? As well as changing interest rates, fees and other things that affect the policy, dynamically based on inputs like age, sex, smoker/non smoker, etc...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
还要确保不要使用浮点数或双精度数进行涉及金钱的计算。 使用十进制。
Also make sure not to use floats or doubles for calculations involving money. Use Decimal.
这确实是一个简单的问题……但答案却很复杂。
您问：我如何规划一个复杂的软件项目？
了解您的环境（听起来就像您所做的那样）：
了解软件工程：
了解您的语言/开发环境：
如果您对开始感到紧张，那么我会创建一个简单的功能原型。 首先以 Balsamiq Mockups 之类的方式可视化项目，以便您可以看到拼图的每一部分将如何一起来。
如果您花几天时间研究软件工程方法，您将节省大量时间的头痛。 从那些已经完成了您想做的事情的阿尔法极客们向我们提供的良好实践开始。
This is really a simple question... with a complex answer.
You are asking: How can I plan a complicated software project?
Know your environment (which you sound like you do):
Know software engineering:
Know your language/developing environment:
If you are nervous about starting, then I would create a simple functional prototype. Start with visualizing the project in something like Balsamiq Mockups, so that you can see how each piece of the puzzle will come together.
If you take a few days of researching software engineering methods, you will save yourself hours and hours of headaches. Start with good practices gifted to us from those alpha geeks who've already done what you're trying to do.
SpreadsheetGear for .NET 可让您直接在 .NET 应用程序中使用 Excel 模型，无需使用 Excel，也无需花费大量时间每次业务人员想要更新模型时，都会花费大量时间转换为 C# 或 VB。
如果您想亲自尝试，可以在此处下载免费试用版。
免责声明：我拥有 SpreadsheetGear LLC
SpreadsheetGear for .NET will let you use your Excel models directly in your .NET applications without Excel and without spending a lot of time converting to C# or VB every time the business folks want to update the model.
You can download a free trial here if you want to try it for yourself.
Disclaimer: I own SpreadsheetGear LLC
您必须一次将事情分解，然后从数据库设计开始。 考虑一下基础表。 您可以浏览 Excel 中的每个工作表，并思考如果获取相关信息并将其放入数据库表中会是什么样子。
数据库视图非常适合进行基本数学计算，因为它可以基于许多表进行计算并以良好的格式呈现结果。
You'd have to break things down one piece at a time, and start with database design. Think about the underlying tables. You could go through each sheet in excel and think about what it would look like if you took information that is related and put it into a database table.
Database views would be excellent for doing basic math, since it can do calculations based on many tables and present the results in a nice format.
我采取的方法如下：
Here's the approach I'd take:
考虑一下电子表格，因为许多事情都在比 C# 代码更高的抽象级别上工作。
因此，要在 .Net 中重新实现您的工作表，您可以采用一些广泛的方法
Consider for a moment that speadsheets, for many things are working at a higher level of abstraction than C# code is.
So to reimplement your worksheets in .Net you have a few broad approaches
添加一些细节：
单元格值成为数据库中的变量或条目。 例如，B1 的公式 = A1 + 2（其中 A1 包含给定策略的小部件数量），您可以：
var widgets =（从 db.Policies 中的策略中选择新的 {policy.widgets}）.First().widgets;var moreWidgets = widgets + 2;
函数和宏得到移植——例如，Round(B1, 0) 变为 Math.Round(moreWidgets, 0, MidpointRounding.AwayFromZero)
执行所有这些操作的好处（假设做得很好）是：
To add some specifics:
Cell values become variables or entries in a database. E.g., Instead of the formula for B1 being = A1 + 2 (where A1 contains the number of widgets for a given policy), you have:
var widgets = (from policy in db.Policies select new {policy.widgets}).First().widgets;var moreWidgets = widgets + 2;
Functions and Macros get ported--E.g., Round(B1, 0) becomes Math.Round(moreWidgets, 0, MidpointRounding.AwayFromZero)
The benefits to doing all of this (assuming it's done well) is:
我敢打赌你们公司正在做> 每年 10 亿美元，全部基于 Excel 电子表格。 你并不孤单。
我会首先考虑问题而不是 Excel 电子表格。 业务问题是什么？ 你能用物体来建模吗？ 我看到了一些对象：策略，以及所有随之而来的覆盖范围和类别子项； 有保险，有性别、吸烟者等特征——你懂的。
投影听起来就像您将拥有一些“假设”变量，这些变量将具有一些您会改变的统计属性。 您的工作将是运行大量类似蒙特卡罗的模拟，通过从样本中提取数据并计算输出来改变变量。 您的结果将是带有标准差和置信水平的预测平均值。
可能是大规模并行计算的良好候选者。
计算的基础是什么？ 有哪些比较出名的车型可以分享一下吗？ 也许这会给出一个提示。
I'll bet your company is doing > $1B annually, all based on an Excel spreadsheet. You aren't alone in that.
I'd start by thinking about the problem instead of the Excel spreadsheet. What is the business problem? Can you model it with objects? I see a few objects: Policy, with all its attendant Coverage and Class children; Insured, with characteristics like gender, smoker, etc. - you get the idea.
Projection sounds like you'll have a handful of "what if" variables that will have some statistical properties that you'll vary. Your job will be to run lots of Monte Carlo like simulations that alter the variables by drawing from a sample and computing the output. Your result will be a projected mean value with a standard deviation and a confidence level.
Could be a good candidate for massive parallel computing.
What is the basis for the calculations? Any well-known models that you can share? Maybe that would give a hint.