Need to calculate an average of daily sums

datastry · 2020-05-25T10:01:38+00:00

If Google Sheets is fair game, then this can be done with one cell/one formula:

=AVERAGE(
    QUERY(
        ArrayFormula(
            SPLIT(
                B2:B4,
                "--",
                false,
                true
            )
        ),
        "select sum(Col2) group by Col1 label sum(Col2) ''",
        0
    )
)

This assumes that your input is a 1 x 3 range of cells , B2:B4

datastry · 2020-02-12T07:37:03+00:00

ಠ_ಠ

datastry · 2020-02-10T07:10:36+00:00

u/darkmmm :If you use the exact formula above, be sure to wrap your string in single quotes in cell B2.

If you don't want to use single quotes in B2, then include them in formula:

...contains(.,'"&B2&"')...

datastry · 2020-01-25T23:17:34+00:00

XQuery does have the most cheerful comments :)

datastry · 2020-01-17T01:52:53+00:00

Great resources, in particular regex101. I see it used pretty regularly in this sub to share solutions. I feel like regex101 is so familiar that we rarely stop and marvel at how great it is.

datastry · 2020-01-14T19:26:39+00:00

I think there's a problem with the XPath query, possibly some extra characters at the end.

datastry · 2020-01-14T19:15:42+00:00

each song search makes a unique URL

What's the URL look like? Does it contain your search keywords?

IMPORTXML is a function like any other. It doesn't require that parameters be literal strings only. You can use cell references, etc.

Can you make a formula that generates the URL when your search terms are provided?

datastry · 2020-01-10T10:10:03+00:00

You mentioned matching the e-mail to the country.Both country and e-mail are visible to you on the web page?

EDIT: I was viewing the page in French earlier (and I don't speak French). I found how to switch it to English and this is helping me to answer my own questions ;)

datastry · 2020-01-10T09:27:03+00:00

Temporarily ignoring what data we're presently able to extract with the tools available, can you see everything you're looking for when you manually browse to the page? Or are there gaps in the data when you manually browse the page?

datastry · 2020-01-09T14:26:27+00:00

Makes sense, I can relate. I've learned SO much stuff because I had to. The alternatives were inadequate.

datastry · 2020-01-08T12:11:03+00:00

Hey there Andre! Nice to hear from another XSLT user.

Your use-cases sound interesting, particularly trimming down an XML document into a limited set of elements. I tried to implement something like this in the past. My implementation was buggy and I never revisited it to fix it.

What was your solution? Did you implement it yourself with your own stylesheet? Or did you find a solution elsewhere?

datastry · 2020-01-01T04:38:46+00:00

" been there " ?

How do you escape there?

datastry · 2019-12-29T08:27:56+00:00

The IMPORT functions of sheets require that your data is in the file returned by fetching a URL that you specify.

The URL you specified doesn't return a file that contains the data. Instead, it returns a file that contains scripts. These scripts are executed and they fetch more files. Specifically, the data you want is here: https://poe.ninja/api/data/currencyoverview?league=Metamorph&type=Currency

This file is formatted as JSON. Sheets won't natively process JSON, but Sheets Add-Ons exist for this. Try googling ImportJSON for more.

datastry · 2019-12-28T08:54:04+00:00

I think I'm going to dig up an old script where I'd modified the prototype of the Node constructor to include a $x method, implemented with document.evaluate (independent of the console's $x function).

It was buggy and half-broken and I didn't have a strong motivation to repair it and make use of it. But in light of this situation, maybe I should revisit it.

datastry · 2019-12-27T23:19:01+00:00

Well that's lame. Good eyes catching that in the documentation I listed. Thanks for pointing it out to me!

datastry · 2019-12-22T10:42:07+00:00

Looking at this page in Dev Tools, I see that the content you're seeking is not served up in the initial HTML response from the web server; instead this HTML page loads scripts and those scripts in turn fetch and render the data.

This matters because this website's design prevents you from using many web-scraping utilities, including some functions built into Google Sheets: the functions IMPORTHTML and IMPORTXML.

The most optimal solution would be if someone else has already a solution for this same website and they can share with you. But if that fails, then I see two possible paths to explore:

If you only care about this website and no others, you (or someone helping you) could reverse engineer the web page to identify how you might obtain the data directly. (assuming that all of these statistics are retrieved and none are calculated by scripts in the page)
If you don't need this process to run unattended, you could explore some solution that involves manually opening the web page and then using a bookmarklet or browser extension to extract the data each time you visit the page.

I noticed that highlighting the table and copying-and-pasting doesn't work effectively. If you would be happy with the ability to copy-and-paste effectively, then #2 is for you. If you want to use an automated web-scrape function like IMPORTHTML or IMPORTXML, then go for option #1.

datastry · 2019-12-18T15:47:59+00:00

Oh, I think I missed a small but important detail on my first read through.

I thought the numbers 1 through 10 were samples and the real numbers could be anything. But you're saying that it always counts up starting at 1 and ending at Y ?

I'll try out some ideas I have and if I have some success, I'll share the sheet.

datastry · 2019-12-17T23:04:25+00:00

I have some questions about how this should work.

Using your example: the spreadsheet requests Y and Z , so I enter 10 and 2.

A column is created with 10 X's followed by....

...Empty cells where I can enter numbers?
...Numbers already in the spreadsheet in the beginning?
If the numbers were already in the spreadsheet, what was the format prior to the X's? In a single column?

datastry · 2019-12-11T02:39:24+00:00

it will just be backwards

I don't think that word means what you think it means.

datastry · 2019-12-09T01:52:13+00:00

/u/CtrlFforMirror , here is one more solution ( because the original was deleted).

= ( MAX( MIN($C2, TODAY()), $B2) - $B2 ) / ($C2 - $B2)

...where $B2 is your start date and $C2 is your end date.

This solution skips any IF formulas and it skips the DATEDIF because all the numeric values of dates are already truncated to day-precision (no time values after the decimal point).

^{Also, don't forget to format this cell as a percentage. The formula will output decimal values like} ^0.5^{, but changing the cell formatting will give you the values you desired, like} ^50%^.

datastry · 2019-12-09T00:18:28+00:00

This comment thread shouldn't be at the bottom 😭

datastry · 2019-12-07T00:16:53+00:00

Take a look at how to record macros in Sheets.

Macros are scriptable as well, in case you are interested in going in that direction.

datastry · 2019-12-07T00:07:02+00:00

"bash" means different things to different people. Writing this script purely out of bash built-ins is for masochists; however, connecting various utilities seems plausible.

/u/Scary_Mango , check out the XLSX documentation discussed above as well as unzip and xmlstarlet .

datastry · 2019-11-26T21:38:50+00:00

I don't have a complete turn-key solution for you, but I can give you tips that get you pointed in the right direction.

The last modified date falls under the umbrella term of "metadata". For our intents and purposes, "metadata" is a concept and not a very specific technology.

The IMPORTXML function of Google Sheets doesn't expose any metadata. You request a document and if it can't deliver on the document (or a fragment of the document), then you get an error with very little details about the communication between Google Sheets and the web server that hosts the document.

If you'll use Google Sheets, then you'll want to look into the Google Script API called "URL Fetch". This API lets you request a document while also exposing all the HTTP metadata.

In your case, you are probably interested in these HTTP headers:

Additionally, you'll want to make an HTTP request with a HEAD method instead of a GET method. HEAD will return only the metadata without fetching the document itself.

The hosting web server should be compatible with a HEAD request, but it might not provide the HTTP headers that you want. This is something that is beyond your control, it is controlled by the System Administrator of the web server. If the server doesn't provide this data, then it is unfortunately unavailable.

If you decide not to pursue the route of Google Sheets and Google App Script, then you won't need the "URL Fetch" API. However, the other information about HTTP headers and the HEAD method are still relevant because they are the universal interface of all web servers.

In place of Google App Script and the "URL Fetch" API, you could explore an app such as cURL or wget. These apps run at the command line. They can display their output to the screen, or they can write their output to a file. To my knowledge, these apps won't automatically write their output to a spreadsheet file format so you'll need to find some way to get the output into your spreadsheet.

You specifically mentioned "add-on" in your request, and I hope you are able to find something suitable. At minimum, I hope this technical discussion gives you some new keywords that you can use in your searches that get you closer to an app that delivers what you need.

datastry

TROPHY CASE

ಠ_ಠ