Main Area and Open Discussion > General Software Discussion

excel tool - cloud or home base?

(1/2) > >>

Steven Avery:
Hi,

I have an unusual, important and mildly unique need that is probably best native to a spreadsheet app. (Although I always would consider a database or programming-macro tool alternative.)

This is an application that could be one person at a desk, or it may be shared.  If we share it, that would of course be one bonus of using a cloud app.  And Google Sheets would have an inside track since we are already sharing some stuff in Google Apps for Biz.

=============================

Looking around: here are some of the alternatives:

PC
Excel
LibreOffice Calc  (or Apache OpenOffice)

Cloud
Google Sheets
Excel Web Application
Zoho Sheets

=============================

THE PUZZLE

We have an unusual pricing method and problem.  The vendor who sends us prices can change his % markup.  And he does not tell us when he does, and he does not give us a download option.  The total number of items is about 200, it could go up if we could streamline the pricing, and about 50 of those have to be watched like a hawk.  At least once a week, since small changes can mean a real difference.  So, no matter what, we have to go into his web site, and extract current data out, one item at a time. (One item on its own page.)  Yes, we have requested some help from them finding an easier method, and will do so more in the future. At this time, it is as it is.

The spot where we put in our tiered prices is dependent on the vendor (that spot does have CSV import/export), so we have to do some manual checking of what is the current % and how does it compare.

To automate this we probably want to put the current two (actually three, a changing spot price is involved that can even change in the middle of the data input session) factors of the items from the difficult source into a spreadsheet (perhaps twice a week, we are in a volatile and sensitive market) and then have the spreadsheet calculate the % and tell us which %s are different than the last time we did this.  Then we would adjust those by hand.  The final adjustment could even potentially be programmed automatically, but that is not so important.

Without this automated, it takes too many hours per week, even for a skilled person who understands the whole thing.  Automated would still require some handwork, much less though.   Plus we want to keep the data from one review to the next, and also the archived history.

You can see that this may be a bit tricky for anybody other than an Excel Whiz. 

WHICH TOOL?

Does that mean I should use Excel (or LibreOffice Calcs) for stronger macros and stuff like that?  Will it be easier than the cloud?  I noticed that Google Calcs says they don't have macros but they do have some script language.  It sounds to me that it might be cumbersome to work that way.

Hmm.. thinking about it, it might be nice to put the data in a MYSQL type of database, if I could find the right tool for data entry and programming and printing. This would be like my Alpha5 or Filemaker or Access thoughts.

WHO? HOW?

If anybody is really skilled on this stuff, helping set it up might actually be a little remote gig. You can send me a msg.  I am a programmer-techie of sort, but my Excel background is minimal.

Should I just buckle down and learn some spreadsheet macro language?  Should I consider the old Alpha 5, Filemaker, Access alternatives to setting this up?  Or some other tool that can through up fields, take screen input, work with the data file and do calcs?  Should I just seek help?

Steven

Ath:
As your data is web-based, you'd be better off using a web-scraping tool to collect the data, instead of manually reading the numbers from your screen.

Steven Avery:
As your data is web-based, you'd be better off using a web-scraping tool to collect the data, instead of manually reading the numbers from your screen.-Ath (October 30, 2015, 02:40 AM)
--- End quote ---

Yep, your right. Thanks.  Any tool you recommend?
.
I can see 3 possibilities.  Easy front end, do it all myself.  Some scripting, I learn the specifics.  Hire it out. Definitely i would prefer to do it myself in-house and if the tool cost a bit, that's ok.

Of course, I have to also up my spreadsheet savvy, once the data is available (there is a uniform SKU for linking) so that part of the question remains.

Steven

Ath:
Well, you could get the pages from the web using curl or wget, use xmlstarlet (in html-mode) to extract your data, and output that to csv format for further processing in Excel or another spreadsheet program. Then upload the resulting file to a shared webspace or Dropbox account to distribute as needed.
Wrap it all with cmd or shell scripting, optionally using Cygwin to have the power of bash-scripting on Windows, and you're in business.

Steven Avery:
Thanks, I hope to spend some time on this today.  And we don't mind paying for a solid program with a friendly front-end (without losing much functionality of a raw scripting language) if such a program exists. Or contracting for some of the expertise needed, especially if the skilled person has a helpful teaching us approach. 

If we can do this in one day instead of a week, at the moment that would be a super-big plus.  I'd explain the company dynamic to the skilled person.

I'll check my user msg box on this.

Navigation

[0] Message Index

[#] Next page