What would you suggest to use as a database? I currently use Excel to store all the data I use for my reports. Sharepoint is a nightmare.
Depends on the size of your datasets. Ideally you have a data warehouse that you use SQL server or some equivalent tool to query. I use excel as a visualization tool, primarily. It's great for presenting data (especially for the end user to have input in slicing and dicing the data) but inefficient at large scale manipulation.
Ha that is the truth. Excel is pretty functional and can be used for more than just a spreadsheet or database. I use it to create a wide range of documents. Dont really get the hate.
I have my databases split up into 19 different files. the largest one is just over 900kb. the longest tab is about 1k rows of data. i still need to keep the end product in excel as it is a report that summarizes the data in tables and graphs. since they are linked once the data is manually put into the databases the report file's graphs and tables all update automatically.
Have you encountered any significant pain points like file versioning creating errors, collaboration, speed? Sounds like your Excel "application" was a easy and low cost way to start (especially for data entry), but you might be needing to upgrade to a SQL backend -- there are costs/benefits to consider for sure. And I mean cost in Time/Effort primarily for the switch. A lot of my Fortune 500 clients went through a similar growth in their Excel "apps." If you are interested, I could run through the options with you. Big Data software and data integration is my profession. Some key questions are: What is the source of your data? Who manages the source data? Do you have IT resources for a switch? But the biggest question is: What are your current pain points with keeping to status quo.
Yes, I understand. I disagree, though. Excel (.xls) is a crap system for sharing - in my opinion, of course. CSV is a fairly agnostic format. That's what's so odd - after all their work, their code, their models, etc, they haphazardly dump everything into an Excel file.
On of my all time favorite memes cause I have seen so many people lie on resumes then look crazy when they were asked to do something they claimed Rocket River
Wow. So this is how the financial industry works? Absolutely amazing. This is from JP Morgan's internal audit concerning the London Whale trade. http://files.shareholder.com/downlo...-4728-9582-625e4519b5ab/Task_Force_Report.pdf [rQUOTEr] As part of his response to Internal Audit’s recommendation to more clearly demonstrate and document the use of thresholds, this individual immediately made certain adjustments to formulas in the spreadsheets he used. These changes, which were not subject to an appropriate vetting process, inadvertently introduced two calculation errors, the effects of which were to understate the difference between the VCG mid-price and the traders’ marks. ... For example, CIO VCG did not have documentation of price-testing thresholds. In addition, the price-testing process relied on the use of spreadsheets that were not vetted by CIO VCG (or Finance) management, and required time-consuming manual inputs to entries and formulas, which increased the potential for errors. ... The Model Review Group noted that the VaR computation was being done on spreadsheets using a manual process and it was therefore “error prone” and “not easily scalable.” ... CIO relied on the model creator, who reported to the front office, to operate the model. Data were uploaded manually without sufficient quality control. Spreadsheet-based calculations were conducted with insufficient controls and frequent formula and code changes were made. Inadequate information technology resources were devoted to the process. Contrary to the action plan contained in the model approval, the process was never automated. ... During the review process, additional operational issues became apparent. For example, the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another. In addition, many of the tranches were less liquid, and therefore, the same price was given for those tranches on multiple consecutive days, leading the model to convey a lack of volatility. While there was some effort to map less liquid instruments to more liquid ones (i.e., calculate price changes in the less liquid instruments derived from price changes in more liquid ones), this effort was not organized or consistent. [/rQUOTEr]
I've worked in multiple large scale international companies. I can tell you that a scary amount of large decisions are made from excel and power point. Huge companies that run performance management by emailing out a spreadsheet. That gets emailed out again. Numbers are entered and emailed back. Compiled by an analyst. Sent up the chain. Compiled again. Put into a PP, sent to decision makers. Rinse, repeat. Ugh.
Excel isn't the problem, the "researchers" are. Of course the default settings in Excel are going to convert data to the most used formats. That means most users don't have to change anything for their daily needs. However, Do you think the SEC is okay with investment banks having false numbers in their reports because their accounting teams used the wrong formulas? No, because their entire job relies on having data represented and reported accurately. There is a reason that you shouldn't take all "scientific research" seriously. John Oliver covered it perfectly: <iframe width="560" height="315" src="https://www.youtube.com/embed/0Rnq1NpHdmw" frameborder="0" allowfullscreen></iframe> It is easier to blame the software though. No, that is how bad investment banking works. There are a lot of great investment banks out there that are risk-adverse, and insure that crap like this doesn't happen. I happen to work for one of those companies... and sure, we could make more money if we weren't so risk adverse, but we have been profitable for over four straight decades because we are.
Excel isn't the problem. Excel is awesome. It's the people who misuse or abuse Excel that should be blamed.
There are several options, and it all comes down to your usage scenario. Microsoft has made a revitalization in their efforts to bolster Access after years of all but ignoring it. Also, there are SQL databases - which are much easier to get into know with hosted SQL solutions in Azure. Both databases tools allow for front ends to be easily written in Windows Forms or .NET's XAML. Then there is URL="https://support.office.com/en-us/article/Power-Pivot-Add-in-A9C2C6E2-CC49-4976-A7D7-40896795D045"]Power Pivot[/URL], and PowerBI in conjunction with SharePoint, Excel or other datasources. SharePoint isn't a nightmare, most SharePoint admins are the nightmare. SharePoint is a fantastic platform when implemented correctly and to best practice... the problem is the vast majority of SharePoint installs are performed by admins that literally put the DVD in and hit next, next, next. If you have a ton of data, you need to move beyond Excel. Yes, Excel can handle a ton of data - but you are limiting your speed and functionality when you hit certain thresholds, especially if you are running the 32-bit version. Power Pivot, and PowerBI are the future. The traders at my company pushed Excel 2013 so far recently that Microsoft actually sent an engineer from the Excel Product group onsite to capture data with me for four days. The data captured lead to severak performance hotfixes released to the general public in June 2013, but even then many of our traders are using Excel beyond its intended capabilities. For those interested, Microsoft and edX.org are currently holding a free self-paced training course that is heavily geared towards Power Pivot called, Analyzing and Visualizing Data with Excel.
The problem I have with Excel is that Excel mixes both functionality and data. In Excel, the data (object) that you are looking at may very well be an aggregated or calculated result from a another dataset. Nothing wrong with that, until you realize that behind the numbers lie a formula. In my experience there has been one major pain with this style of number crunching: figuring out what is data and what is a function. Viewing functions inside a "cell" is a pain in itself, especially when you begin nesting (particularly with logical statements). For my own "personal" use and long-term career goals, I have switched over to RStudio. It is 10x easier to write functions, keep track of different datasets, and keep track of files - plus you can concurrently use git version control. The ability to "package" everything together makes Excel look like something only junior-high school students would use for their science fair project. However, that statement does not hold true for these two fifth-graders: https://benaracreations2015.shinyapps.io/gr5project/
